sql - Using variables in Oracle script -

there complex query generates report. query has several sub queries generate 3-columns table different products. each sub query returns 1 row. returned rows need united. there 1 requirement. if there no result rows sub query need include corresponding product final report anyway, specify trades_count equal zero.

i can achieve using set of variables. following code work 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) (         --data extractions several joins goes here....  ) temptable1 group product_name   --product 2 select @product_name_2 = product_name, @offer_valid_date_2 = max(expiry_date), @trades_count_2 = count(deal_number) (         --data extractions several joins goes here.... ) temptable2 group product_name   select isnull(@product_name_1,'product 1') product_name, @offer_valid_date_1 max_maturity, isnull(@trades_count_1,0) union ( select isnull(@product_name_2,'product 2') product_name, @offer_valid_date_2 max_maturity, isnull(@trades_count_2,0) ) 

i think haven’t used t-sql specific, pure ansi-sql (i’m not 100% sure though).

so not working in oracle.

first of requires having 1 declare keyword. forces me using begin … end execution scope. doesn’t allow me assign variables (see example above) – need use “select into” statement instead. after calculations done doesn’t allow me selecting values local variables. heck.

does know how make work in oracle?

thanks!

pl/sql different t-sql, did change comments you, @ links andy. ran in oracle's free sql developer (which has "translation scratch handler (tools>migration>translation scratch handler) may of use.

--this creates refcursor allow print results var refc refcursor /  declare --here declare our variables     product_name_1 varchar2(15) ;     offer_valid_date_1 date ;     trade_count_1 number ;     product_name_2 varchar2(15) ;     offer_valid_date_2 date ;     trade_count_2 number ;     begin     begin --this creates block may handle exceptions           select product_name,    max(expiry_date),    count(deal_number)             product_name_1 , offer_valid_date_1 , trade_count_1             --in oracle select into, not var=col         (                 --data extractions several joins goes here....                 select                      123 product_name,                         sysdate expiry_date,                         5 deal_number                 dual --this 'fake' table generate data testing          )  temptable1 --drop "as"         group product_name ;     exception --if not data found, error thrown               --if multiple values thrown error thrown (not caught here)     when no_data_found         product_name_1 := null ; --note, var = , use "var := value;"         offer_valid_date_1 := null;         trade_count_1 := null;     end ;     begin           select product_name,    max(expiry_date),    count(deal_number)             product_name_2 , offer_valid_date_2 , trade_count_2             --in oracle select into, not var=col         (                 --data extractions several joins goes here....                 select 555 product_name,    sysdate expiry_date,    6 deal_number                 dual          )  temptable2 -- drop "as"         group product_name ;     exception --if not data found, error thrown               --if multiple values thrown error thrown (not caught here)     when no_data_found         product_name_2 := null ;         offer_valid_date_2 := null;         trade_count_2 := null;     end ;      open :refc  --you cannot have select statement, must "open" cursor         --oracle isnull nvl (or nvl2 or can case or decode...)     select nvl(product_name_1,'product 1') product_name           , offer_valid_date_1 max_maturity           , nvl(trade_count_1,0)       dual --you must have table, dual oracle table tasks         union    select nvl(product_name_2,'product 2') product_name           , offer_valid_date_2 max_maturity           , nvl(trade_count_2,0)     dual;  end ; /  --now print results, if did in proc simple have output print refc;  ------------- product_name max_maturity              nvl(:b1,0)              -------------------------------------- ----------------------  123          18.feb.2011 08:43         1                       555          18.feb.2011 08:43         1                       

oracle concepts used here: dual table , nvl, variables, pl/sql exception

and @ http://www.dba-oracle.com/t_convent_sql_server_tsql_oracle_plsql.htm