DSXchange

I've been tasked with writing ETL server jobs using 1 source table & 3 lookup tables. I'm getting multiple results & DS is writing the 1st record to all fields in the output. My limited previous experience was with 6.0. Does the logic go in the transform stage under constraints or in the After of each input stage. The latest training class was cancelled & I cannot find the tutorial for DS 7.5. Reference material is clear as to how to do lookups. Please help.

Thanks,
Judy Bonser

Judy,

what are you trying to do? Do you want n-output rows or 1 output row per input row on your lookups?

Source table has SOURCE_ID - ABC and DEF
Lookup table has SOURCE_ID 1 SOURCE_DESC "ABC",
SOURCE_ID 2 SOURCE_DESC "DEF"

If source.SOURCE_ID = lookup.SOURCE_DESC then return lookup.SOURCE_ID
It should one source_id per record but DS is only finding the 1st record in the table & writing it to all records.

Thanks,
Judy

ArndW wrote:Judy,

what are you trying to do? Do you want n-output rows or 1 output row per input row on your lookups?

Here it is......
Source table has SOURCE_ID - ABC and DEF
Lookup table has SOURCE_ID 1 SOURCE_DESC "ABC",
SOURCE_ID 2 SOURCE_DESC "DEF"

If source.SOURCE_ID = lookup.SOURCE_DESC then return lookup.SOURCE_ID
It should one source_id per record but DS is only finding the 1st record in the table & writing it to all records.

Thanks,
Judy

In the transform, the column "lookup.SOURCE_DESC" should be marked as a KEY field and the value of "source.SOURCE_ID" should be in the key expression.

DataStage server jobs will only ever return the first match unless you enable multiple row return, which you can only do if the stage on the other end of your reference input link is an ODBC stage or a UV stage. Otherwise you need to find some other strategy, such as performing an outer join in the database itself.

I was able to resolve the issue using Oracle8i stage but I opted for hash files for the lookups instead due to performance. As long as I had the bind parameters correct. Thank you all for your assistance. Have a great day.
Judy :D