Help on oracle SQL

Hi,

Please find attached excel sheet, I have four tables here.
We need to create the sql that will update billed column in the output_sql

if the mapped column is

Shield - the billled column is 0
Y-   the billed column is unit_price*shield_units
Else its join with the ol_po file for PO_BU_Region,PO_Quarter,PO_Year,Vchr_Amount
 copied into the set

Let me know any questions ..
test_data_UD.xlsx
PRAVEEN TAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Added UNION ALL. Updated Tested code for you.

SELECT 
	ITEM_NUMBER , MFG_ITM_ID , DESCRIPTION ,  MEMO ,  MAPPED , NEWCOLUMN
	, CASE WHEN newcol IS NULL THEN Vchr_Amount ELSE newcol END Build,o.PO_BU_Region
	,o.PO_Quarter,o.PO_Year 
FROM 
(
	SELECT ITEM_NUMBER , MFG_ITM_ID , DESCRIPTION ,  MEMO ,  MAPPED ,    NEWCOLUMN
	, CASE WHEN MAPPED = 'SHILD IN' THEN 0 
	       WHEN MAPPED = 'Y' THEN unit_price * Units END newcol
	FROM 
	(
		SELECT 
				a.ITEM_NUMBER AS Item_Number,
				b.MFG_ITM_ID AS MFG_ITM_ID,
				a.DESCI AS DESCRIPTION,
				b.MEMO AS Memo,
				'Y' Mapped, 
				'Y' NewColumn
				, b.unit_price
				,a.Units
			FROM CROSS_REF c
			INNER JOIN SHILD a on c.ITEM_NUMBER = a.ITEM_NUMBER
			INNER JOIN OL b on c.MFG_ITM_ID= b.MFG_ITM_ID
		UNION ALL
		SELECT 
					a.ITEM_NUMBER AS Item_Number,
					b.MFG_ITM_ID AS MFG_ITM_ID,
					a.DESCI AS DESCRIPTION,
					b.MEMO AS Memo,
					CASE 
						  WHEN c.ITEM_NUMBER = a.ITEM_NUMBER and c.MFG_ITM_ID= b.MFG_ITM_ID THEN 'Y'
					  WHEN (c.ITEM_NUMBER = a.ITEM_NUMBER) OR (b.MFG_ITM_ID IS NULL) THEN 'SHILD IN'
						  WHEN (c.MFG_ITM_ID= b.MFG_ITM_ID) OR (a.ITEM_NUMBER IS NULL)  THEN 'OL IN' 
						  ELSE 'N' 
					END Mapped, 
				  CASE 
						  WHEN c.ITEM_NUMBER = a.ITEM_NUMBER and c.MFG_ITM_ID= b.MFG_ITM_ID 
					  THEN 'OL and Shield'
					  ELSE NULL END NewColumn
					, b.unit_price
					,a.Units
			FROM CROSS_REF c
			FULL JOIN SHILD a on c.ITEM_NUMBER = a.ITEM_NUMBER
			FULL JOIN OL b on c.MFG_ITM_ID= b.MFG_ITM_ID  
	)
)r LEFT JOIN  ol_po o ON o.PO_ID = r.MFG_ITM_ID

Open in new window


OUTPUT

| ITEM_NUMBER | MFG_ITM_ID | DESCRIPTION |   MEMO |   MAPPED |     NEWCOLUMN | BUILD | PO_BU_REGION | PO_QUARTER | PO_YEAR |
|-------------|------------|-------------|--------|----------|---------------|-------|--------------|------------|---------|
|      (null) |        202 |      (null) |    GHI |    OL IN |        (null) |    28 |         reg1 |         Q1 |    2016 |
|      (null) |        202 |      (null) |    GHI |    OL IN |        (null) |   7.6 |         reg1 |         Q1 |    2016 |
|      (null) |        202 |      (null) |    GHI |    OL IN |        (null) |   7.6 |         reg1 |         Q1 |    2016 |
|      (null) |        203 |      (null) |    JKL |    OL IN |        (null) |   7.6 |         reg3 |         q4 |    2016 |
|      (null) |        204 |      (null) |    MNO |    OL IN |        (null) |   9.8 |         reg2 |         Q2 |    2017 |
|      (null) |        205 |      (null) |    PQR |    OL IN |        (null) |  11.9 |         reg6 |         Q2 |    2016 |
|         100 |     (null) |         AAA | (null) | SHILD IN |        (null) |     0 |       (null) |     (null) |  (null) |
|         103 |     (null) |         DDD | (null) | SHILD IN |        (null) |     0 |       (null) |     (null) |  (null) |
|         104 |     (null) |         EEE | (null) | SHILD IN |        (null) |     0 |       (null) |     (null) |  (null) |
|         102 |        201 |         CCC |    DEF |        Y | OL and Shield |    56 |       (null) |     (null) |  (null) |
|         102 |        201 |         CCC |    DEF |        Y |             Y |    56 |       (null) |     (null) |  (null) |
|         101 |        200 |         BBB |    ABC |        Y | OL and Shield | 52.65 |       (null) |     (null) |  (null) |
|         101 |        200 |         BBB |    ABC |        Y |             Y | 52.65 |       (null) |     (null) |  (null) |

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Since this is a continuation of your previous question, wouldn't it just be a similar case statement that you already have on similar columns?

You have provided the logic and in the previous questions you should know how the CASE statement works.

I realize you are wanting copy/paste solutions but you never learn with those.

Given the SQL in your previous question, what have you tried?
0
 
PRAVEEN TAuthor Commented:
Hi slightwv ,

Thank you for your replay.
It's not the same question, But i am using the same tables and there is one more additional table i was added in the spread sheet.

this one is the additional requirement of last question...

Last Question. I was asked for mapped column value..This question is based on mapped column value .. need to update the billed column..
by using the other file OL_PO..

I was tried with the case statement , But not able to work.. case statement needs the string .. but here i need the calculate value based on other two columns.. i am not familiar with sql . it's taking me more time to identify the solution

Please help me to resolve the issue..

Thank you


Regards.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Pawan KumarDatabase ExpertCommented:
Hi Parveen,
Please find the solution with tested output.

SOLUTION

SELECT ITEM_NUMBER , MFG_ITM_ID , DESCRIPTION ,  MEMO ,  MAPPED ,    NEWCOLUMN,
 CASE WHEN newcol IS NULL THEN Vchr_Amount ELSE newcol END Build
,o.PO_BU_Region,o.PO_Quarter,o.PO_Year 
FROM 
(
SELECT ITEM_NUMBER , MFG_ITM_ID , DESCRIPTION ,  MEMO ,  MAPPED ,    NEWCOLUMN
, CASE WHEN MAPPED = 'SHILD IN' THEN 0 
       WHEN MAPPED = 'Y' THEN unit_price * Units END newcol
FROM 
(
	SELECT 
			a.ITEM_NUMBER AS Item_Number,
			b.MFG_ITM_ID AS MFG_ITM_ID,
			a.DESCI AS DESCRIPTION,
			b.MEMO AS Memo,
			CASE 
				  WHEN c.ITEM_NUMBER = a.ITEM_NUMBER and c.MFG_ITM_ID= b.MFG_ITM_ID THEN 'Y'
			  WHEN (c.ITEM_NUMBER = a.ITEM_NUMBER) OR (b.MFG_ITM_ID IS NULL) THEN 'SHILD IN'
				  WHEN (c.MFG_ITM_ID= b.MFG_ITM_ID) OR (a.ITEM_NUMBER IS NULL)  THEN 'OL IN' 
				  ELSE 'N' 
			END Mapped, 
		  CASE 
				  WHEN c.ITEM_NUMBER = a.ITEM_NUMBER and c.MFG_ITM_ID= b.MFG_ITM_ID 
			  THEN 'OL and Shield'
			  ELSE NULL END NewColumn
  , b.unit_price
           ,a.Units
	FROM CROSS_REF c
	FULL JOIN SHILD a on c.ITEM_NUMBER = a.ITEM_NUMBER
	FULL JOIN OL b on c.MFG_ITM_ID= b.MFG_ITM_ID  
)
)r LEFT JOIN  ol_po o ON o.PO_ID = r.MFG_ITM_ID

Open in new window


OUTPUT

| ITEM_NUMBER | MFG_ITM_ID | DESCRIPTION |   MEMO |   MAPPED |     NEWCOLUMN | BUILD | PO_BU_REGION | PO_QUARTER | PO_YEAR |
|-------------|------------|-------------|--------|----------|---------------|-------|--------------|------------|---------|
|      (null) |        202 |      (null) |    GHI |    OL IN |        (null) |    28 |         reg1 |         Q1 |    2016 |
|      (null) |        202 |      (null) |    GHI |    OL IN |        (null) |   7.6 |         reg1 |         Q1 |    2016 |
|      (null) |        202 |      (null) |    GHI |    OL IN |        (null) |   7.6 |         reg1 |         Q1 |    2016 |
|      (null) |        203 |      (null) |    JKL |    OL IN |        (null) |   7.6 |         reg3 |         q4 |    2016 |
|      (null) |        204 |      (null) |    MNO |    OL IN |        (null) |   9.8 |         reg2 |         Q2 |    2017 |
|      (null) |        205 |      (null) |    PQR |    OL IN |        (null) |  11.9 |         reg6 |         Q2 |    2016 |
|         100 |     (null) |         AAA | (null) | SHILD IN |        (null) |     0 |       (null) |     (null) |  (null) |
|         103 |     (null) |         DDD | (null) | SHILD IN |        (null) |     0 |       (null) |     (null) |  (null) |
|         104 |     (null) |         EEE | (null) | SHILD IN |        (null) |     0 |       (null) |     (null) |  (null) |
|         102 |        201 |         CCC |    DEF |        Y | OL and Shield |    56 |       (null) |     (null) |  (null) |
|         101 |        200 |         BBB |    ABC |        Y | OL and Shield | 52.65 |       (null) |     (null) |  (null) |

Open in new window

0
 
PRAVEEN TAuthor Commented:
Hi Pawan,

Can you make changes on the following sql, that was provided on the last question by you.

create table FINAL_OUTPUT1 as
SELECT * FROM
(
      SELECT
            a.ITEM_NUMBER AS Item_Number,
            b.MFG_ITM_ID AS MFG_ITM_ID,
            a.DESCI AS DESCRIPTION,
            b.MEMO AS Memo,
            'Y' Mapped,
             'Y' NewColumn
      FROM CROSS_REF c
      INNER JOIN SHILD a on c.ITEM_NUMBER = a.ITEM_NUMBER
      INNER JOIN OL b on c.MFG_ITM_ID= b.MFG_ITM_ID
      UNION ALL
      SELECT
                  a.ITEM_NUMBER AS Item_Number,
                  b.MFG_ITM_ID AS MFG_ITM_ID,
                  a.DESCI AS DESCRIPTION,
                  b.MEMO AS Memo,
                  CASE
                          WHEN c.ITEM_NUMBER = a.ITEM_NUMBER and c.MFG_ITM_ID= b.MFG_ITM_ID THEN 'Y'
                    WHEN (c.ITEM_NUMBER = a.ITEM_NUMBER) OR (b.MFG_ITM_ID IS NULL) THEN 'SHILD IN'
                          WHEN (c.MFG_ITM_ID= b.MFG_ITM_ID) OR (a.ITEM_NUMBER IS NULL)  THEN 'OL IN'
                          ELSE 'N'
                  END Mapped,
              CASE
                          WHEN c.ITEM_NUMBER = a.ITEM_NUMBER and c.MFG_ITM_ID= b.MFG_ITM_ID
                    THEN 'OL and Shield'
                    ELSE NULL END NewColumn
      FROM CROSS_REF c
      FULL JOIN SHILD a on c.ITEM_NUMBER = a.ITEM_NUMBER
      FULL JOIN OL b on c.MFG_ITM_ID= b.MFG_ITM_ID
);
0
 
PRAVEEN TAuthor Commented:
I don't see the union all from the last sql..
0
 
PRAVEEN TAuthor Commented:
Working
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.