Selecting Date which lies between 2 dates

pkl · Post by **pkl** » Thu Oct 07, 2004 3:00 pm

Hi,

I have a job in which I perform many look ups ......

Now I have various field on which I am performing a look up . I have a scenario whereI need tocheck if a Date lies between given 2 dates

If u talk in oracle terms it would be something like a where condition with
Table1.Date between table2.date1 and table2.date2

I am trying to do this in Xfm but I am not getting the required values.

Can anybody help me out pls

Thanks in advance
Preethi

dhiraj · Post by **dhiraj** » Thu Oct 07, 2004 3:37 pm

I am trying to do this in Xfm but I am not getting the required values.

What are you doing in the transformer? I mean how are you trying to implement this?

what are you looking up into? An ODBC? if so you could use the user defined sql option in the ODBC stage and specify the relational condition you require for the date field.

Dhiraj

kcbland · Post by **kcbland** » Thu Oct 07, 2004 3:39 pm

Are you attempting a BETWEEN lookup using an ODBC/OCI stage? Or is your attempt to use hash files. Search this forum, as this topic is extensively covered.

pkl · Post by **pkl** » Mon Oct 11, 2004 8:49 am

Hi,

I am trying to acheive the "between" functionality thru hash files.

Is there any way to do it?

Thanx
Preethi

pkl · Post by **pkl** » Mon Oct 11, 2004 9:48 am

Hi,

I just want to be clear in what I'm trying to acheive.

I am performing a look up on a key, and need to check the date range from the same hash, so I am trying to do the look up first and write a condition in the Transformer using a Stage Var like

If ( Iconv(Table.A[1,10],"DYMD") > Iconv(Lookup.Date1[1,10],"DYMD") and
Iconv(Table.A[1,10],"DYMD")<Iconv(Lookup.Date2[1,10],"DYMD") ) then "Insert" else "Reject"

And my constraint would be
StageVar="Insert"

THis doesn't give me any error when I try to run this but only a few rows get processed

on the other hand if I give
If ( Iconv(Table.A[1,10],"DYMD") > Iconv(Lookup.Date1[1,10],"DYMD")
>Iconv(Lookup.Date2[1,10],"DYMD") ) then "Insert" else "Reject"

then all the rows get processed. Both the results are wrong.

Can anybody help me with this?

Thanx
Preethi

kudaka · Post by **kudaka** » Mon Oct 11, 2004 10:09 am

Step1:
Create a hash file (MyHash) with three fields:
1. Id -- Hard code this value with 'XYZ'
2. Date1 - integer - store your 1st date in the internal format (use iconv)
3. Date2 - integer - store your 2nd date in the internal format (use iconv)
Step2:
Do a look-up to Myhash with key expression = 'XYZ'
on the output, create a constraint like this:
Your input date (internal format -integer) > MyHash.Date1 and Your input date (internal format -integer) < MyHash.Date2

Reject any rows that does not satisfy this constraint to a reject file.
This is based on the assumption that you have only one set of date range.

Hope this helps.

pkl · Post by **pkl** » Mon Oct 11, 2004 10:33 am

Hi,

I might sound stupid by asking this but I'm not very clear on what do you mean by

(internal format -integer)

Can you explain this?

Thanx
Preethi

kudaka · Post by **kudaka** » Mon Oct 11, 2004 10:37 am

When you use "iconv" on a date it will be converted to an integer (which is called internal (format) representaion of a date in DataStage)

Thanks.

pkl · Post by **pkl** » Mon Oct 11, 2004 1:27 pm

Hi,
Oh that.... thanx a lot

Preethi

sumitgulati · Post by **sumitgulati** » Tue Oct 12, 2004 9:34 am

pkl wrote: If ( Iconv(Table.A[1,10],"DYMD") > Iconv(Lookup.Date1[1,10],"DYMD") and
Iconv(Table.A[1,10],"DYMD")<Iconv(Lookup.Date2[1,10],"DYMD") ) then "Insert" else "Reject"

Hi Preethi, I do not see anything wrong with this IF condition. I guess the date format you have given in Iconv ("DMYD") is not in sync with the date format coming from input\lookup tables. Just check that and give the proper date formats. It should work.

I had exactly the same requirement and I implemented it using the same logic. Its working fine for me.

Regards,
Sumit