Using lookups

[Ragunathan Gunasekaran]

Hi ,

There are product master and product inventory table .The product master table has the pcode and the desc of the product, this is a grouped table where 1 to 500 pcode in the inventory table will have a single entry in this table which will describe the description of the pcodes. The inventory table has the pcode ,stock in hand and re order level feilds.

How do i achieve a resultset that has the following fields "pcode ,stock in hand and Description "
Scenario:
select * from product master;
pcode desc
---------------
500 Edible oil

select * from product_inv;
Pcode stck_in_hnd rol
------------------------------
1 10 20
250 20 30
500 30 40


All these pcodes 1 ,250 and 500 will have an single entry in the product master table
I would like to have a resultset that has the following fields

pcode desc stck_in_hnd
-----------------------------------
1 Edible oil 10

How shall i achieve this . . . Please help me around

[chulett]

Use your first query to populate a hashed file keyed on pcode. Use your second to stream rows in and lookup the description from that same hashed file using pcode. Send the combination of the two out as the result.

Or simply join the two tables in your source query.

[Ragunathan Gunasekaran]

I have tried with that but there is a join issue here . DS is making a cartesian product of the master link and the lookup link .Hence there should be some way to make an exact join .
the Join should perform as given below

"The pode in the inventory table should fall in the master table entry and its corresponding descrption should be clubbed to output."

Some more input needed please

[chulett]

Don't see how there could be a join issue unless it is one of your own making. Hashed reference lookups are inner by nature, either an exact match or there's no match at all. Provide a detailed description of your job design, please.

[Ragunathan Gunasekaran]

Oracle Stage ------------------->
[Product_master]

Transformer----------------> Oracle stage

Oracle Stage ------------------->
[Product_inventory]

[ray.wurlod]

Constrain the output from the Transformer stage so that it only passes rows for which the lookup succeeds. It's not clear from your diagram, but the parent table needs to be the reference input. (Hint: encase your diagrams in Code tags, and use Preview to get the "ASCII art" right.)

[rafik2k]

Oracle Stage ------------------->
[Product_master]

Transformer----------------> Oracle stage

Oracle Stage ------------------->
[Product_inventory]

Where is your hash file/lookup table in your diagram? Follow the steps, what Craig mentioned in his reply.

As you said

Code: Select all

All these pcodes 1 ,250 and 500 will have [b]an single [/b]entry in the product master table 

even using source sql, you will get required result set.

[rafik2k]

Hi ,
Scenario:
select * from product master;
pcode desc
---------------
500 Edible oil

select * from product_inv;
Pcode stck_in_hnd rol
------------------------------
1 10 20
250 20 30
500 30 40

I would like to have a resultset that has the following fields

pcode desc stck_in_hnd
-----------------------------------
1 Edible oil 10

How shall i achieve this . . . Please help me around

BTW as per your sample data you will get result as

Code: Select all

pcode  desc        stck_in_hnd 
-----------------------------------      
1         Edible oil          30

not

Code: Select all

pcode  desc        stck_in_hnd 
-----------------------------------      
1         Edible oil          10

[Ragunathan Gunasekaran]

i have made the master table as the reference link and the inventory table as the primary link.How should i make the conditions in the transformer . . .

[rafik2k]

While creating hash file from table, create pcode as key.

In transformer join both table using pcode.

Just drag pcode from primary link and drop it into derivation of reference link in the transformer.
Then pass required column into outpu link

[Ragunathan Gunasekaran]

Hi,
The problem is that there is no upper limit lower limit demarcation in the master table hence its getting hard to take it via sql . . . I have tried it and failed

i have used the following querry

select pinv.*,pdesc.description from
product_description pdesc,
product_inventory pinv
where pdesc.pcode <=pinv.pcode
-- this SQL is forming a cartesian product of the tables used.

When u give "=" as the condition its just picking the rows only from the product_description table alone and clubs the description part corresponding to the pcode in the inventory table

But the requirement is that i need the description to be appended to all the records found in the inventory table

[rafik2k]

I am still confused in your requirement.

If need all records from inventory and their corresponding matching records from master table? if yes then your following sql will work perfect.

Code: Select all

select pinv.*,pdesc.description from 
product_description pdesc, 
product_inventory pinv 
where pdesc.pcode = pinv.pcode 

BTW what is relationship between the two tables(1-1,1-many or many-1, or many-many)?

[chulett]

select pinv.*,pdesc.description from
product_description pdesc,
product_inventory pinv
where pdesc.pcode <=pinv.pcode
-- this SQL is forming a cartesian product of the tables used.

No, it's not a 'cartesian product' - but I'm sure it's doing exactly what you told it to do and giving you way too many rows. You need an equi-join in the sql, but your description of what happened when you did that makes very little sense. Can you post examples of the 'clubbing'?

[ray.wurlod]

I think you need to attend some entry-level DataStage training.