UV Stage Lookup
Posted: Tue Feb 13, 2007 1:58 pm
I've created a universe table to store information for lookup purposes. I chose to use the universe table instead of the hashed file because my criteria for the lookup is a date range. The universe table has 5 keys, two of which are date fields.
Column Type Key
----------------- ----------- --------
BU_SRC_ID Char 12 Y
BU_TYPE Char 3 Y
SRC_SYS_ID Char 5 Y
EFF_START_DT Date 10 Y
EFF_END_DT Date 10 Y
BU_SID Integer 10 N
Here is my issue: When I use only the 3 non date keys in the lookup criteria, I get the SID.
However if I include the dates in the criteria, no lookups occur.
Is there a syntax issue? Am I doing this correctly?
Column Type Key
----------------- ----------- --------
BU_SRC_ID Char 12 Y
BU_TYPE Char 3 Y
SRC_SYS_ID Char 5 Y
EFF_START_DT Date 10 Y
EFF_END_DT Date 10 Y
BU_SID Integer 10 N
Here is my issue: When I use only the 3 non date keys in the lookup criteria, I get the SID.
Code: Select all
SELECT HASH_D_BUSINESS_UNIT_LKP2.BU_SRC_ID, HASH_D_BUSINESS_UNIT_LKP2.BU_TYPE, HASH_D_BUSINESS_UNIT_LKP2.SRC_SYS_ID, HASH_D_BUSINESS_UNIT_LKP2.Eff_Start_Dt, HASH_D_BUSINESS_UNIT_LKP2.Eff_End_Dt, HASH_D_BUSINESS_UNIT_LKP2.BU_SID FROM HASH_D_BUSINESS_UNIT_LKP2 WHERE (HASH_D_BUSINESS_UNIT_LKP2.BU_SRC_ID = ? AND HASH_D_BUSINESS_UNIT_LKP2.BU_TYPE = ? AND HASH_D_BUSINESS_UNIT_LKP2.SRC_SYS_ID = ? );
Code: Select all
SELECT HASH_D_BUSINESS_UNIT_LKP2.BU_SRC_ID, HASH_D_BUSINESS_UNIT_LKP2.BU_TYPE, HASH_D_BUSINESS_UNIT_LKP2.SRC_SYS_ID, HASH_D_BUSINESS_UNIT_LKP2.Eff_Start_Dt, HASH_D_BUSINESS_UNIT_LKP2.Eff_End_Dt, HASH_D_BUSINESS_UNIT_LKP2.BU_SID FROM HASH_D_BUSINESS_UNIT_LKP2 WHERE (HASH_D_BUSINESS_UNIT_LKP2.BU_SRC_ID = ? AND HASH_D_BUSINESS_UNIT_LKP2.BU_TYPE = ? AND HASH_D_BUSINESS_UNIT_LKP2.SRC_SYS_ID = ? AND HASH_D_BUSINESS_UNIT_LKP2.Eff_Start_Dt <= ? AND HASH_D_BUSINESS_UNIT_LKP2.Eff_End_Dt >= ?);