Need Help on Write the PLSQL

Can you please provide the table columns and how these tables are related(joined) , what columns you need in the fourth table.

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

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 

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

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

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

And also .. how to add the formula based column in the same final table..

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

Select allOpen in new window

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

we need only one column in the final table that showing that row is mapped.

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

Select allOpen in new window

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

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

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

Select allOpen in new window

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

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?

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

Select allOpen in new window

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'

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

Select allOpen in new window

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?

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

I want the all the rows  in the final table

plus there is additional column that mapped or not with the mapped table

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

yes they are unique

in the final table should a+b right?

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

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

Select allOpen in new window

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

PLEASE FIND ATTACCHED..
29062304_UPDATED.xlsx

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

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

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

you can use

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

Select allOpen in new window

or

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

Select allOpen in new window

if I use that one .. I'm not able to use the sum of the number?

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