title: Using variables in Oracle script
body: <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>