| Author |
 Topic  |
|
|
anchoredwisdom
Starting Member
20 Posts |
 Posted - 07/11/2012 : 05:42:19
|
Table 1
CURR_STS
LOB FP BRAND ITEM SUB_ITEM PART COST
--------------------------------------------------------------
LOB1 FP1 BRAND1 SKU1 10
LOB1 FP1 BRAND1 SKU1 MOD1 20
LOB1 FP1 BRAND1 SKU1 MOD1 PART1 30
TABLE 2
PRIOR_STS
LOB FP BRAND ITEM SUB_ITEM PART COST
-----------------------------------------------------------------
LOB1 FP1 BRAND1 SKU1 MOD1 PART2 400
LOB1 FP1 BRAND1 SKU1 MOD1 PART1 300
LOB1 FP1 BRAND1 SKU1 MOD1 200
LOB1 FP1 BRAND1 SKU1 100
LOB1 FP1 BRAND1 SKU2 MOD2 PART2 10
Expected Output
LOB ITEM SUB_ITEM PART CURRCOST PRIORCOST
-----------------------------------------------------------
LOB1 SKU1 10 100
LOB1 SKU1 MOD1 20 200
LOB1 SKU1 MOD1 PART1 30 300
LOB1 SKU1 MOD1 PART2 400
------------------------------------------------------------
THe row which containts item=sku2 ( from table prior_sts) should not come as it is not present in cur_sts.
I tried this query but it is not working
SELECT PR.LOB,
PR.ITEM,
PR.SUB_ITEM,
PR.PART,
PR.COST,
CR.COST
FROM PRIOR_STS PR LEFT JOIN CURR_STS CR
ON
PR.LOB=CR.LOB AND
PR.ITEM=CR.ITEM AND
(CASE PR.SUB_ITEM WHEN NULL THEN ‘A’ ELSE PR.SUB_ITEM END) = (CASE PR.SUB_ITEM WHEN NULL THEN ‘A’ ELSE PR.SUB_ITEM END) AND
(CASE PR.PART WHEN NULL THEN ‘A’ ELSE PR.PART END) = (CASE PR.PART WHEN NULL THEN ‘A’ ELSE PR.PART END) WHERE EXISTS
( SELECT ‘X’ FROM CURR_STS C WHERE C.ITEM=PRIOR_STS.ITEM)
|
|
|
bitsmed
Yak Posting Veteran
Denmark
99 Posts |
Posted - 07/11/2012 : 05:58:00
|
| change "left join" to "inner join" and remove the where section |
 |
|
|
anchoredwisdom
Starting Member
20 Posts |
Posted - 07/11/2012 : 08:33:02
|
Apologies for posting again.
It happened as there was some error after i clicked on submit.
I got the solution and i am posting the query below.
SELECT PR.*,CR.COST AS CURRENT_COST FROM
(
SELECT LOB,
ITEM,
(CASE WHEN SUB_ITEM IS NULL THEN 'X' ELSE SUB_ITEM END) SUBITEM,
(CASE WHEN PART IS NULL THEN 'Y' ELSE PART END) PART,
COST
FROM PRIOR_STS ) PR LEFT JOIN
(
SELECT LOB,
ITEM,
(CASE WHEN SUB_ITEM IS NULL THEN 'X' ELSE SUB_ITEM END) SUBITEM,
(CASE WHEN PART IS NULL THEN 'Y' ELSE PART END) PART,
COST
FROM CURR_STS ) CR
ON
PR.LOB=CR.LOB AND
PR.ITEM=CR.ITEM AND
PR.SUBITEM=CR.SUBITEM AND
PR.PART=CR.PART
WHERE EXISTS (SELECT 1 FROM CURR_STS C WHERE C.ITEM=PR.ITEM)
ORDER BY COST
|
|
|
| |
Topic |
|
|
|
Issue with Join
