Page 1 of 1
LOOKUP
Posted: Wed Apr 13, 2005 8:42 am
by naren6876
Code: Select all
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.
Posted: Wed Apr 13, 2005 8:46 am
by Sainath.Srinivasan
Write a 'user defined SQL' in the universe stage to meet your requirements.
Posted: Wed Apr 13, 2005 8:51 am
by tonystark622
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.
Posted: Wed Apr 13, 2005 9:00 am
by naren6876
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
Posted: Wed Apr 13, 2005 9:17 am
by Sainath.Srinivasan
The reference input
Posted: Wed Apr 13, 2005 1:57 pm
by naren6876
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
Posted: Wed Apr 13, 2005 3:17 pm
by ray.wurlod
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.
Posted: Wed Apr 13, 2005 3:20 pm
by Sainath.Srinivasan
As you have 4 '?', you need 4 columns in the Universe stage defined as keys.
Posted: Wed Apr 13, 2005 3:30 pm
by naren6876
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