Date Range Lookup Error Using UV Stage

[gsym]

Hi DSXCHAGERS,

I implimented the UVSTAGE method for the date range lookup as suggested in previous posts. But, I am getting

"DSD.BCIOpenRef call to SQLNumResultCols failed.SQL statement has incorrect number of result columns" error.

I created a seperate job to create a hashed file "HASHED_M_EMPLOYEE_UV" in the localuv. Used UVSTAGE to read from this hashed file.

I am using userdefined SQL in lookup UVSTAGE:

SQL for primary inputs:
SELECT I_EMP_DIM, D_DAY_STRT, D_DAY_END, I_USER FROM HASHED_M_EMPLOYEE_UV

SQL for reference inputs:
SELECT I_EMP_DIM FROM HASHED_M_EMPLOYEE_UV WHERE I_USER_MYP=? AND (D_DAY_STRT<=? AND D_DAY_END>=?)

Table used in Query:
HASHED_M_EMPLOYEE_UV

Please advice on this error.

Thanks
gsym

[kris007]

Did you set the datatypes in the UV stage to Varchar? If not, set it to varchar for all columns and then try running your job. Also, make sure you have created the hashed file correctly, i.e. if you are not using the Delete before create option, you have to be sure that you didn't change the metadata of the hashedfile(such as adding new columns or deleting columns) after you have created it.

[ray.wurlod]

When you're doing the range lookup you need to lie to DataStage about which columns are Key. In your case you need three columns identified as Key: I_USER_MYP, D_DAY_STRT and D_DAY_END. The Columns grid should contain only these and I_EMP_DIM (a total of four columns).

[gsym]

Hello,

Still getting the same error

This what I did..

# Created the Hashed file with a synthetic key, using @INROWNUM, to get all the rows from the source to hashed file. Total 5 coulmns(I_EMP_DIM,I_USER, D_DAY_STRT,D_DAY_END and SYN_KEY).

# Used UVSTAGE to fetched records from Hashed file. Only 4 columns (I_EMP_DIM,I_USER, D_DAY_STRT and D_DAY_END) were used. And I did set the datatypes for all the to VARCHAR in UVSTAGE.

# Also defined I_USER, D_DAY_STRT and D_DAY_END as key columns in UVSTAGE and mapped as below:(did all the conversions)

I_USER from Source ----> I_USER from UVSTAGE ,
D_DATE from Source ----> D_DAY_STRT from UVSTAGE &
D_DATE from Source ----> D_DAY_END from UVSTAGE.

# SQL for primary inputs:

SELECT I_EMP_DIM, D_DAY_STRT, D_DAY_END, I_USER FROM HASHED_M_EMPLOYEE_UV

#SQL for reference inputs:

SELECT I_EMP_DIM FROM HASHED_M_EMPLOYEE_UV WHERE I_USER=? AND (D_DAY_STRT<=? AND D_DAY_END>=?)

#Table used in Query:

HASHED_M_EMPLOYEE_UV -- Is this correct???

# Also in the Transformer Stage where the UVSTAGE is referenced, I checked the "reference link with multi row result set". I actually tried with and without this checkbox enabled.

Ray & Kris - Thanks for replying to my post and it will be really helpful if you can suggest/advice me if any alterations to be made to the ETL.

Thanks,
gsym

[kris007]

Used UVSTAGE to fetched records from Hashed file. Only 4 columns (I_EMP_DIM,I_USER, D_DAY_STRT and D_DAY_END) were used. And I did set the datatypes for all the to VARCHAR in UVSTAGE.

Include the SYN_KEY in the UVStage and then try reading the data from the UV stage.