UV Stage Lookup

[itcconsulting]

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.

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 = ? );

However if I include the dates in the criteria, no lookups occur.

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 >= ?);

Is there a syntax issue? Am I doing this correctly?

[ray.wurlod]

It's initially a syntax issue. Identifier names (table names, column names) are case sensitive. Eff_Start_Dt and EFF_START_DT are not the same. The error message generated should have given you a clue to this.

It's secondarily a syntax issue. You are providing two parameter markers to test against the date range. Are you providing two copies of the "Key" date column from which these parameter markers can be resolved? Perhaps you need to prefer

Code: Select all

? BETWEEN Eff_Start_Dt AND Eff_End_Dt

[ray.wurlod]

You are trying to be too clever. Create separate indexes on Eff_Start_Dt and Eff_End_Dt.

You can test your queries (as to whether they will use indexes) using the EXPLAIN keyword, for example in the Adminsitrator client Command window.

Code: Select all

SELECT COUNT(*) FROM table WHERE CURRENT_DATE BETWEEN Eff_Start_Dt and Eff_End_Dt EXPLAIN;

[itcconsulting]

Thanks Ray..The separate indexes resulted in improved performance. I now have 3 indexes.