CODEWITHSUNDEEP
sqloracle11g
Nurman Ade
Nurman Ade

Reputation: 31

Join table in oracle database

I have 2 tables that showing data Item master and BOM. I would like to join the tables between Item master as T1 and BOM as T2 and the additional table for table BOM as T3. Item master table containing ITM_CD, ITM_TYP (1,2,3,4) where each ITM_TYP represents a code for the first digit on ITM_CD. The thing that I want is like the picture below

enter image description here

CHILD_CD2 value replace to CHILD_CD1 value. So the data should be like this. What query should I fix ? I am very new using oracle query.

enter image description here

Here is mycode;

SELECT DISTINCT
    T1.ITM_CD,
    T2.C_ITM_CD AS CHILD_CD1,
    T3.C_ITM_CD AS CHILD_CD2
FROM CM_HINMO_ALL T1
INNER JOIN (SELECT P_ITM_CD, C_ITM_CD, BOM_PTN FROM SM_BOM_ALL) T2 
ON T1.ITM_CD = T2.P_ITM_CD
        
LEFT  JOIN (SELECT P_ITM_CD, C_ITM_CD, BOM_PTN FROM SM_BOM_ALL) T3 
ON T2.C_ITM_CD = t3.P_ITM_CD
WHERE 0=0
AND T2.BOM_PTN IN (1)
AND T1.ITM_TYP IN (1,2)
AND T1.ITM_CD = '110100370'
ORDER BY 2

Upvotes: 0

Views: 62

Answers (2)

Sujitmohanty30
Sujitmohanty30

Reputation: 3316

If I understood, you want child_cd2 value should taken precedence over child_cd1 if available. If this assumption is right then we can use coalesce which returns the fist non null expression to achieve the same.

SELECT DISTINCT
    T1.ITM_CD,
    COALESCE(T3.C_ITM_CD,T2.C_ITM_CD) AS CHILD_CD1
  FROM CM_HINMO_ALL T1
  INNER JOIN SM_BOM_ALL T2 
     ON T1.ITM_CD = T2.P_ITM_CD
  LEFT JOIN SM_BOM_ALL T3 
     ON T2.C_ITM_CD = t3.P_ITM_CD
  WHERE T2.BOM_PTN IN (1)
    AND T1.ITM_TYP IN (1,2)
    AND T1.ITM_CD = '110100370'
ORDER BY 2

Upvotes: 1

Atif
Atif

Reputation: 2210

Just use Case expression to replace the values.

SELECT ITM_CD, CASE WHEN CHILD_CD2 IS NULL THEN CHILD_CD2 ELSE CHILD_CD1 END AS CHILD_CD1
    FROM TABLE1

Upvotes: 1

Related Questions