Page 1 of 1
How do a between when joining in a lookup table
Posted: Tue Jul 30, 2013 9:27 am
by MrBlack
I'm trying to figure out the best way to bring in a look up value into a transformer. My look up table is date bound so I want to look up the key appropriate key value so if my source has a "Service_Date" and my look up table is "My_Key, Start_Date, End_Date" and I'm joining in on the transformer, how do I draw the link so it does a between statement? In simple SQL it would look like:
Code: Select all
SELECT t.MyKey
FROM mytable t
WHERE Service_Date BETWEEN t.Start_Date AND t.End_Date
would I have to use custom SQL and write my query like this:
Code: Select all
SELECT t.MyKey
FROM mytable t
WHERE :1 BETWEEN t.Start_Date AND t.End_Date
How would I know what variable number it's going to be? Would I still need to draw some lines in the transformer linking the two tables by keys?
Posted: Tue Jul 30, 2013 11:21 am
by ArndW
I don't have access to DataStage at the moment, but if you look at the help files for "range lookups" that should point in the correct direction for this question.
Posted: Tue Jul 30, 2013 11:42 am
by MrBlack
From what I'm reading in the IBM help they talk about a Range Window, I'm looking all over but I can't find a Range Window or anything like it. I think it may only be possible on the Parallel edition and not Server.
Posted: Tue Jul 30, 2013 12:45 pm
by chulett
Correct, that's a parallel only feature. Here you need to see the SQL it generates for an equi-join and then modify that to do the between. That way you'll know what it is looking for in the way of bind variables - positional or numbered, I don't recall off the top of my head.
One thing to know as a piece of generic advice is you cannot simply return the result field(s) you are looking for, you need to return the passed in key field(s) as well. In your case you would have to return something in the Service_Date position and have MyKey as the second field in the select. What you pass back in the bind variable's position doesn't really matter as you'll never use it but it will be required by the stage. So something like:
Code: Select all
SELECT t.Start_Date, t.MyKey
FROM mytable t
WHERE :1 BETWEEN t.Start_Date AND t.End_Date
Whether your bind variable is a
:1 or a
:? I don't recall off the top of my head as it depends on the lookup source but as I said, generating the 'normal' SQL it wants to use will show you that.