• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 437
  • 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              500     03/02
1            2000     04/02
Tabel 3)
Columns
No        SQty       SDate     Customer
---------------------------------------------
1           100         04/02     ABC
1             50         05/02     XYZ
1           200         06/02     ABC
1           150         07/02     DEF


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

No       PQTY      PDATE            SQTY            SDATE        CUSTOMER
1          1000       01/02              100                  04/02        ABC
1            500       03/02              200                  06/02        ABC
1          2000      04/02

I want to show all the pqty for no=1 and all the sqty for no=1
and customer='ABC'  Thanks a lot for your help
0
VenkatBunny
Asked:
VenkatBunny
  • 20
  • 12
  • 4
  • +1
1 Solution
 
VenkatBunnyAuthor Commented:
The table 2 and Table 3 has other values as well

No
1
2
3

But I want to pull on 1
0
 
OP_ZaharinCommented:
try this:

SELECT Table1.No, Table2.PQTY, Table2.PDATE, Table3.SQTY, Table3.SDATE, Table3.CUSTOMER
FROM Table1
JOIN TABLE2 ON Table1.No = Table2.No
JOIN TABLE3 ON Table1.No = Table3.No
0
 
OP_ZaharinCommented:
- sorry left the WHERE clause

SELECT Table1.No, Table2.PQTY, Table2.PDATE, Table3.SQTY, Table3.SDATE, Table3.CUSTOMER
FROM Table1
JOIN TABLE2 ON Table1.No = Table2.No
JOIN TABLE3 ON Table1.No = Table3.No
WHERE Table1.No = '1'
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:
I got no records found message
0
 
OP_ZaharinCommented:
WHERE Table1.No = '1' AND Table3.CUSTOMER = 'ABC'
0
 
VenkatBunnyAuthor Commented:
SAME MESSAGE
0
 
VenkatBunnyAuthor Commented:
SORRY I Apologize I got 540 records which is supposed to be 18 records
0
 
Walter RitzelSenior Software EngineerCommented:
You  can try this:


select nvl(t2.no,t3.no), pqty, pdate, sqty, sdate, tab1.item
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
left join tab1 on nvl(t2.no,t3.no) = tab1.no
/
0
 
Walter RitzelSenior Software EngineerCommented:
and to ocmplement, add the where clause:
where tab1.no = '1'
0
 
VenkatBunnyAuthor Commented:
I want the enter the customer name and it should give the output
0
 
Walter RitzelSenior Software EngineerCommented:
select nvl(t2.no,t3.no), pqty, pdate, sqty, sdate, customer
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, customer, row_number() over(partition by no order by no, sdate) myrownum from tab3) t3
on t2.no = t3.no and t2.myrownum = t3.myrownum
left join tab1 on nvl(t2.no,t3.no) = tab1.no
where tab1.no = '1' and t3.customer = 'ABC'
0
 
OP_ZaharinCommented:
try this:

SELECT Table1.No, Table2.PQTY, Table2.PDATE, Table3.SQTY, Table3.SDATE, Table3.CUSTOMER
FROM Table1
JOIN TABLE3 ON Table3.No = Table1.No
JOIN TABLE2 ON Table2.No = Table3.No
WHERE Table1.No = '1' AND Table3.CUSTOMER = 'ABC'
0
 
VenkatBunnyAuthor Commented:
SAME 504 RECORDS
0
 
VenkatBunnyAuthor Commented:
wpcortes, some how I got 30 records with the sql u sent
0
 
Walter RitzelSenior Software EngineerCommented:
select nvl(t2.no,t3.no), pqty, pdate, sqty, sdate, customer
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, customer, row_number() over(partition by no order by no, sdate) myrownum from tab3 where customer='ABC') t3
on t2.no = t3.no and t2.myrownum = t3.myrownum
left join tab1 on nvl(t2.no,t3.no) = tab1.no
where tab1.no = '1'
0
 
VenkatBunnyAuthor Commented:
I want put all the sql into view and I want to pass parameters No and Customer
0
 
VenkatBunnyAuthor Commented:
I'm running the following query


select nvl(t2.no,t3.no),po,pqty,pdate,so,sqty,sdate,t3.party_name
from

(select null,no,pqty,pdate,po,row_number() over(partition by no order by no,pdate)myrownum from t2
--WHERE  no=1
)t2
full outer join
(select party_name,no,sqty,sdate,so,row_number() over(partition by no order by no,sdate)myrownum from t3
--WHERE NO=1 --and party_name like 'ABC''
)t3
on t2.no=t3.no and t2.myrownum=t3.myrownum


If I take the comments out the I'm getting the exact resuls, but I want to give the parameters outside
0
 
awking00Commented:
See attached.
query.txt
0
 
VenkatBunnyAuthor Commented:
I tried the attachment but I don't get any data for sqty,sdate,customer

where exists (select item from table1 t1
               where t1.item = t3.customer           joining item with customer ?
               and t1.no = &no
               and t1.item = &customer)
0
 
VenkatBunnyAuthor Commented:
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              500     03/02
1            2000     04/02
1              999     05/02
1              888     06/02
1              777     07/02
1              666     08/02
Tabel 3)
Columns
No        SQty       SDate     Customer
---------------------------------------------
1           100         04/02     ABC
1             50         05/02     XYZ
1           200         06/02     ABC
1           150         07/02     DEF
1           111         01/03    ABC
1           222         02/03    XXX
1           333         03/03    ZZZ

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

No       PQTY      PDATE            SQTY            SDATE        CUSTOMER
1          1000       01/02              100                  04/02        ABC
1            500       03/02              200                  06/02        ABC
1          2000       04/02              111                  01/03        ABC
1              999     05/02              --------------------------------------
1              888     06/02              --------------------------------------
1              777     07/02              --------------------------------------
1              666     08/02             ---------------------------------------


I want to show all the pqty for no=1 and all the sqty for no=1
and customer='ABC'  There are other customers for the same no=1
and I want customer as a parameter Thanks a lot for your help
0
 
Walter RitzelSenior Software EngineerCommented:

Select t.* from(
select nvl(t2.no,t3.no) as no, pqty,pdate,sqty,sdate,t3.party_name
from
(select no,pqty,pdate,row_number() over(partition by no order by no,pdate) myrownum from t2)t2
full outer join
(select party_name,no,sqty,sdate,row_number() over(partition by no order by no,sdate) myrownum from t3)t3
on t2.no=t3.no and t2.myrownum=t3.myrownum
) t
where t.no = 1 and party_name like 'ABC'
0
 
Walter RitzelSenior Software EngineerCommented:
In this case, the where would be like this

where t.no =1 and (party_name is null or party_name like 'ABC')
0
 
VenkatBunnyAuthor Commented:
I still get only ABC though I included and (party_name is null or party_name like 'ABC') in the query
0
 
awking00Commented:
Given your latest examples, isn't the attached what you want to see. I modified the data slightly since my test tables where using varchar2 and not date datatypes for the pdate and sdate, which made the 03 dates come before the 02 dates due to ascii sorting. However, if your sdate and pdate are of date datatype, then the query will still work.
proof.txt
0
 
VenkatBunnyAuthor Commented:
sorry wpcortes, The same Item was sold to different parties so the party name never null, I think the below condition will not work.

and (party_name is null or party_name like 'ABC')
0
 
VenkatBunnyAuthor Commented:
To, awking00:

I don't know I ran the same query you sent but I don't see any sqty,sdate and party name everything is null
0
 
Walter RitzelSenior Software EngineerCommented:
Venkat,

I know this is working, because was tested here on my oracle instance:
Select t.* from(
select nvl(t2.no,t3.no) as no, pqty,pdate,sqty,sdate,t3.party_name
from 
(select no,pqty,pdate,row_number() over(partition by no order by no,pdate) myrownum from tab2)t2
full outer join
(select party_name,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
) t
where t.no = 1 and (party_name like 'ABC' or party_name is null)

Open in new window


here is the result for the data I have on tables:
NO                     PQTY                   PDATE SQTY                   SDATE PARTY_NAME           
---------------------- ---------------------- ----- ---------------------- ----- -------------------- 
1                      1000                   01/02 100                    04/02 ABC                  
1                      2000                   02/02 50                     05/02                      
1                      500                    03/02 200                    06/02 ABC                  
1                                                   150                    07/02                  

Open in new window

0
 
Walter RitzelSenior Software EngineerCommented:
Also, let me explain: you are testing the party name null for the lines where you have information only on pqty and pdate... you should remember that you are getting 2 different tables and showing side by side... so, there could be more lines on t2 than on t3, thus generating lines where party name could be null.
0
 
VenkatBunnyAuthor Commented:


Table  2)
----------------------------
No         PQTY    PDate
----------------------------
1            1000     01/02
1              500     03/02
1            2000     04/02
1              999     05/02
1              888     06/02
1              777     07/02
1              666     08/02
Tabel 3)
Columns
No        SQty       SDate      Party Name
---------------------------------------------
1           100         04/02     ABC
1             50         05/02     XYZ            Different Customer but not null
1           200         06/02     ABC
1           150         07/02     DEF             Different Customer but not null
1           111         01/03    ABC
1           222         02/03    XXX             Different Customer but not null
1           333         03/03    ZZZ             Different Customer but not null

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

No       PQTY      PDATE            SQTY            SDATE        PARTY_NAME
1          1000       01/02              100                  04/02        ABC
1            500       03/02              200                  06/02        ABC
1          2000       04/02              111                  01/03        ABC
1              999     05/02              --------------------------------------
1              888     06/02              --------------------------------------
1              777     07/02              --------------------------------------
1              666     08/02             ---------------------------------------

party_name is null or party_name='ABC' since the party_name is never null and the first condition is failed and it is giving me only the ABC data
0
 
Walter RitzelSenior Software EngineerCommented:
And this is exactly what my query is delivering.
Unless you are changing it somehow... please post the exact query you are running and seeing only ABC party_names.
0
 
VenkatBunnyAuthor Commented:
Ourput If I put this condition

where t.no =1

No       PQTY      PDATE            SQTY            SDATE        PARTY_NAME
1          1000       01/02              100                  04/02        ABC
1            500       03/02              200                  06/02        ABC
1          2000       04/02              111                  01/03        ABC
1              999     05/02                50                  05/02        XYZ  
1              888     06/02              150                  07/02         DEF
1              777     07/02              222                  02/03         XXX
1              666     08/02             333                    03/03        ZZZ


Output If I put this condition

where t.no =1 and (party_name is null or party_name='ABC'

No       PQTY      PDATE            SQTY            SDATE        PARTY_NAME
1          1000       01/02              100                  04/02        ABC
1            500       03/02              200                  06/02        ABC
1          2000       04/02              111                  01/03        ABC

I  want the user to pass parameters for NO & Party it shoud give the below output for example
No : 1 and Party='ABC'

No       PQTY      PDATE            SQTY            SDATE        PARTY_NAME
1          1000       01/02              100                  04/02        ABC
1            500       03/02              200                  06/02        ABC
1          2000       04/02              111                  01/03        ABC
1              999     05/02              --------------------------------------
1              888     06/02              --------------------------------------
1              777     07/02              --------------------------------------
1              666     08/02             ---------------------------------------
0
 
Walter RitzelSenior Software EngineerCommented:
Well, not exactly. To make exactly like you want, you should put the filters on the internal queries:

select nvl(t2.no,t3.no) as no, pqty,pdate,sqty,sdate,t3.party_name
from
(select no,pqty,pdate,row_number() over(partition by no order by no,pdate) myrownum from tab2 where tab2.no = 1)t2
full outer join
(select party_name,no,sqty,sdate,row_number() over(partition by no order by no,sdate) myrownum from tab3 where tab3.no = 1 and party_name = 'ABC')t3
on t2.no=t3.no and t2.myrownum=t3.myrownum

And this should be the final version of the query. As I post previously, you can put this in a function or sp that returns a cursor and can receive parameters. But the position of the parameters should be exactly where they are.
0
 
VenkatBunnyAuthor Commented:
That is exactly right If I put the filters in the internal queries then it is working fine the way I want. How to pass parameters to Internal queries in this case

select party_name,no,sqty,sdate,row_number() over(partition by no order by no,sdate) myrownum from tab3 where tab3.no = 1 and party_name = 'ABC'

select no,pqty,pdate,row_number() over(partition by no order by no,pdate) myrownum from tab2 where tab2.no = 1
0
 
Walter RitzelSenior Software EngineerCommented:
How are you going to execute this query?
If you are using sqlplus, toad or sql developer, just replace the actual values by :<variable_name>, using the variable name you prefer.


0
 
VenkatBunnyAuthor Commented:
I'm executing using Toad and I can replace actual values by variables that is fine but I want to create a view and build a report from the view and create parameter to pass by end users that is my requirement
0
 
VenkatBunnyAuthor Commented:
Thanks!
0
 
Walter RitzelSenior Software EngineerCommented:
Did some research around views and the result is: you cannot have bind variables in views. So, as I said, you should create a function or even a procedure to achieve your goal.

Here is the error given when trying to create the view with the bind variables:
create or replace view vtab as
select nvl(t2.no,t3.no) as no, pqty,pdate,sqty,sdate,t3.party_name
from 
(select no,pqty,pdate,row_number() over(partition by no order by no,pdate) myrownum from tab2 where tab2.no = :pno)t2
full outer join
(select party_name,no,sqty,sdate,row_number() over(partition by no order by no,sdate) myrownum from tab3 where tab3.no = :pno and party_name = 'ABC')t3
on t2.no=t3.no and t2.myrownum=t3.myrownum



--and party_name like 'ABC'
Erro na Linha de Comando:2 Coluna:0
Relatório de Erro:
Erro de SQL: ORA-01027: bind variables not allowed for data definition operations
01027. 00000 -  "bind variables not allowed for data definition operations"
*Cause:    An attempt was made to use a bind variable in a SQL data definition
           operation.
*Action:   Such bind variables are not allowed.

Open in new window

0
 
VenkatBunnyAuthor Commented:
True you cannot have bind variables in views, I'm think how I can I pass parameters to Internal query.
0
 
awking00Commented:
>>I don't know I ran the same query you sent but I don't see any sqty,sdate and party name everything is null <<
Can you post the data that returned such results? Given the example data you gave, I still get the desired output you indicated and I would like to see what may be different.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 20
  • 12
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now