Data services while loop help

dataservices job design
1

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

Thanks & Regards

leo_dec11 (BOB member since 2009-06-18)

2

To troubleshoot the problem yourself, you could build up the decode bit by bit, or break it up to assess the output of each bit.

I was also just wondering why you might be using a while loop to do this? See the second post on this thread.

Darth Services :uk: (BOB member since 2007-11-20)

3

Hi Darth

Thanks for your reply… i need to use while loop as one single id can have multiple na_frm and na_to and from that i need to find only those dates which lies in between condition which i specified earlier…

source -> query in which i used lookupfunction to get the na_frm and na_to from tab where na_id = wl_id and $G_loop = tab.order-> tab joined to ->query_1 and query with wl_id = tab.na_id and in transformation mapped na_frm to decode(
tab.NA_FRM < sysdate() and
(tab.NA_FRM <= Query. ACT_SRT_DT_TWO_DEC_OF or
tab.NA_FRM > Query.I_FL_DT_UN), tab.NA_FRM,
NULL
)
mapped na_to to decode(
tab.NA_TO < Query.ACT_SRT_DT_TWO_DEC_OF, tab.NA_TO,
tab.NA_TO > sysdate(), NULL,
nvl(tab.NA_TO, sysdate())
)

-> query_2 checked if na_frm and na_to is not null then add days difference to I_FL_DT_UN

it is working fine… the problem is coming that it is inserting multiple rows in target table… where only one row should insert into target table.

regards

leo_dec11 (BOB member since 2009-06-18)