StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUsing variables in Oracle script
text
Body
copied!<p>There is a complex query which generates a report. The query has several sub queries that generate 3-columns table for different products. Each sub query returns one row. All returned rows then need to be united. But there is one requirement. If there are no result rows for a sub query we need to include the corresponding product to the final report anyway, but specify that Trades_Count is equal to zero.</p> <p>I can achieve this using set of variables. The following code will work perfectly in MS SQL Server:</p> <pre><code>DECLARE @PRODUCT_NAME_1 nvarchar(100); DECLARE @OFFER_VALID_DATE_1 datetime; DECLARE @TRADES_COUNT_1 int; DECLARE @PRODUCT_NAME_2 nvarchar(100); DECLARE @OFFER_VALID_DATE_2 datetime; DECLARE @TRADES_COUNT_2 int; --Product 1 select @PRODUCT_NAME_1 = PRODUCT_NAME, @OFFER_VALID_DATE_1 = MAX(EXPIRY_DATE), @TRADES_COUNT_1 = COUNT(DEAL_NUMBER) from ( --Data extractions with several joins goes here.... ) as TempTable1 GROUP BY PRODUCT_NAME --Product 2 select @PRODUCT_NAME_2 = PRODUCT_NAME, @OFFER_VALID_DATE_2 = MAX(EXPIRY_DATE), @TRADES_COUNT_2 = COUNT(DEAL_NUMBER) from ( --Data extractions with several joins goes here.... ) as TempTable2 GROUP BY PRODUCT_NAME SELECT ISNULL(@PRODUCT_NAME_1,'Product 1') AS PRODUCT_NAME, @OFFER_VALID_DATE_1 AS MAX_MATURITY, ISNULL(@TRADES_COUNT_1,0) UNION ( SELECT ISNULL(@PRODUCT_NAME_2,'Product 2') AS PRODUCT_NAME, @OFFER_VALID_DATE_2 AS MAX_MATURITY, ISNULL(@TRADES_COUNT_2,0) ) </code></pre> <p>I think that I haven’t used anything T-SQL specific, but pure ANSI-SQL (I’m not 100% sure though).</p> <p>So this is <strong>not working</strong> in Oracle. </p> <p>First of all it requires having only one DECLARE keyword. Then it forces me using Begin … End execution scope. Then it doesn’t allow me to assign variables like I do (see example above) – I need to use “Select INTO” statement instead. After all calculations are done it doesn’t allow me selecting values from local variables. Heck.</p> <p>Does anyone know how to make it work in Oracle?</p> <p>Thanks!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload