|
anchoredwisdom
Starting Member
20 Posts |
 Posted - 07/11/2012 : 05:49:54
|
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)
|
|
Issue with Join
Topic Locked
