[quote="pranay"]Not very sure about this one, but try changing the link order. It might work out fine. Let us know if this works fine.
had tried changing the links still no effect.
JOIN STAGE
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Charter Member
- Posts: 166
- Joined: Wed Mar 16, 2005 6:52 am
- Location: Mumbai, India
Sumit,
From what i understand of your issue:
You are attempting to do an inner join between 2 tables in datastage via the Join Stage.
The expectation is that the join should only extract rows that qualify( i.e. where invmst.sku# = invbal .sku# and invbal .store# = 250) from the INVMST stage.
However the actual result seen is that all data present from invmst table is extracted and sent to the join stage.
Apologies,
But the expectation is invalid.
The reason is: the join process executes on the datastage server and not the database server.
the job as designed will execute 2 sql queries on the database server:
1. Select SKU#, STORE# from INVBAL where STORE# = 250
2. Select SKU#, SKU_DESC from INVMST
and get the resultset to the datastage server. Please note that the resultset for SQL 2 is a full table select ( which what you report getting)
The join is executed on the two resultsets on the datastage server.
if you need to limit the rows fetched from the database server, the aqlternative is to perform the join on the database server by using the following job design
the odbc can have the sql mentioned in your post:
select a.sku#, a.store#, b.sku_desc from invbal a,invmst b where b.sku# = a.sku# and a.store# = 250
effectively doing away with the DS Join
IHTH
From what i understand of your issue:
You are attempting to do an inner join between 2 tables in datastage via the Join Stage.
The expectation is that the join should only extract rows that qualify( i.e. where invmst.sku# = invbal .sku# and invbal .store# = 250) from the INVMST stage.
However the actual result seen is that all data present from invmst table is extracted and sent to the join stage.
Apologies,
But the expectation is invalid.
The reason is: the join process executes on the datastage server and not the database server.
the job as designed will execute 2 sql queries on the database server:
1. Select SKU#, STORE# from INVBAL where STORE# = 250
2. Select SKU#, SKU_DESC from INVMST
and get the resultset to the datastage server. Please note that the resultset for SQL 2 is a full table select ( which what you report getting)
The join is executed on the two resultsets on the datastage server.
if you need to limit the rows fetched from the database server, the aqlternative is to perform the join on the database server by using the following job design
Code: Select all
ODBC------->{rest of the JOB}
select a.sku#, a.store#, b.sku_desc from invbal a,invmst b where b.sku# = a.sku# and a.store# = 250
effectively doing away with the DS Join
IHTH
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>