Lookup without key

[prasad111]

I have two tables

source table has

-----------------------------------------
address1 | city | state | zip
------------|---------|-------|-----------
123 main | newark| NJ | 12310
3434 | sanjose| CA | 54540
1232 | ASJK | AI | 4454550
-----------------------------------------------

NOTE: In the above table AI stands for one of the foreign country
the lookup table has all the state columns

stt_cd
--------
AE
CA
NJ
.
.
..
all the states in USA are mentioned

Now In my target table, if there are any state outside USA in the lookup has to be replaced with **, so for above example the output should be
-----------------------------------------
address1 | city | state | zip
------------|---------|-------|-----------
123 main | newark| NJ | 12310
3434 | sanjose| CA | 54540
**** | *** | ** | *****
-----------------------------------------------

NOTE: there is no key column in source and lookup and I should not hardcode the 52 states in column derivation inside the transformer.

how can I achive this output in DataStage, is it possible by an SQL query atleast....
please give some ideas.

Regards
Prasad

[vmcburney]

You would have just the US states in the lookup. You would add the * to the output data in the transformer when a lookup row is not found. There is no reason to try and bring back ** records from the lookup when you can generate ** strings in the transformer.

[narasimha]

How can you do a lookup without a key ?

- Create a hashed file from your lookup table with stt_cd as key (Use this as a lookup)
- Now when you dont find a lookup replace with '**' else let the column flow through

Ex : if LookUp.NOTFOUND then '**' else SourceTable.State

Get back if have problems doing this.

[prasad111]

How can you do a lookup without a key ?

- Create a hashed file from your lookup table with stt_cd as key (Use this as a lookup)
- Now when you dont find a lookup replace with '**' else let the column flow through

Ex : if LookUp.NOTFOUND then '**' else SourceTable.State

Get back if have problems doing this.

I tried like this, i generated it as a key and in column derivation I used it as,

if (SourceLink.State <> LookupLink.STT_CD) then '**' Else SourceLink.State

but this is not working

[narasimha]

Have you linked the key from the hashed file lookup to the source table column?

Then use this

Code: Select all

if LookupLink.NOTFOUND then '**' Else SourceTable.State 

[prasad111]

Have you linked the key from the hashed file lookup to the source table column?

They use this

Code: Select all

if LookupLink.NOTFOUND then '**' Else SourceTable.State 

It is working, it was mistake from my side.

Thanks a lot

[narasimha]

Can you mark it as resolved