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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.