Home » SQL & PL/SQL » SQL & PL/SQL » Outer join restricts a condition (RDBMS : 11.1.0.7.0 Oracle Applications : 12.1.2)
| Outer join restricts a condition [message #633421] |
Wed, 18 February 2015 04:40  |
mmasoomansari
Messages: 2
Registered: February 2010
Location: multan
|
Junior Member |
|
|
Dear,
I built this query to find the GRN either their invoices have been made or not therefore I used outer join from rcv_transactions rcv table, but when I put the last check " aila.cancelled_flag<>'Y' " to only valid invoices means status should not be cancel, then it show only those GRN which has invoice ,obsolete GRN that has no invoice ,that's a problem for me.
I want to show those GRN too which has no invoice
RDBMS : 11.1.0.7.0
Oracle Applications : 12.1.2
------------------------------------
select grn_date,create_date,inward_no,receipt_num,quantit y_received,po_unit_price
,amount,ship_to_org_id,vendor_id,vendor_name,invoi ce_id,invoice_num,ap_amount,check_id
from
(
SELECT distinct
to_char(rcv.transaction_date,'dd-mm-yyyy') grn_date
,to_char(rcv.creation_date,'dd-mm-yyyy') create_date,
rcv.transaction_id,
rsh.attribute13 inward_no,
rsh.attribute13,rsh.receipt_num
,nvl(rcv.quantity,0) quantity_received
-- ,sum(nvl(rcv.quantity,0))* sum(rcv.po_unit_price) amount
,rcv.po_unit_price
,nvl(rcv.quantity,0)*rcv.po_unit_price amount
-- ,(aila.amount) amt
,rsh.ship_to_org_id
,rsh.vendor_id
,ven.vendor_name
,aia.invoice_id
,aia.invoice_num
,(select sum(amount) from ap_invoice_lines_all aila1 where rcv_transaction_id=rcv.transaction_id
) ap_amount
,aipa.check_id
--,aila.line_number,
,(SELECT max(TAX_RATE)
FROM ap.AP_TAX_CODES_ALL
WHERE name in (select tax_classification_code from ap_invoice_lines_all aila2 where aila2.rcv_transaction_id=rcv.transaction_id and aila2.invoice_id=aia.invoice_id))gst1
FROM rcv_transactions rcv,rcv_shipment_headers rsh,po_vendors ven
,ap_invoice_distributions_all aida,ap_invoices_all aia,ap_invoice_lines_all aila,ap_invoice_payments_all aipa
where rcv.transaction_type='RECEIVE'
AND (rcv.po_header_id,rcv.po_line_id) in (select rt.po_header_id,rt.po_line_id
from rcv_transactions rt where rt.po_header_id=rcv.po_header_id
and rt.po_line_id=rcv.po_line_id
and rt.SUBINVENTORY='STORE'
AND rt.transaction_type='DELIVER')
and rsh.shipment_header_id=rcv.shipment_header_id
and ven.vendor_id=rsh.vendor_id
and aida.rcv_transaction_id(+)=rcv.transaction_id
and aia.invoice_id(+)=aida.invoice_id
and aila.invoice_id(+)=aia.invoice_id
and aipa.invoice_id(+)=aia.invoice_id
[color=red]and aila.cancelled_flag<>'Y'[/color]
and rcv.organization_id= decode(_unit,null,rcv.organization_id,_unit)
--AND rsh.vendor_id = DECODE(:P_VENDOR,null,rsh.vendor_id,:P_VENDOR)
AND to_char(rcv.creation_date,'yyyy/mm/dd')||' 00:00:00' BETWEEN :P_Date_From AND :P_Date_To
)
MOD EDIT: fixed code tags
[Updated on: Wed, 18 February 2015 04:47] by Moderator Report message to a moderator |
|
|
|
| Re: Outer join restricts a condition [message #633423 is a reply to message #633421] |
Wed, 18 February 2015 04:51  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You need:
and aila.cancelled_flag(+)<>'Y'
That says if you find a matching record check the cancelled_flag <> 'Y'
The other way says check the cancelled_flag <> 'Y' regardless of if you've found a matching record. And if you haven't found a matching record then cancelled_flag is null, which isn't not equal to 'Y'
When doing an outer join using the non-ansi syntax you have to putt (+) next to every column from the outer-joined table in the where clause.
[Updated on: Wed, 18 February 2015 04:52] Report message to a moderator |
|
|
|
| Re: Outer join restricts a condition [message #633426 is a reply to message #633421] |
Wed, 18 February 2015 08:04 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
mmasoomansari wrote on Wed, 18 February 2015 05:40
AND to_char(rcv.creation_date,'yyyy/mm/dd')||' 00:00:00' BETWEEN :P_Date_From AND :P_Date_To
)
This is suspect.
Why convert a date to a character string and then compare it to what appears to be a date?
|
|
|
|
| Re: Outer join restricts a condition [message #633459 is a reply to message #633423] |
Thu, 19 February 2015 05:16 |
mmasoomansari
Messages: 2
Registered: February 2010
Location: multan
|
Junior Member |
|
|
cookiemonster wrote on Wed, 18 February 2015 04:51You need:
and aila.cancelled_flag(+)<>'Y'
Thanks, through above code result is as same as did not put the condition, problem solved after changing some logic and using union all
SELECT grn_date,
create_date,
inward_no,
receipt_num,
quantity_received,
po_unit_price,
Round(amount) amount,
ship_to_org_id,
vendor_id,
vendor_name,
invoice_id,
invoice_num,
ap_amount,
check_id,
Round(( ap_amount * awt_rate / 100 ), 0) awt,
gst_rate,
Round(( ap_amount * gst_rate / 100 ), 0) gst,
check_number,
awt_rate
FROM (SELECT DISTINCT To_char(rcv.transaction_date, 'dd-mm-yyyy')
grn_date,
To_char(rcv.creation_date, 'dd-mm-yyyy')
create_date,
rcv.transaction_id,
rsh.attribute13
inward_no,
rsh.attribute13,
rsh.receipt_num,
Nvl(rcv.quantity, 0)
quantity_received,
rcv.po_unit_price,
Nvl(rcv.quantity, 0) * rcv.po_unit_price
amount,
rsh.ship_to_org_id,
rsh.vendor_id,
ven.vendor_name,
aia.invoice_id,
aia.invoice_num,
(SELECT SUM(amount)
FROM ap_invoice_lines_all aila1
WHERE rcv_transaction_id = rcv.transaction_id)
ap_amount,
(SELECT Max(tax_rate)
FROM ap.ap_tax_codes_all
WHERE name IN (SELECT tax_classification_code
FROM ap_invoice_lines_all aila2
WHERE
aila2.rcv_transaction_id = rcv.transaction_id
AND aila2.invoice_id = aia.invoice_id))GST_RATE,
(SELECT tax_rate
FROM ap.ap_awt_tax_rates_all aatra
WHERE aatra.tax_name = (SELECT DISTINCT aag.name
FROM ap_awt_groups aag
WHERE
aag.group_id = (SELECT pay_awt_group_id
FROM ap_invoices_all aia2
WHERE
aia2.invoice_id = aia.invoice_id
))) AWT_rate,
aipa.check_id,
aca.check_number
FROM rcv_transactions rcv,
rcv_shipment_headers rsh,
po_vendors ven,
ap_invoice_distributions_all aida,
ap_invoices_all aia,
ap_invoice_lines_all aila,
ap_invoice_payments_all aipa,
ap_checks_all aca
WHERE rcv.transaction_type = 'RECEIVE'
AND ( rcv.po_header_id, rcv.po_line_id ) IN
(SELECT rt.po_header_id,
rt.po_line_id
FROM rcv_transactions rt
WHERE
rt.po_header_id = rcv.po_header_id
AND rt.po_line_id = rcv.po_line_id
AND rt.subinventory = 'STORE'
AND rt.transaction_type = 'DELIVER')
AND rsh.shipment_header_id = rcv.shipment_header_id
AND ven.vendor_id = rsh.vendor_id
AND aida.rcv_transaction_id(+) = rcv.transaction_id
AND aia.invoice_id(+) = aida.invoice_id
AND aila.invoice_id(+) = aia.invoice_id
AND aipa.invoice_id(+) = aia.invoice_id
AND aca.check_id(+) = aipa.check_id
AND aila.cancelled_flag <> 'Y'
--and rsh.attribute13='2550'--'1581' --'1555'
--and rsh.receipt_num='18749'
-- and aia.invoice_id=215598
AND rcv.organization_id =
Decode(:p_unit, NULL, rcv.organization_id,
:p_unit)
AND rsh.vendor_id = Decode(:P_VENDOR, NULL, rsh.vendor_id,
:P_VENDOR)
AND To_char(rcv.creation_date, 'yyyy/mm/dd')
||' 00:00:00' BETWEEN :P_Date_From AND :P_Date_To
UNION ALL
SELECT DISTINCT To_char(rcv.transaction_date, 'dd-mm-yyyy') grn_date,
To_char(rcv.creation_date, 'dd-mm-yyyy') create_date,
rcv.transaction_id,
rsh.attribute13 inward_no,
rsh.attribute13,
rsh.receipt_num,
Nvl(rcv.quantity, 0)
quantity_received,
rcv.po_unit_price,
Nvl(rcv.quantity, 0) * rcv.po_unit_price amount,
rsh.ship_to_org_id,
rsh.vendor_id,
ven.vendor_name,
NULL invoice_id,
NULL invoice_num,
NULL ap_amount,
NULL GST_RATE,
NULL awt_rate,
NULL check_id,
NULL check_number
FROM rcv_transactions rcv,
rcv_shipment_headers rsh,
po_vendors ven
WHERE rcv.transaction_type = 'RECEIVE'
AND ( rcv.po_header_id, rcv.po_line_id ) IN
(SELECT rt.po_header_id,
rt.po_line_id
FROM rcv_transactions rt
WHERE
rt.po_header_id = rcv.po_header_id
AND rt.po_line_id = rcv.po_line_id
AND rt.subinventory = 'STORE'
AND rt.transaction_type = 'DELIVER')
AND rsh.shipment_header_id = rcv.shipment_header_id
AND ven.vendor_id = rsh.vendor_id
AND rcv.transaction_id NOT IN (SELECT rcv_transaction_id
FROM
ap_invoice_distributions_all aida
WHERE
rcv.transaction_id = aida.rcv_transaction_id)
AND rcv.organization_id =
Decode(:p_unit, NULL, rcv.organization_id,
:p_unit)
AND rsh.vendor_id = Decode(:P_VENDOR, NULL, rsh.vendor_id,
:P_VENDOR)
AND To_char(rcv.creation_date, 'yyyy/mm/dd')
||' 00:00:00' BETWEEN :P_Date_From AND :P_Date_To)
Regards.
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 17:54:58 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
