• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

Oracle SQL

Hi Guru's,

I have the below requirement not sure if it is simple one or difficult.

I have 3 tables
------------------

Table 1
---------
Columns
No          Item
----------------------
1             ABC


Table  2)
----------------------------
No         PQTY    PDate
----------------------------
1            1000     01/02
1            2000     02/02
1              500     03/02

Tabel 3)
Columns
No        SQty       SDate
---------------------------
1           100         04/02
1             50         05/02
1           200         06/02
1           150         07/02


I want the output to be
----------------------------

No       PQTY      PDATE            SQTY            SDATE
1          1000       01/02              100                  04/02
1          2000       02/02                50                  05/02
1            500       03/02              200                  06/02
                                                  150                  07/02

The common join in No. If I join Table 2 and Table 3 I get multiple records
Can we do this in SQL ? If I am not clear please let me know..Thanks a lot for your help
0
VenkatBunny
Asked:
VenkatBunny
  • 6
  • 5
  • 4
1 Solution
 
slightwv (䄆 Netminder) Commented:
What defines what pQty and pDate goes with what sQty and Sdate?
0
 
VenkatBunnyAuthor Commented:
No relation basically

I want to see All the purchases and All the sales side by side though for the same Item
0
 
slightwv (䄆 Netminder) Commented:
Give this a try.  If it doesn't work just add records to the test case and explain why it fails.
drop table tab1 purge;
drop table tab2 purge;
drop table tab3 purge;

create table tab1(no number, item char(3));
create table tab2(no number, pqty number, pdate char(5));
create table tab3(no number, sqty number, sdate char(5));

insert into tab1 values(1,'ABC');

insert into tab2 values(1,1000,'01/02');
insert into tab2 values(1,2000,'02/02');
insert into tab2 values(1,500,'03/02');

insert into tab3 values(1,100,'04/02');
insert into tab3 values(1,50,'05/02');
insert into tab3 values(1,200,'06/02');
insert into tab3 values(1,150,'07/02');
commit;

select max(no), max(pqty), max(pdate), max(sqty), max(sdate)
from
(
select no, pqty, pdate, null sqty, null sdate, row_number() over(order by pdate) myrownum from tab2
union
select no, null pqty, null pdate, sqty, sdate, row_number() over(order by sdate) myrownum from tab3
)
group by myrownum
order by myrownum
/

Open in new window

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

 
VenkatBunnyAuthor Commented:
Sure I will try right away and will let you know thanks!
0
 
Walter RitzelSenior Software EngineerCommented:
I believe the last query should be like this:
select nvl(t2.no,t3.no), pqty, pdate, sqty, sdate
from
(select no, pqty, pdate, row_number() over(partition by no order by no, pdate) myrownum from tab2) t2
full outer join
(select no, sqty, sdate, row_number() over(partition by no order by no, sdate) myrownum from tab3) t3
on t2.no = t3.no and t2.myrownum = t3.myrownum
0
 
VenkatBunnyAuthor Commented:
I'm getting Invalid sdate error when I run the slightwv's sql
0
 
slightwv (䄆 Netminder) Commented:
>>I believe the last query should be like this:

I get the same results with mine versus yours.  If you run the plans, mine is more efficient.

>>I'm getting Invalid sdate error when I run the slightwv's sql

I ran what I posted against Oracle XE (10.2.0.1).

Can you post what you ran?
0
 
VenkatBunnyAuthor Commented:
Since I don't have much data I am not sure about performance at this point but Both slightwv's and wpcortes sql is working.I'm really very thankful to both you guys for getting this sql now, I am impressed with both the sql let me know which one to accept as solution...
0
 
slightwv (䄆 Netminder) Commented:
There's a quick gauge for seeing potential performance.  The explain plan.

Here's what I ran to see the plans.


explain plan for
select max(no), max(pqty), max(pdate), max(sqty), max(sdate)
from
(
select no, pqty, pdate, null sqty, null sdate, row_number() over(order by pdate) myrownum from tab2
union
select no, null pqty, null pdate, sqty, sdate, row_number() over(order by sdate) myrownum from tab3
)
group by myrownum
order by myrownum
/

SELECT * FROM TABLE(dbms_xplan.display);

explain plan for
select nvl(t2.no,t3.no), pqty, pdate, sqty, sdate
from
(select no, pqty, pdate, row_number() over(partition by no order by no, pdate) myrownum from tab2) t2
full outer join
(select no, sqty, sdate, row_number() over(partition by no order by no, sdate) myrownum from tab3) t3
on t2.no = t3.no and t2.myrownum = t3.myrownum
/

SELECT * FROM TABLE(dbms_xplan.display);

Open in new window

0
 
VenkatBunnyAuthor Commented:
Thanks!
0
 
Walter RitzelSenior Software EngineerCommented:
@slightwv, I tend do disagree that your solution has a best explain plan. In running your script on my system with Oracle 11g XE, my explain plan is better, with the lower cost:
plan FOR bem-sucedido.
PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
Plan hash value: 4146139992                                                                                                                                                                                                                                                                                  
                                                                                                                                                                                                                                                                                                             
-------------------------------------------------------------------------------                                                                                                                                                                                                                              
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                              
-------------------------------------------------------------------------------                                                                                                                                                                                                                              
|   0 | SELECT STATEMENT       |      |    14 |   882 |    11  (46)| 00:00:01 |                                                                                                                                                                                                                              
|   1 |  SORT GROUP BY         |      |    14 |   882 |    11  (46)| 00:00:01 |                                                                                                                                                                                                                              
|   2 |   VIEW                 |      |    14 |   882 |    10  (40)| 00:00:01 |                                                                                                                                                                                                                              
|   3 |    SORT UNIQUE         |      |    14 |   168 |    10  (70)| 00:00:01 |                                                                                                                                                                                                                              
|   4 |     UNION-ALL          |      |       |       |            |          |                                                                                                                                                                                                                              
|   5 |      WINDOW SORT       |      |     6 |    72 |     5  (40)| 00:00:01 |                                                                                                                                                                                                                              
|   6 |       TABLE ACCESS FULL| TAB2 |     6 |    72 |     3   (0)| 00:00:01 |                                                                                                                                                                                                                              
|   7 |      WINDOW SORT       |      |     8 |    96 |     5  (40)| 00:00:01 |                                                                                                                                                                                                                              
|   8 |       TABLE ACCESS FULL| TAB3 |     8 |    96 |     3   (0)| 00:00:01 |                                                                                                                                                                                                                              
-------------------------------------------------------------------------------                                                                                                                                                                                                                              

 15 linhas selecionadas 

plan FOR bem-sucedido.
PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
Plan hash value: 2120293379                                                                                                                                                                                                                                                                                  
                                                                                                                                                                                                                                                                                                             
----------------------------------------------------------------------------------                                                                                                                                                                                                                           
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                           
----------------------------------------------------------------------------------                                                                                                                                                                                                                           
|   0 | SELECT STATEMENT      |          |    14 |   924 |     9  (34)| 00:00:01 |                                                                                                                                                                                                                           
|   1 |  VIEW                 | VW_FOJ_0 |    14 |   924 |     9  (34)| 00:00:01 |                                                                                                                                                                                                                           
|*  2 |   HASH JOIN FULL OUTER|          |    14 |  1288 |     9  (34)| 00:00:01 |                                                                                                                                                                                                                           
|   3 |    VIEW               |          |     6 |   276 |     4  (25)| 00:00:01 |                                                                                                                                                                                                                           
|   4 |     WINDOW SORT       |          |     6 |    72 |     4  (25)| 00:00:01 |                                                                                                                                                                                                                           
|   5 |      TABLE ACCESS FULL| TAB2     |     6 |    72 |     3   (0)| 00:00:01 |                                                                                                                                                                                                                           
|   6 |    VIEW               |          |     8 |   368 |     4  (25)| 00:00:01 |                                                                                                                                                                                                                           
|   7 |     WINDOW SORT       |          |     8 |    96 |     4  (25)| 00:00:01 |                                                                                                                                                                                                                           
|   8 |      TABLE ACCESS FULL| TAB3     |     8 |    96 |     3   (0)| 00:00:01 |                                                                                                                                                                                                                           
----------------------------------------------------------------------------------                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                                                             
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                             
   2 - access("T2"."NO"="T3"."NO" AND "T2"."MYROWNUM"="T3"."MYROWNUM")                                                                                                                                                                                                                                       

 20 linhas selecionadas 

Open in new window

0
 
VenkatBunnyAuthor Commented:
Hi slightwv, I'm not talking about performance or explain plan here, please go through my requrement
0
 
Walter RitzelSenior Software EngineerCommented:
Venkat,
his answer still is correct, independent from the performance. You should keep his answer as the accepted one. I've just point out that his information on performance did not match what I see.
0
 
slightwv (䄆 Netminder) Commented:
Can I ask why the "B" grade?  The SQL does exactly what you asked.  If you needed more information you should have asked.

>> with Oracle 11g XE

I wouldn't go with a Beta just yet...  It can change.  Also, the cost column is more of a guideline.  I've seen higher costs perform better.
0
 
Walter RitzelSenior Software EngineerCommented:
so if the cost is just a guideline, and since the difference in bytes overall is negligible, you should not use this information to critique other expert answer. Also, I would like to point out that your answer, if consider that more than one no id exists, will not bring the correct result, while mine is, because I consider the join between the tables.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 6
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now