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.
I can achieve this using set of variables. The following code will work perfectly in MS SQL Server:
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)
)
I think that I haven’t used anything T-SQL specific, but pure ANSI-SQL (I’m not 100% sure though).
So this is not working in Oracle.
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.
Does anyone know how to make it work in Oracle?
Thanks!
PL/SQL is different than t-sql, I did a change with some comments for you, but definitely look at the links from Andy. This was ran in oracle's free SQL Developer (which also has a "Translation Scratch Handler (tools>Migration>Translation Scratch Handler) that may be of use.
Oracle concepts used here: Dual Table , NVL, Variables, pl/sql Exception
and look at this http://www.dba-oracle.com/t_convent_sql_server_tsql_oracle_plsql.htm