Home » SQL & PL/SQL » SQL & PL/SQL » Invalid column name??
| Invalid column name?? [message #23680] |
Thu, 26 December 2002 12:26  |
annie
Messages: 30
Registered: March 2000
|
Member |
|
|
I don't understand why I get an invalid column name at this line: SUM(DECODE(i1.payment_status_flag,'Y',i1.invoice_amount,'N',0)),
in the query. It's saying i1.invoice_amount is an invalid column name and I don't understand why. Please help.
INSERT INTO ap_duplicate_vendors (
entry_id,
vendor_id,
duplicate_vendor_id,
vendor_site_id,
duplicate_vendor_site_id,
number_unpaid_invoices,
number_paid_invoices,
number_po_headers_changed,
amount_unpaid_invoices,
amount_paid_invoices,
last_update_date,
last_updated_by,
process_flag,
process,
keep_site_flag,
paid_invoices_flag,
last_update_login,
creation_date,
created_by,
org_id )
SELECT
ap_duplicate_vendors_s.NEXTVAL,all1.vendor_id, all2.vendor_id,
all1.vendor_site_id,all2.vendor_site_id,
count(DECODE(i1.payment_status_flag,'N',i1.payment_status_flag,'Y',0)),
count(DECODE(i1.payment_status_flag,'Y',i1.payment_status_flag,'N',0)),
i1.po_header_id,SUM(DECODE(i1.payment_status_flag,'N',i1.invoice_amount,'Y',0)),
SUM(DECODE(i1.payment_status_flag,'Y',i1.invoice_amount,'N',0)),
SYSDATE, 2326,'Y','I','N','Y',to_number(Fnd_Profile.VALUE('USER_ID')),
SYSDATE,2326,3
FROM
(SELECT v.vendor_name,v.vendor_id,
v.segment1 ven_number,pvs.vendor_site_id,v.end_date_active ed,
v.payment_method_lookup_code method,p.creation_date,
count(i1.invoice_id) ic,MAX(i1.invoice_date) id,
p.national_identifier,p.employee_number, p.person_id
FROM
ap_invoices i1,po_vendors v,per_all_people_f p,po_vendor_sites pvs
WHERE v.employee_id = p.person_id
AND v.vendor_id = pvs.vendor_id
AND p.national_identifier IN (
select national_identifier
from per_all_people_f where business_group_id = 1
group by national_identifier having count(distinct person_id) > 1
) --Changed >2 to >1 this makes the query pick vendors that have
-- 2 suppliers set up in the suppliers table that need to be merged
-- as oppossed to 3 with >2.
AND i1.vendor_id (+) = v.vendor_id
group by v.vendor_name,v.vendor_id,v.segment1,pvs.vendor_site_id,
v.end_date_active,v.payment_method_lookup_code,
p.national_identifier,p.creation_date, p.employee_number, p.person_id )all1,
(SELECT v.vendor_name,v.vendor_id,
v.segment1 ven_number,pvs.vendor_site_id,v.end_date_active ed,
v.payment_method_lookup_code method,p.creation_date,
count(i2.invoice_id) ic,MAX(i2.invoice_date) id,
p.national_identifier,p.employee_number, p.person_id
FROM
ap_invoices i2,po_vendors v,per_all_people_f p,po_vendor_sites pvs
WHERE v.employee_id = p.person_id
AND v.vendor_id = pvs.vendor_id
AND p.national_identifier IN (
select national_identifier
from per_all_people_f where business_group_id = 1
group by national_identifier having count(distinct person_id) > 1
) --Changed >2 to >1 this makes the query pick vendors that have
-- 2 suppliers set up in the suppliers table that need to be merged
-- as oppossed to 3 with >2.
AND i2.vendor_id (+) = v.vendor_id
group by v.vendor_name,v.vendor_id,v.segment1,pvs.vendor_site_id,
v.end_date_active,v.payment_method_lookup_code,
p.national_identifier,p.creation_date, p.employee_number, p.person_id )all2
WHERE all1.national_identifier = all2.national_identifier
AND all1.creation_date > all2.creation_date
--AND all1.creation_date = (
-- SELECT MAX(creation_date) FROM per_all_people_f
-- WHERE national_identifier = all1.national_identifier)
and all1.ed is null
and all2.ed is null
ORDER BY all1.vendor_name
|
|
|
|
| Re: Invalid column name?? [message #23683 is a reply to message #23680] |
Thu, 26 December 2002 15:48  |
andy
Messages: 92
Registered: December 1999
|
Member |
|
|
Your local alias 'i1' for table ap_invoices is
defined within the inline view 'all1' and is not
visible at that scope. The error occurs at the
last instance because Oracle seems to validate from
the last to the first. You would need to include
all the i1.* columns in the select clause of all1
to fix the errors. Also, you may want to change:
count(DECODE(i1.payment_status_flag,'N',i1.payment_status_flag,'Y',0)),
count(DECODE(i1.payment_status_flag,'Y',i1.payment_status_flag,'N',0)),
to:
sum(DECODE(i1.payment_status_flag,'N',1,0)),
sum(DECODE(i1.payment_status_flag,'Y',1,0)),
Hope that helps. Good luck!
Andy
|
|
|
|
Goto Forum:
Current Time: Fri May 17 10:33:30 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
