Page 1 of 1
Problem in lookup
Posted: Tue Apr 12, 2011 4:51 am
by mac4rfree85
Hi Guys,
My job is having 2 lookup on 2 tables. In the first lookup, i am pulling 4 columns from the total 64 columns present in the table.
In the second lookup, i am pulling 2 columns from the total 3 columns present in the table.
Now the values are coming correctly for the first lookup. But in the second lookup, the data for the 2nd and 3rd column is getting concantanated and coming as 2nd column. The SQL is for only 2 column.
Now, how can the 3rd col value can be coming...
Can somebody point me in a correct direction.
Cheers!!!
Posted: Tue Apr 12, 2011 4:56 am
by ray.wurlod
Demonstrate that the SQL in the second lookup matches the metadata in the job design. Or, if it's a hashed file lookup, demonstrate that the location field in the table definition is correct for all three columns.
Posted: Tue Apr 12, 2011 5:03 am
by mac4rfree85
It is not a hashed file lookup. The metadata matches with the SQL in the second lookup..
Actually, in the database, col2 on which the lookup is done is varchar2(9) and in the job, it is defined as Varchar(9).
Now, when the data from col3 combines with it, it exceeds that 9 character and it is throwing a warning..
Is there any other way to suppress this warning or work around??? but ideally i like to understand how this third column is coming..
Posted: Tue Apr 12, 2011 5:06 am
by ray.wurlod
Show us the SQL.
Posted: Tue Apr 12, 2011 5:10 am
by mac4rfree85
This is the SQL i got from the Lookup stage.
Code: Select all
SELECT XRF_CUSTOMER_T.N_CUST_DWID,XRF_CUSTOMER_T.N_CUST FROM XRF_CUSTOMER_T WHERE XRF_CUSTOMER_T.N_CUST=:2
Also, when i am viewing data from the Oracle(lookup) stage, i am able to see only 2 columns and not 3 columns. But only during the run, somehow 3 columns are coming.
Posted: Tue Apr 12, 2011 4:33 pm
by ray.wurlod
Your SQL is only selecting two columns. XRF_CUSTOMER_T.N_CUST_DWID and XRF_CUSTOMER_T.N_CUST .
Posted: Wed Apr 13, 2011 4:07 am
by mac4rfree85
exactly Ray,, even though my SQL is selecting only two columns, my second column contains data of both the second column contanated with the third column value.
In table,
N_CUST CSGNE
1203 011
1204 055
When running the job, i am getting for N_CUST values as 1203011 and 1204055...
This is causing the problem..
Posted: Wed Apr 13, 2011 4:29 pm
by ray.wurlod
What database are you selecting from? Can you use SQL functions or DataStage functions (Modify or Transformer stage) to isolate the components you require?
Posted: Wed Apr 20, 2011 7:24 am
by mac4rfree85
Sorry to reply back late,,, the problem is resolved..
The problem was with the Source data and not as Lookup data as i thought.
Now i am doing the lookup by Spliting the source column to the required length and looking up with the lookup column.
Thanks for your help and guidance..