Need help on Oracle SQL

Hi Experts,

I need help on update the existing sql based on below requirement.

I have a three tables

1) Shield
2) OL
3) Cross_Ref

I need to create the new table ' Result' by using above table data. Here i am sharing the table data, and the SQL i have created .
Basically i need help on update that sql to get final output showed in the excel file.

Please let me know if  any questions.
test_data.xlsx
PRAVEEN TAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Below is working for me -

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
);

SELECT * FROM FINAL_OUTPUT1

Open in new window

0
 
Ryan ChongCommented:
quick try... (not tested)

with cte as
(
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 
from CR0SS_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
)
select * from cte
union all
select * from cte where Mapped = 'Y'
order by nvl(Description, '999999'), Item_Number

Open in new window


if it's not working , try remove the "Order By" clause first to see if you get the "duplicated rows"?
0
 
Pawan KumarDatabase ExpertCommented:
Hey Ryan,  Thats not working :)

Hi Praveen - Please try full tested solution for Oracle. Please let us know if you need any changes.

Data Generation -

CREATE TABLE SHILD
(
     ITEM_NUMBER INTEGER
    ,DESCI VARCHAR2(10)
);

INSERT INTO SHILD VALUES (100,'AAA');
INSERT INTO SHILD VALUES (101,'BBB');
INSERT INTO SHILD VALUES (102,'CCC');
INSERT INTO SHILD VALUES (103,'DDD');
INSERT INTO SHILD VALUES (104,'EEE');

CREATE TABLE OL
(
    MFG_ITM_ID INTEGER
   ,MEMO VARCHAR2(10)
);  

INSERT INTO OL VALUES (200,'ABC');
INSERT INTO OL VALUES (201,'DEF');
INSERT INTO OL VALUES (202,'GHI');
INSERT INTO OL VALUES (203,'JKL');
INSERT INTO OL VALUES (204,'MNO');
INSERT INTO OL VALUES (205,'PQR');

CREATE TABLE CROSS_REF
(
   ITEM_NUMBER	INTEGER
  ,DESCI	VARCHAR2(10)
  ,MFG_ITM_ID	INTEGER 
  ,MEMO VARCHAR2(10)
);  

INSERT INTO CROSS_REF VALUES ( 101,'BBB',	200,	'ABC');
INSERT INTO CROSS_REF VALUES ( 102	,'CCC',	201	,'DEF');

Open in new window


Solution

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 
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 
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

Open in new window


Output

| ITEM_NUMBER | MFG_ITM_ID | DESCRIPTION |   MEMO |   MAPPED |
|-------------|------------|-------------|--------|----------|
|         101 |        200 |         BBB |    ABC |        Y |
|         102 |        201 |         CCC |    DEF |        Y |
|         101 |        200 |         BBB |    ABC |        Y |
|         102 |        201 |         CCC |    DEF |        Y |
|      (null) |        202 |      (null) |    GHI |    OL IN |
|      (null) |        203 |      (null) |    JKL |    OL IN |
|      (null) |        204 |      (null) |    MNO |    OL IN |
|      (null) |        205 |      (null) |    PQR |    OL IN |
|         104 |     (null) |         EEE | (null) | SHILD IN |
|         103 |     (null) |         DDD | (null) | SHILD IN |
|         100 |     (null) |         AAA | (null) | SHILD IN |

Open in new window

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.

 
PRAVEEN TAuthor Commented:
Hi Pawan,

It's working fine.
I need a change on the sql.
can we add one more column that says that set value 'OL and Shield' when it's  the 'Y'


Thank you.
0
 
slightwv (䄆 Netminder) Commented:
Wouldn't that just be chaning the case that generates the 'Y' to the string you want?

Change:
WHEN c.ITEM_NUMBER = a.ITEM_NUMBER and c.MFG_ITM_ID= b.MFG_ITM_ID THEN 'Y'

To:
WHEN c.ITEM_NUMBER = a.ITEM_NUMBER and c.MFG_ITM_ID= b.MFG_ITM_ID THEN 'OL and Shield'
0
 
Pawan KumarDatabase ExpertCommented:
Hi Parveen,
Please find the updated solution below. As of now I have named the column as NewColumn, you can rename it as per your choice.

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, 
     'OL and Shield' 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

Open in new window


Output
| ITEM_NUMBER | MFG_ITM_ID | DESCRIPTION |   MEMO |   MAPPED |     NEWCOLUMN |
|-------------|------------|-------------|--------|----------|---------------|
|         101 |        200 |         BBB |    ABC |        Y | OL and Shield |
|         102 |        201 |         CCC |    DEF |        Y | OL and Shield |
|         101 |        200 |         BBB |    ABC |        Y | OL and Shield |
|         102 |        201 |         CCC |    DEF |        Y | OL and Shield |
|      (null) |        202 |      (null) |    GHI |    OL IN |        (null) |
|      (null) |        203 |      (null) |    JKL |    OL IN |        (null) |
|      (null) |        204 |      (null) |    MNO |    OL IN |        (null) |
|      (null) |        205 |      (null) |    PQR |    OL IN |        (null) |
|         104 |     (null) |         EEE | (null) | SHILD IN |        (null) |
|         103 |     (null) |         DDD | (null) | SHILD IN |        (null) |
|         100 |     (null) |         AAA | (null) | SHILD IN |        (null) |

Open in new window

0
 
PRAVEEN TAuthor Commented:
Hi Pawan,
I need one more change. The duplicate row we are creating that row only we need to set 'OL and Shield' other row value same as 'Y'

101 -Y
102 -Y
5 TH ROW 101- OL and shield
6 th row    102  - OL and shield


Thank you fro your help
0
 
Pawan KumarDatabase ExpertCommented:
Hi Parveen,

Updated Solution as required.

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

Open in new window


Output
| ITEM_NUMBER | MFG_ITM_ID | DESCRIPTION |   MEMO |   MAPPED |     NEWCOLUMN |
|-------------|------------|-------------|--------|----------|---------------|
|         101 |        200 |         BBB |    ABC |        Y |             Y |
|         102 |        201 |         CCC |    DEF |        Y |             Y |
|         101 |        200 |         BBB |    ABC |        Y | OL and Shield |
|         102 |        201 |         CCC |    DEF |        Y | OL and Shield |
|      (null) |        202 |      (null) |    GHI |    OL IN |        (null) |
|      (null) |        203 |      (null) |    JKL |    OL IN |        (null) |
|      (null) |        204 |      (null) |    MNO |    OL IN |        (null) |
|      (null) |        205 |      (null) |    PQR |    OL IN |        (null) |
|         104 |     (null) |         EEE | (null) | SHILD IN |        (null) |
|         103 |     (null) |         DDD | (null) | SHILD IN |        (null) |
|         100 |     (null) |         AAA | (null) | SHILD IN |        (null) |

Open in new window

0
 
PRAVEEN TAuthor Commented:
Hi Pawan,

I am getting the error when we add the create table with the sql.
Can you please check it.

create table FINAL_OUTPUT as
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
 
Pawan KumarDatabase ExpertCommented:
Can you pls post the error. Also please try this -

create table FINAL_OUTPUT 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
);

Open in new window

0
 
PRAVEEN TAuthor Commented:
Hi Pawan,

I am getting below error, when i try with my original SQL
Error report -
ORA-01789: query block has incorrect number of result columns
01789. 00000 -  "query block has incorrect number of result columns"
*Cause:    
*Action:

Can you please check it
0
 
Pawan KumarDatabase ExpertCommented:
Hi Parveen,

>>I am getting below error, when i try with my original SQL
You have to use the last query I gave.

I am not getting any error. Please check at testing at - http://sqlfiddle.com/#!4/8e101/28
0
 
Ryan ChongCommented:
this is working well for me (tested using Oracle Live SQL):

with cte as
(
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 
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
), cte2 as
(
select * from cte 
union all
select * from cte where Mapped = 'Y'
)
select * from cte2
order by nvl(MFG_ITM_ID, 99999) --, Item_Number

Open in new window

0
 
PRAVEEN TAuthor Commented:
Hi pawan,

its working fine .
i need one more change on this.

we need to create one more calc column as 'billed'
this value is based on mapped column
if mapped column value is
shield - the new column values is 0
y - is it unit_price*sheild*units
else its join wih the another file (PO) and copied these values to set
location
qurter
year
po_amt

let me know if you have any questions

Regards
0
 
PRAVEEN TAuthor Commented:
if you need .. i will send you updated excell and other PO file
0
 
PRAVEEN TAuthor Commented:
its working fne
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.