Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need Help on Write the PLSQL

Posted on 2017-10-12
33
High Priority
?
44 Views
Last Modified: 2017-10-14
Hi ,

I have the four oracle tables, I need to write the sql the generate the fourth table by using the three table data.
Table 1) A_S
Table 2) B_S
Table 3) CROSS_REF

By using above three table data , we need to generate 4th table (Final_Table)

I will provide the table information..

Regards,
Pra
0
Comment
Question by:PRAVEEN T
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 14
  • 3
33 Comments
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 42329352
Can you please provide the table columns and how these tables are related(joined) , what columns you need in the fourth table.
0
 

Author Comment

by:PRAVEEN T
ID: 42329362
I just need to insert the record in the fourth table
where the column values from the 1st and 2nd table (Compare with  the cross_ref table)

table (A_S)

Item id      des      Qty   Price
A                AAA       1     9.50
B                BBB       2      7.00

table (B_S)

Item number     des      Qty   Price
X                 XX         1      8.00
Y                 YY         1       5.00
Z                 ZZ         1       4.00

cross_ref TABLE

MADE      Item number (B_S)   des(B_S)   Item_id(A_S)  des(A_S)  
nyc            X                                 XX                   A                   AA
nyc            y                                  yy                    B                  BB
nyc           z                                   zz                  no corss      


By using the sql , we need to generate 4 th table like this ( Only Matched one)

Itemnumber(B_S)  Des(B_S) Qty(B_S) Price(B_S) Itemnumber(a_S)  Des(a_S) Qty(a_S) Price(a_S)
x                                xx             1               8.00                 a                          aa           1             9.5
y                                yy              1              5.0 0                b                          bb           2             7.0                

Please let me know if  any questions
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 42329380
here, just adjust table names and column names

create table table4 as
select b.Itemnumber, b.des, b.price, a.item_id, a.des, a.price
  from cross_ref c
 inner join A_S a on c.itemnumber=a.itemnumber 
 inner join B_S b on c.itemnumber=b.itemnumber 

Open in new window


before comment out first line and make sure select returns what you want...
then un-comment first line to create your table with that result
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 42329428
Please try this -

Just try below it will give you the expected data in the final_table.

CREATE TABLE final_table
AS
SELECT
             b.Itemnumber as 'Itemnumber(B_S)'
            ,b.des as 'Des(B_S)'
            ,b.Qty as 'Qty(B_S)'
            ,b.price as 'Price(B_S)'
            ,a.item_id as 'Itemnumber(a_S)'
            ,a.des as 'Des(a_S)'
            ,a.Qty as 'Qty(a_S)'
            ,a.price as 'Price(a_S)'
 from A_S AS a
 INNER JOIN cross_ref AS c ON c.itemnumber = a.itemnumber
 INNER JOIN B_S AS b ON c.itemnumber = b.itemnumber
0
 

Author Comment

by:PRAVEEN T
Thank you for the reply.
By using the same tables..
Can we have the final out like this..

the final table should have the all the rows of A_S,B_S  Adding a column that say mapping not found of Y or N - like

Regards,
Praveen
0
 

Author Comment

by:PRAVEEN T
And also .. how to add the formula based column in the same final table..
0
 
LVL 32

Expert Comment

by:Pawan Kumar
Please try like this -

CREATE TABLE final_table
AS
SELECT 
             b.Itemnumber as 'Itemnumber(B_S)'
            ,b.des as 'Des(B_S)'
            ,b.Qty as 'Qty(B_S)'
            ,b.price as 'Price(B_S)'
			, CASE WHEN c.itemnumber IS NULL AND b.itemnumber IS NOT NULL THEN 'N' ELSE 'Y' END AS 'MappingFound(b_S)'
            ,a.item_id as 'Itemnumber(a_S)'
            ,a.des as 'Des(a_S)'
            ,a.Qty as 'Qty(a_S)'
            ,a.price as 'Price(a_S)'
			, CASE WHEN c.itemnumber IS NULL AND a.itemnumber IS NOT NULL THEN 'N' ELSE 'Y' END AS 'MappingFound(a_S)'
from A_S AS a 
LEFT JOIN cross_ref AS c ON c.itemnumber = a.itemnumber 
RIGHT JOIN B_S AS b ON c.itemnumber = b.itemnumber

Open in new window

0
 

Author Comment

by:PRAVEEN T
Hi Pawan
Thank you for the support

Is above query will work like that

a table as 5 rows.... b table as 7 rows
c table as 4 rows

the final table should be
5+7 = 12 rows
and 4 rows should the mapping column value as 'Y'

LET ME KNOW IF YOU HAVE ANY QUESTIONS..

Thankks
0
 

Author Comment

by:PRAVEEN T
we need only one column in the final table that showing that row is mapped.
0
 
LVL 60

Expert Comment

by:HainKurt
added 2 more columns that shows if it is mapped in TableA and TableB

create table table4 as
select b.Itemnumber, b.des, b.price, a.item_id, a.des, a.price,
       case when a.itemnumber is null then 'Y' else 'N' end Mapped_A,
       case when b.itemnumber is null then 'Y' else 'N' end Mapped_B
  from cross_ref c
 left join A_S a on c.itemnumber=a.itemnumber 
 left join B_S b on c.itemnumber=b.itemnumber

Open in new window

0
 

Author Comment

by:PRAVEEN T
Hi Hani Kurt,

I need only one column that should show 'Y' or 'N'

basically for the  fourth table driving table is a . final table we need the rows from a table and b table  and the additional column says 'Y' OR
'N' compared with c table
0
 
LVL 60

Expert Comment

by:HainKurt
what will be the value of new column?

  1. if it is in just A
  2. if it is in just B
  3. if it is in both
0
 
LVL 60

Expert Comment

by:HainKurt
are we comparing des columns? if yes, we can use

case when a.Des=c.DesA and b.Des=c.DesB then 'Y' else 'N' end MapAB

Open in new window


it will be Y if des columns match, N if not...
0
 

Author Comment

by:PRAVEEN T
do we need to use the full outer join to get the all the rows from a and b table ? or the above query is OK?
0
 
LVL 60

Expert Comment

by:HainKurt
you said just matching rows... so you should use inner joins...
ie, cross table should have both A and B

ie

create table table4 as
select b.Itemnumber, b.des, b.price, a.item_id, a.des, a.price,
       case when a.Des=c.DesA and b.Des=c.DesB then 'Y' else 'N' end MapAB
  from cross_ref c
 inner join A_S a on c.itemnumber=a.itemnumber 
 inner join B_S b on c.itemnumber=b.itemnumber

Open in new window

0
 

Author Comment

by:PRAVEEN T
No .. I need like this

rows from a  + rows from b
and in the new column in the  final table should show mapped 'Y' or 'N'

FOR EX:
TABLE A HAVE 5 ROWS
TABLE B HAVE 7 ROWS
MAPPING TABLE HAVE 4 ROWS

Final table should
5+7 = 12 rows and the new column should show 'Y' or 'N'
0
 
LVL 60

Expert Comment

by:HainKurt
this will do it... list all columns of A and B and give them unique names

create table table4 as
select a.Itemnumber itemNumberA, a.des desA, a.Price PriceA,...
       b.Itemnumber itemNumberB, b.des desB, b.Price PriceB,...
       case when a.Des=c.DesA and b.Des=c.DesB then 'Y' else 'N' end MapAB
  from cross_ref c
 inner join A_S a on c.itemnumber=a.itemnumber 
 inner join B_S b on c.itemnumber=b.itemnumber

Open in new window

0
 

Author Comment

by:PRAVEEN T
I am getting wrong row count
in my original table
table a 21397
table b 678
table c 678
new table is showing 5k rows

it should 21397+678 right?
0
 
LVL 60

Expert Comment

by:HainKurt
you want just matched rows right?
so it will be max(a,b), so 5K may be correct...
0
 

Author Comment

by:PRAVEEN T
I want the all the rows  in the final table
0
 

Author Comment

by:PRAVEEN T
plus there is additional column that mapped or not with the mapped table
0
 
LVL 60

Expert Comment

by:HainKurt
is a.itemnumber unique?
is b.itemnumber unique?

if they are unique, max rows will be the number of records in C, ie, new table should have max 678 records...
0
 

Author Comment

by:PRAVEEN T
yes they are unique

in the final table should a+b right?
0
 
LVL 60

Expert Comment

by:HainKurt
please create an excel showing some sample for

tableA
tableB
tableC

and result...
it is confusing this way...

here is excel, so you can fill it and we can write the query...
29062304.xlsx
0
 
LVL 60

Expert Comment

by:HainKurt
you can use full join

create table table4 as
select a.Itemnumber itemNumberA, a.des desA, a.Price PriceA,...
       b.Itemnumber itemNumberB, b.des desB, b.Price PriceB,...
       case when a.Des=c.DesA and b.Des=c.DesB then 'Y' else 'N' end MapAB,
       case when a.ID is null then 'No Match in A' else 'Match in A' end MatchA,
       case when b.ID is null then 'No Match in B' else 'Match in B' end MatchB
  from cross_ref c
  full join A_S a on c.itemnumber=a.itemnumber 
  full join B_S b on c.itemnumber=b.itemnumber

Open in new window


this gives max (A,B), ie 21397...
0
 

Author Comment

by:PRAVEEN T
PLEASE FIND ATTACCHED..
29062304_UPDATED.xlsx
0
 
LVL 60

Accepted Solution

by:
HainKurt earned 3000 total points
try this

select a.*, case when c.id is null then 'N' else 'Y' end mapped
from tableA a left join TableC c on a.id=c.id
union all
select b.*, case when c.id is null then 'N' else 'Y' end mapped
from tableB b left join TableC c on b.id=c.id

Open in new window

0
 

Author Comment

by:PRAVEEN T
first one is working fine..
I need one more clarification
I have some amount columns .. how can we control the value for 2 decimal


like
2.29
0
 
LVL 60

Expert Comment

by:HainKurt
how can we control the value for 2 decimal

I could not get what you ask for here :) normally, you format on your app not on db side...
0
 

Author Comment

by:PRAVEEN T
I mean we are getting the amount value like   0.23346.. I need to format it like
$ 23,34
any suggestion
is there any to_char()
can we use
0
 
LVL 60

Expert Comment

by:HainKurt
you can use

select '$ ' || trim(to_char(12.345, '9,990.00')) from dual
$ 12.35

Open in new window


or

select '$ ' || trim(replace(replace(replace(to_char(12.345, '9,990.00'),',','~'),'.',','),'~',',')) from dual
$ 12,35

Open in new window

0
 

Author Comment

by:PRAVEEN T
if I use that one .. I'm not able to use the sum of the number?
0
 
LVL 60

Expert Comment

by:HainKurt
I am not sure where and how you are trying to do that...
are you trying to round and sum?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question