LOOKUP

naren6876 · Post by **naren6876** » Wed Apr 13, 2005 8:42 am





SEQ_FILE----------->XFM------------->SEQ_FILE
                               ^
                               |
HSH_FILE--------->UVSTAGE

TO DO THE RANGE LOOKUP IS THIS THE CORRECT DESIGN?.

My Source seqfile has ID,EFF_DT ,AND HSH_FILE HAS ID,START_DT ,END_DT.

IN UVSTAGE INPUTS IAM SPECIFYING THE HSH_FILE NAME AS TABLE NAME AND THE COLUMNS SAME AS HSH_FILE.
AND I WANT TO USE THE SAME COLUMNS IN THE UV OUTPUTS PAGE AND LOOKUP THE RANGE.

SOMEONE SHED SOME LIGHT ON THIS PLEASE

TIA.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Apr 13, 2005 8:46 am

Write a 'user defined SQL' in the universe stage to meet your requirements.

tonystark622 · Post by **tonystark622** » Wed Apr 13, 2005 8:51 am

You may need to add an index on the column(s) in the universe table/hash file on which you're doing the range lookup.

naren6876 · Post by **naren6876** » Wed Apr 13, 2005 9:00 am

Sainath.Srinivasan wrote:Write a 'user defined SQL' in the universe stage to meet your requirements.

when i select the user defined sql query, under sql query it is asking for SQL for primary input and SQL for reference input.

But here i have only one input that is HSH_File.

what should i write?.

TIA

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Apr 13, 2005 9:17 am

The reference input

naren6876 · Post by **naren6876** » Wed Apr 13, 2005 1:57 pm

Sainath.Srinivasan wrote:The reference input

Hi,
I have seqFile as source and UVStage as reference.Iam doing lookup for the range.
when i ran the job iam getting the error

B021_1..Transformer_54.DSLink76: DSD.BCIGetByKey call to SQLExecute failed.
SQL statement:SELECT TUC_SP_HSH.SIT_RT, TUC_SP_HSH.DIV_NO, TUC_SP_HSH.FED_NO, TUC_SP_HSH.SP_EFFECTIVE_DT, TUC_SP_HSH.SP_EXPIRY_DT WHERE (TUC_SP_HSH.DIVISION_NO = ? AND TUC_SP_HSH.FED_NO = ? AND TUC_SP_HSH.SP_EFFECTIVE_DT >= ? AND TUC_SP_HSH.SP_EXPIRY_DT <= ?)
SQLSTATE=07001, DBMS.CODE=0
[DataStage][SQL Client]Not all parameter markers have been resolved

HOW DO I RESOLVE THIS ERROR.

PLEASE SHED SOME LIGHT ON THIS

TIA

ray.wurlod · Post by **ray.wurlod** » Wed Apr 13, 2005 3:17 pm

You have to tell DataStage which are the search keys, as opposed to the primary key(s). Any column that is constrained, mark as Key. Any column that is not constrained, mark as not Key.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Apr 13, 2005 3:20 pm

As you have 4 '?', you need 4 columns in the Universe stage defined as keys.

naren6876 · Post by **naren6876** » Wed Apr 13, 2005 3:30 pm

ray.wurlod wrote:You have to tell DataStage which are the search keys, as opposed to the primary key(s). Any column that is constrained, mark as Key. Any column that is not constrained, mark as not Key.

I did that. still getting same error. my search columns in seqfile and UVstage are same datatype and length also.

any clue pls

TIA