while loop

71 Views

Hi guys

I have source table which has record like this :-

WL_ID I_STRT_DT ACT_STRT_DT ACT_SRT_DT_TWO_DEC_OF I_FL_DT I_FL_DT_C_D I_FL_DT_TWO_DL_OF I_FL_DT_UN
986532 2013-03-29 2013-03-29 2013-04-29 2013-05-31 2013-05-31 2013-07-01 2013-07-01

and i need to compare below data

NA_ID NA_R_ID NA_Ord NA_FRM NA_TO
986532 986532||3 1 2013-04-29 2013-05-27
986532 986532||13 2 2013-07-21 2013-07-28
986532 986532||15 3 2013-08-02 2013-08-07

to find only records where na_frm is less than sysdate and less than to ACT_STRT_DT_TWO_DEC_OF or greater than I_FL_DT_UN

i have used below mentioned decode function in while loop to get rid of records where na_ord in (2,3) as in result i want only 1 row where na_ord ='1' because other two rows dates are after sysdate.

decode(
Query.NA_TO < cast(Query.ACT_STRT_DT_TWO_DEC_OF, 'date'), NULL,
Query.NA_FRM <= cast(Query.ACT_STRT_DT_TWO_DEC_OF, 'date'), cast(Query.ACT_STRT_DT_TWO_DEC_OF, 'date') + 1,
Query.NA_FRM > cast(Query.I_FL_DT_UN, 'date'), NULL,
Query.NA_FRM
)

but after finishing the data flow, column is coming as NULL not as value na_frm.

could you please help me what is wrong with decode function