DS - Server Achieving a between condition in lookup

[kww]

Quick Question:

Is it possible to achieve a between condition coming from a lookup (hash file lookup, oracle source/stream link)?

i.e.

lookup: key_fields, beging_date, end_date
stream link: key_fields, actual_date

I want to find the key_fields that the source.actual_date falls between the begin and end date in the lookup. Is there any tricks in the transformer to force a between condidtion?

Is there any other solution aside from flattening out the lookup data to individual records or pushing the join back to the source?

[WoMaWil]

There may be for any case a solution some more easy some a bit more complicated.

If you have for every key in your hashfile lets say less than 50 time interval rows you could for example proceed as follows.

You open two non-key multivalue fields with one an the same association.
One called start-date and an other called end-date.

You fetch the whole two multivalue-fields completly in your lookup and write it into another hash file.

You open as next stage this hash-file with now the multivalues open and check if the date is in between and you use for to continue only this rows.

If you have more row we have to look for an other trick.

Kind regards
Wolfgang

[kww]

The range of time intervals in the lookup range from 5 to 270. No ulimit in place so the time interval is not capped in any way. I think i may have to explore flattening out the row into muliple rows per time interval.

[ray.wurlod]

Anything but a hashed file stage can accommodate your requirement, but you'll need to us user-defined SQL (for the BETWEEN condition) and specify your search keys carefully.