Problem in lookup

mac4rfree85 · Post by **mac4rfree85** » Tue Apr 12, 2011 4:51 am

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!!!

ray.wurlod · Post by **ray.wurlod** » Tue Apr 12, 2011 4:56 am

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.

mac4rfree85 · Post by **mac4rfree85** » Tue Apr 12, 2011 5:03 am

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..

ray.wurlod · Post by **ray.wurlod** » Tue Apr 12, 2011 5:06 am

Show us the SQL.

mac4rfree85 · Post by **mac4rfree85** » Tue Apr 12, 2011 5:10 am

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.

ray.wurlod · Post by **ray.wurlod** » Tue Apr 12, 2011 4:33 pm

Your SQL is only selecting two columns. XRF_CUSTOMER_T.N_CUST_DWID and XRF_CUSTOMER_T.N_CUST .

mac4rfree85 · Post by **mac4rfree85** » Wed Apr 13, 2011 4:07 am

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..

ray.wurlod · Post by **ray.wurlod** » Wed Apr 13, 2011 4:29 pm

What database are you selecting from? Can you use SQL functions or DataStage functions (Modify or Transformer stage) to isolate the components you require?

mac4rfree85 · Post by **mac4rfree85** » Wed Apr 20, 2011 7:24 am

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..