we need to implement the following query in such a way that we should not have to write such a big query instead we should implement it through procedure,function or anonymous block (what ever way we can)
wk_units1-105 are column names
cnt_sls_dm.fct_sales_summary table name.
we are comparing the data in the same table but week 2 of a.fct_sales_summary
with week 1 of b.fct_sales_summary
In short we need to create something in pl/sql to apply following functionality(am I rite)
SELECT DISTINCT A.bio_id ,
DECODE(A.wk_units2 - B.wk_units1,0,NULL,A.wk_units2) prev,
DECODE(A.wk_units2 - B.wk_units1,0,NULL,B.wk_units1) curr,
DECODE(A.wk_units3 -B.wk_units2,0,NULL,A.wk_units3) prev,
DECODE(A.wk_units3 -B.wk_units2,0,NULL,B.wk_units2) curr,
DECODE(A.wk_units4 -B.wk_units3,0,NULL,A.wk_units4) prev,
DECODE(A.wk_units4 -B.wk_units3,0,NULL,B.wk_units3) curr,
DECODE(A.wk_units5 -B.wk_units4,0,NULL,A.wk_units5) prev,
DECODE(A.wk_units5 -B.wk_units4,0,NULL,B.wk_units4) curr,
DECODE(A.wk_units6 -B.wk_units5,0,NULL,A.wk_units6) prev,
DECODE(A.wk_units6 -B.wk_units5,0,NULL,B.wk_units5) curr
. . .
. . .
. . .
DECODE(A.wk_units105 -B.wk_units104,0,NULL,A.wk_units105) prev,
DECODE(A.wk_units105 -B.wk_units105,0,NULL,B.wk_units104) curr
FROM
cnt_sls_dm.fct_sales_summary A,
cnt_sls_dm.fct_sales_summary B
WHERE
A.bio_id=B.bio_id AND A.bio_id<>0
AND ROWNUM<25
AND ( A.wk_units2<> B.wk_units1
OR A.wk_units3<> B.wk_units2
OR A.wk_units4<> B.wk_units3
OR A.wk_units5<> B.wk_units4
OR A.wk_units6<> B.wk_units5
. .
. .
. .
OR A.wk_units105<>B.wk_units104)
The output is like:
BIO_ID PREV CURR PREV_1 CURR_1 PREV_2 CURR_2 PREV_3 CURR_3
5,032,130.00 -1 0 0 1 0 1
5,032,130.00 1 0 0 1 1 0 0 1
5,032,130.00 0 1 0 1
5,032,130.00 1 0 1 1 0 0 0 1
5,032,328.00 5 3 0 36 0 2 0 0
we implemented following stuff it doesnt work
DECLARE
sql_str long(32000);
where_str long(32000);
counter NUMBER(3):=0;
BEGIN
sql_str:='SELECT '||'A.bio_id ,';
where_str:=' where '||'A.bio_id=B.bio_id AND'||' A.bio_id<>0 and rownum<25 AND (' ;
LOOP
counter:=counter+1;
sql_str:=sql_str||'decode(A.wk_units'||(counter+1)||' - B.wk_units'|| (counter)||',0,NULL,A.wk_units'||(counter+1)||')';
sql_str:=sql_str||', decode(A.wk_units'||(counter+1)||' - B.wk_units'|| (counter)||',0,NULL,B.wk_units'||(counter)||')' ;
where_str:=where_str||' A.wk_units'||(counter+1)||'<> B.wk_units'||(counter) ;
EXIT WHEN counter=50;
sql_str:=sql_str||', ';
where_str:=where_str||' or ';
END LOOP;
sql_str:=sql_str||' from cnt_sls_dm.fct_sales_summary A, cnt_sls_dm.fct_sales_summary B';
sql_str:=sql_str||where_str||')';
dbms_output.put_line(sql_str);
EXECUTE IMMEDIATE (sql_str);
END;
we should get output as records but we got the following query as output which is running fine in toad but we want the script above to get us the result as records but not as query............
SELECT A.bio_id ,DECODE(A.wk_units2 - B.wk_units1,0,NULL,A.wk_units2),
DECODE(A.wk_units2 - B.wk_units1,0,NULL,B.wk_units1), DECODE(A.wk_units3 -
B.wk_units2,0,NULL,A.wk_units3), DECODE(A.wk_units3 -
B.wk_units2,0,NULL,B.wk_units2), DECODE(A.wk_units4 -
B.wk_units3,0,NULL,A.wk_units4), DECODE(A.wk_units4 -
B.wk_units3,0,NULL,B.wk_units3), DECODE(A.wk_units5 -
B.wk_units4,0,NULL,A.wk_units5), DECODE(A.wk_units5 -
B.wk_units4,0,NULL,B.wk_units4), DECODE(A.wk_units6 -
B.wk_units5,0,NULL,A.wk_units6), DECODE(A.wk_units6 -
B.wk_units5,0,NULL,B.wk_units5), DECODE(A.wk_units7 -
B.wk_units6,0,NULL,A.wk_units7), DECODE(A.wk_units7 -
B.wk_units6,0,NULL,B.wk_units6), DECODE(A.wk_units8 -
B.wk_units7,0,NULL,A.wk_units8), DECODE(A.wk_units8 -
B.wk_units7,0,NULL,B.wk_units7), DECODE(A.wk_units9 -
B.wk_units8,0,NULL,A.wk_units9), DECODE(A.wk_units9 -
B.wk_units8,0,NULL,B.wk_units8), DECODE(A.wk_units10 -
B.wk_units9,0,NULL,A.wk_units10), DECODE(A.wk_units10 -
B.wk_units9,0,NULL,B.wk_units9), DECODE(A.wk_units11 -
B.wk_units10,0,NULL,A.wk_units11), DECODE(A.wk_units11 -
B.wk_units10,0,NULL,B.wk_units10), DECODE(A.wk_units12 -
B.wk_units11,0,NULL,A.wk_units12), DECODE(A.wk_units12 -
B.wk_units11,0,NULL,B.wk_units11), DECODE(A.wk_units13 -
B.wk_units12,0,NULL,A.wk_units13), DECODE(A.wk_units13 -
B.wk_units12,0,NULL,B.wk_units12), DECODE(A.wk_units14 -
B.wk_units13,0,NULL,A.wk_units14), DECODE(A.wk_units14 -
B.wk_units13,0,NULL,B.wk_units13), DECODE(A.wk_units15 -
B.wk_units14,0,NULL,A.wk_units15), DECODE(A.wk_units15 -
B.wk_units14,0,NULL,B.wk_units14), DECODE(A.wk_units16 -
B.wk_units15,0,NULL,A.wk_units16), DECODE(A.wk_units16 -
B.wk_units15,0,NULL,B.wk_units15), DECODE(A.wk_units17 -
B.wk_units16,0,NULL,A.wk_units17), DECODE(A.wk_units17 -
B.wk_units16,0,NULL,B.wk_units16), DECODE(A.wk_units18 -
B.wk_units17,0,NULL,A.wk_units18), DECODE(A.wk_units18 -
B.wk_units17,0,NULL,B.wk_units17), DECODE(A.wk_units19 -
B.wk_units18,0,NULL,A.wk_units19), DECODE(A.wk_units19 -
B.wk_units18,0,NULL,B.wk_units18), DECODE(A.wk_units20 -
B.wk_units19,0,NULL,A.wk_units20), DECODE(A.wk_units20 -
B.wk_units19,0,NULL,B.wk_units19), DECODE(A.wk_units21 -
B.wk_units20,0,NULL,A.wk_units21), DECODE(A.wk_units21 -
B.wk_units20,0,NULL,B.wk_units20), DECODE(A.wk_units22 -
B.wk_units21,0,NULL,A.wk_units22), DECODE(A.wk_units22 -
B.wk_units21,0,NULL,B.wk_units21), DECODE(A.wk_units23 -
B.wk_units22,0,NULL,A.wk_units23), DECODE(A.wk_units23 -
B.wk_units22,0,NULL,B.wk_units22), DECODE(A.wk_units24 -
B.wk_units23,0,NULL,A.wk_units24), DECODE(A.wk_units24 -
B.wk_units23,0,NULL,B.wk_units23), DECODE(A.wk_units25 -
B.wk_units24,0,NULL,A.wk_units25), DECODE(A.wk_units25 -
B.wk_units24,0,NULL,B.wk_units24), DECODE(A.wk_units26 -
B.wk_units25,0,NULL,A.wk_units26), DECODE(A.wk_units26 -
B.wk_units25,0,NULL,B.wk_units25), DECODE(A.wk_units27 -
B.wk_units26,0,NULL,A.wk_units27), DECODE(A.wk_units27 -
B.wk_units26,0,NULL,B.wk_units26), DECODE(A.wk_units28 -
B.wk_units27,0,NULL,A.wk_units28), DECODE(A.wk_units28 -
B.wk_units27,0,NULL,B.wk_units27), DECODE(A.wk_units29 -
B.wk_units28,0,NULL,A.wk_units29), DECODE(A.wk_units29 -
B.wk_units28,0,NULL,B.wk_units28), DECODE(A.wk_units30 -
B.wk_units29,0,NULL,A.wk_units30), DECODE(A.wk_units30 -
B.wk_units29,0,NULL,B.wk_units29), DECODE(A.wk_units31 -
B.wk_units30,0,NULL,A.wk_units31), DECODE(A.wk_units31 -
B.wk_units30,0,NULL,B.wk_units30), DECODE(A.wk_units32 -
B.wk_units31,0,NULL,A.wk_units32), DECODE(A.wk_units32 -
B.wk_units31,0,NULL,B.wk_units31), DECODE(A.wk_units33 -
B.wk_units32,0,NULL,A.wk_units33), DECODE(A.wk_units33 -
B.wk_units32,0,NULL,B.wk_units32), DECODE(A.wk_units34 -
B.wk_units33,0,NULL,A.wk_units34), DECODE(A.wk_units34 -
B.wk_units33,0,NULL,B.wk_units33), DECODE(A.wk_units35 -
B.wk_units34,0,NULL,A.wk_units35), DECODE(A.wk_units35 -
B.wk_units34,0,NULL,B.wk_units34), DECODE(A.wk_units36 -
B.wk_units35,0,NULL,A.wk_units36), DECODE(A.wk_units36 -
B.wk_units35,0,NULL,B.wk_units35), DECODE(A.wk_units37 -
B.wk_units36,0,NULL,A.wk_units37), DECODE(A.wk_units37 -
B.wk_units36,0,NULL,B.wk_units36), DECODE(A.wk_units38 -
B.wk_units37,0,NULL,A.wk_units38), DECODE(A.wk_units38 -
B.wk_units37,0,NULL,B.wk_units37), DECODE(A.wk_units39 -
B.wk_units38,0,NULL,A.wk_units39), DECODE(A.wk_units39 -
B.wk_units38,0,NULL,B.wk_units38), DECODE(A.wk_units40 -
B.wk_units39,0,NULL,A.wk_units40), DECODE(A.wk_units40 -
B.wk_units39,0,NULL,B.wk_units39), DECODE(A.wk_units41 -
B.wk_units40,0,NULL,A.wk_units41), DECODE(A.wk_units41 -
B.wk_units40,0,NULL,B.wk_units40), DECODE(A.wk_units42 -
B.wk_units41,0,NULL,A.wk_units42), DECODE(A.wk_units42 -
B.wk_units41,0,NULL,B.wk_units41), DECODE(A.wk_units43 -
B.wk_units42,0,NULL,A.wk_units43), DECODE(A.wk_units43 -
B.wk_units42,0,NULL,B.wk_units42), DECODE(A.wk_units44 -
B.wk_units43,0,NULL,A.wk_units44), DECODE(A.wk_units44 -
B.wk_units43,0,NULL,B.wk_units43), DECODE(A.wk_units45 -
B.wk_units44,0,NULL,A.wk_units45), DECODE(A.wk_units45 -
B.wk_units44,0,NULL,B.wk_units44), DECODE(A.wk_units46 -
B.wk_units45,0,NULL,A.wk_units46), DECODE(A.wk_units46 -
B.wk_units45,0,NULL,B.wk_units45), DECODE(A.wk_units47 -
B.wk_units46,0,NULL,A.wk_units47), DECODE(A.wk_units47 -
B.wk_units46,0,NULL,B.wk_units46), DECODE(A.wk_units48 -
B.wk_units47,0,NULL,A.wk_units48), DECODE(A.wk_units48 -
B.wk_units47,0,NULL,B.wk_units47), DECODE(A.wk_units49 -
B.wk_units48,0,NULL,A.wk_units49), DECODE(A.wk_units49 -
B.wk_units48,0,NULL,B.wk_units48), DECODE(A.wk_units50 -
B.wk_units49,0,NULL,A.wk_units50), DECODE(A.wk_units50 -
B.wk_units49,0,NULL,B.wk_units49), DECODE(A.wk_units51 -
B.wk_units50,0,NULL,A.wk_units51), DECODE(A.wk_units51 -
B.wk_units50,0,NULL,B.wk_units50) FROM cnt_sls_dm.fct_sales_summary A,
cnt_sls_dm.fct_sales_summary B WHERE A.bio_id=B.bio_id AND A.bio_id<>0 AND
ROWNUM<25 AND ( A.wk_units2<> B.wk_units1 OR A.wk_units3<> B.wk_units2 OR
A.wk_units4<> B.wk_units3 OR A.wk_units5<> B.wk_units4 OR A.wk_units6<>
B.wk_units5 OR A.wk_units7<> B.wk_units6 OR A.wk_units8<> B.wk_units7 OR
A.wk_units9<> B.wk_units8 OR A.wk_units10<> B.wk_units9 OR A.wk_units11<>
B.wk_units10 OR A.wk_units12<> B.wk_units11 OR A.wk_units13<> B.wk_units12 OR
A.wk_units14<> B.wk_units13 OR A.wk_units15<> B.wk_units14 OR A.wk_units16<>
B.wk_units15 OR A.wk_units17<> B.wk_units16 OR A.wk_units18<> B.wk_units17 OR
A.wk_units19<> B.wk_units18 OR A.wk_units20<> B.wk_units19 OR A.wk_units21<>
B.wk_units20 OR A.wk_units22<> B.wk_units21 OR A.wk_units23<> B.wk_units22 OR
A.wk_units24<> B.wk_units23 OR A.wk_units25<> B.wk_units24 OR A.wk_units26<>
B.wk_units25 OR A.wk_units27<> B.wk_units26 OR A.wk_units28<> B.wk_units27 OR
A.wk_units29<> B.wk_units28 OR A.wk_units30<> B.wk_units29 OR A.wk_units31<>
B.wk_units30 OR A.wk_units32<> B.wk_units31 OR A.wk_units33<> B.wk_units32 OR
A.wk_units34<> B.wk_units33 OR A.wk_units35<> B.wk_units34 OR A.wk_units36<>
B.wk_units35 OR A.wk_units37<> B.wk_units36 OR A.wk_units38<> B.wk_units37 OR
A.wk_units39<> B.wk_units38 OR A.wk_units40<> B.wk_units39 OR A.wk_units41<>
B.wk_units40 OR A.wk_units42<> B.wk_units41 OR A.wk_units43<> B.wk_units42 OR
A.wk_units44<> B.wk_units43 OR A.wk_units45<> B.wk_units44 OR A.wk_units46<>
B.wk_units45 OR A.wk_units47<> B.wk_units46 OR A.wk_units48<> B.wk_units47 OR
A.wk_units49<> B.wk_units48 OR A.wk_units50<> B.wk_units49 OR A.wk_units51<>
B.wk_units50)