Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need Help on Write the PLSQL

Posted on 2017-10-12
33
High Priority
?
54 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
  • 16
  • 14
  • 3
33 Comments
 
LVL 37

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 61

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 37

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
ID: 42330159
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
ID: 42330179
And also .. how to add the formula based column in the same final table..
0
 
LVL 37

Expert Comment

by:Pawan Kumar
ID: 42330229
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
ID: 42330267
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
ID: 42330272
we need only one column in the final table that showing that row is mapped.
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 42330289
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
ID: 42330321
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 61

Expert Comment

by:HainKurt
ID: 42330330
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 61

Expert Comment

by:HainKurt
ID: 42330334
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
ID: 42330343
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 61

Expert Comment

by:HainKurt
ID: 42330346
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
ID: 42330349
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 61

Expert Comment

by:HainKurt
ID: 42330360
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
ID: 42330369
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 61

Expert Comment

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

Author Comment

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

Author Comment

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

Expert Comment

by:HainKurt
ID: 42330392
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
ID: 42330394
yes they are unique

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

Expert Comment

by:HainKurt
ID: 42330400
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 61

Expert Comment

by:HainKurt
ID: 42330405
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
ID: 42330420
PLEASE FIND ATTACCHED..
29062304_UPDATED.xlsx
0
 
LVL 61

Accepted Solution

by:
HainKurt earned 3000 total points
ID: 42330433
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
ID: 42330461
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 61

Expert Comment

by:HainKurt
ID: 42330477
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
ID: 42330493
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 61

Expert Comment

by:HainKurt
ID: 42330501
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
ID: 42330520
if I use that one .. I'm not able to use the sum of the number?
0
 
LVL 61

Expert Comment

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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Via a live example, show how to take different types of Oracle backups using RMAN.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

810 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