Help with Lookup stage

[kumarjit]

This may sound wierd,. but I came accross a requirement which I am not able to implement using the
following job design

Code: Select all

	   Ref1
	    |
	    |
Oci_Src ---- Lookup ---- Tfm ----Oci_Tgt
	    |
	    |
	   Ref2

Among many of the columns in the dataset from Oci_Src are the following two columns :
> AIRLINE_NUMBER - This is the key field with which the input link is joined (or looked up ) from both the Ref1 and Ref2 sources
> FLIGHT_DATE - A column for which Range Lookup will be performed from both the Ref1 and Ref2 sources


The schema for the references are as follows :

REF1 : >A_NUM - will be mapped with the AIRLINE_NUM field from the input
>START_DATE - a start date of processing batch
>END_DATE - end date of the processing batch


REF2 :>A_NUM - will be mapped with the AIRLINE_NUM field from the input
>ACTIVE_FROM_DATE - date since which the transaction is active
>ACTIVE_TO_DATE - date till which the transaction is active


BUSINESS LOGIC : To get pass only those records for which the AIRLINE_NUMBER matches with the A_NUM entries in the reference sources
AND
START_DATE<FLIGHT_DATE<END_DATE
AND
ACTIVE_FROM_DATE<FLIGHT_DATE<ACTIVE_TO_DATE



QUESTION :

Can I implement this busienss logic using a single LOOKUP stage ? In the above design , the Range lookup expression for FLIGHT_DATE column
references only ONE link , either Ref1 or Ref2 , but not both at the same time .

Please help.

Thanks
Kumarjit.

[ray.wurlod]

You can do it, but you need two Lookup stages. A Lookup stage with a range lookup actually compiles to a transform operator.