How do a between when joining in a lookup table

[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?

[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.

[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.

[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.