Need Help on Write the PLSQL

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
PRAVEEN TAsked:
Who is Participating?
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.

Pawan KumarDatabase ExpertCommented:
Can you please provide the table columns and how these tables are related(joined) , what columns you need in the fourth table.
0
PRAVEEN TAuthor Commented:
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
HainKurtSr. System AnalystCommented:
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
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!

Pawan KumarDatabase ExpertCommented:
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
PRAVEEN TAuthor Commented:
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
PRAVEEN TAuthor Commented:
And also .. how to add the formula based column in the same final table..
0
Pawan KumarDatabase ExpertCommented:
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
PRAVEEN TAuthor Commented:
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
PRAVEEN TAuthor Commented:
we need only one column in the final table that showing that row is mapped.
0
HainKurtSr. System AnalystCommented:
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
PRAVEEN TAuthor Commented:
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
HainKurtSr. System AnalystCommented:
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
HainKurtSr. System AnalystCommented:
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
PRAVEEN TAuthor Commented:
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
HainKurtSr. System AnalystCommented:
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
PRAVEEN TAuthor Commented:
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
HainKurtSr. System AnalystCommented:
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
PRAVEEN TAuthor Commented:
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
HainKurtSr. System AnalystCommented:
you want just matched rows right?
so it will be max(a,b), so 5K may be correct...
0
PRAVEEN TAuthor Commented:
I want the all the rows  in the final table
0
PRAVEEN TAuthor Commented:
plus there is additional column that mapped or not with the mapped table
0
HainKurtSr. System AnalystCommented:
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
PRAVEEN TAuthor Commented:
yes they are unique

in the final table should a+b right?
0
HainKurtSr. System AnalystCommented:
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
HainKurtSr. System AnalystCommented:
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
PRAVEEN TAuthor Commented:
PLEASE FIND ATTACCHED..
29062304_UPDATED.xlsx
0
HainKurtSr. System AnalystCommented:
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

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:
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
HainKurtSr. System AnalystCommented:
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
PRAVEEN TAuthor Commented:
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
HainKurtSr. System AnalystCommented:
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
PRAVEEN TAuthor Commented:
if I use that one .. I'm not able to use the sum of the number?
0
HainKurtSr. System AnalystCommented:
I am not sure where and how you are trying to do that...
are you trying to round and sum?
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.