Oracle SQL

The table 2 and Table 3 has other values as well

No
1
2
3

But I want to pull on 1

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

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

I got no records found message

WHERE Table1.No = '1' AND Table3.CUSTOMER = 'ABC'

SAME MESSAGE

SORRY I Apologize I got 540 records which is supposed to be 18 records

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
/

and to ocmplement, add the where clause:
where tab1.no = '1'

I want the enter the customer name and it should give the output

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'

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'

SAME 504 RECORDS

wpcortes, some how I got 30 records with the sql u sent

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'

I want put all the sql into view and I want to pass parameters No and Customer

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

See attached.
query.txt

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)

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

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'

In this case, the where would be like this

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

I still get only ABC though I included and (party_name is null or party_name like 'ABC') in the query

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

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')

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

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)

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

Select allOpen in new window

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.

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

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.

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

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

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.

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

Thanks!

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.

Select allOpen in new window

True you cannot have bind variables in views, I'm think how I can I pass parameters to Internal query.

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