My SQL is as follows: CREATE or replace VIEW ap_check_query_view AS WITH ReconciliationStatus (reconciliation_journal_nbr, check_register_nbr, reconciliation_status ) AS (SELECT DISTINCT fin_payment.reconciliation_journal_nbr, fin_payment.check_register_nbr, fin_payment.reconciliation_status FROM fin_payment WHERE payment_source IN ('APC','APP') AND fin_payment.payment_type IN ('K','V','N','C','Q','P','W','A') AND reconciliation_status IS NOT NULL AND reconciliation_journal_nbr IS NOT NULL AND advice_nbr IS NOT NULL ) SELECT fin_payment.customer_vendor_id AS vendor_id, s1_name_and_address_a.full_name AS vendor, fin_payment.bank_id, s1_name_and_address_b.full_name bank, CASE WHEN (COALESCE(fin_payment.payment_method_code,'C') = 'C' AND fin_payment.check_reference IS NOT NULL ) THEN s1_payment_method_lang.description ELSE 'Payment Advice' END AS payment_type, s1_payment_method_lang.description AS payment_method, fin_payment.check_reference AS check_number, fin_payment.advice_nbr AS payment_number, fin_payment.payment_date AS check_date, fin_payment.bank_amount, fin_payment.currency_code, s1_currency_lang.description AS currency, CASE WHEN (fin_payment.advice_nbr IS NOT NULL AND fin_payment.payment_type = 'V' ) THEN 'Voided' ELSE (CASE WHEN (fin_payment.advice_nbr IS NOT NULL AND fin_payment.payment_type <> 'V' ) THEN (CASE WHEN (fin_payment.reconciliation_status IS NULL OR fin_payment.reconciliation_status = 'U' ) THEN 'Unreconciled' ELSE 'Reconciled' END ) ELSE (CASE WHEN (fin_payment.payment_nbr < fin_payment.replacement_payment_nbr) THEN 'Reprinted' ELSE (CASE WHEN ((fin_payment.replacement_payment_nbr IS NULL OR fin_payment.payment_nbr >= fin_payment.replacement_payment_nbr ) AND fin_payment.payment_type IN ('V','W') ) THEN 'Voided' ELSE (CASE WHEN (fin_payment.payment_type = 'L') THEN 'Alignment' ELSE (CASE WHEN (fin_payment.reconciliation_status IS NULL OR fin_payment.reconciliation_status = 'U' ) THEN 'Unreconciled' ELSE 'Reconciled' END ) END ) END ) END ) END ) END AS status, fin_payment.check_register_nbr AS register_no, fin_gl_audit_trail.bank_reconciliation_date AS bank_rec, CAST(CASE WHEN COALESCE(s1_business_type.description,'') = '' THEN CAST (NULL AS varchar(50)) ELSE CASE WHEN COALESCE(s1_business_type.description,'') = 'Multi' THEN COALESCE(NULL,'Multi') ELSE s1_business_type.description END END AS varchar(50) ) AS business_type, CAST(COALESCE(naa_pay_loc.short_name,'Multi') AS varchar(10)) AS payment_location, fin_payment.ap_check_reference_nbr FROM fin_payment LEFT OUTER JOIN s1_payment_method_lang ON COALESCE(fin_payment.payment_method_code,'C') = s1_payment_method_lang.payment_method_code LEFT OUTER JOIN (SELECT DISTINCT fin_transaction.payment_nbr, fin_invoice.location_id, s1_name_and_address.short_name FROM fin_transaction JOIN fin_invoice ON fin_invoice.invoice_nbr = fin_transaction.invoice_nbr JOIN s1_name_and_address ON s1_name_and_address.name_and_address_id = fin_invoice.location_id ) inv_pay ON fin_payment.payment_nbr = inv_pay.payment_nbr LEFT OUTER JOIN ReconciliationStatus rs ON fin_payment.check_register_nbr = rs.check_register_nbr AND fin_payment.reconciliation_journal_nbr = rs.reconciliation_journal_nbr LEFT OUTER JOIN fin_gl_audit_trail ON fin_payment.payment_nbr = fin_gl_audit_trail.key_nbr AND fin_gl_audit_trail.source_type = 'P' AND fin_gl_audit_trail.bank_reconciliation_date IS NOT NULL LEFT OUTER JOIN s1_name_and_address naa_pay_loc ON fin_payment.location_id = naa_pay_loc.name_and_address_id, s1_name_and_address s1_name_and_address_a LEFT OUTER JOIN s1_name_and_address s1_name_and_address_d ON s1_name_and_address_a.marketing_company_id = s1_name_and_address_d.name_and_address_id JOIN s1_currency_lang ON fin_payment.currency_code = s1_currency_lang.currency_code LEFT OUTER JOIN (SELECT nbt.name_and_address_id, CASE WHEN COUNT(nbt.name_and_address_id) = 1 THEN MIN(bt.description) ELSE 'Multi' END AS description FROM s1_na_business_type nbt JOIN s1_business_type_lang bt ON nbt.business_type_code = bt.business_type_code GROUP BY nbt.name_and_address_id ) s1_business_type ON s1_name_and_address_a.name_and_address_id = s1_business_type.name_and_address_id, s1_name_and_address s1_name_and_address_b, s1_currency WHERE fin_payment.customer_vendor_id = s1_name_and_address_a.name_and_address_id AND fin_payment.internal_flag = 'N' AND fin_payment.bank_id = s1_name_and_address_b.name_and_address_id -- AND fin_payment.currency_code = s1_currency.currency_code AND (fin_payment.check_reference IS NOT NULL OR fin_payment.advice_nbr IS NOT NULL ) AND (fin_payment.replacement_payment_nbr IS NULL OR fin_payment.payment_source = 'GDC' ) AND fin_payment.financial_source = 'A/P' AND fin_payment.payment_type <> 'L' UNION SELECT fin_payment.customer_vendor_id AS vendor_id, s1_name_and_address_a.full_name AS vendor, fin_payment.bank_id, s1_name_and_address_b.full_name AS bank, CASE WHEN (COALESCE(fin_payment.payment_method_code, 'C') = 'C' AND fin_payment.check_reference IS NOT NULL ) THEN s1_payment_method_lang.description ELSE 'Payment Advice' END AS payment_type, s1_payment_method_lang.description AS payment_method, fin_payment.check_reference AS check_number, fin_payment.advice_nbr AS payment_number, fin_payment.payment_date AS check_date, fin_payment.bank_amount, fin_payment.currency_code, s1_currency_lang.description AS currency, CASE WHEN (fin_payment.advice_nbr IS NOT NULL AND fin_payment.payment_type = 'V' ) THEN 'Voided' ELSE (CASE WHEN (fin_payment.advice_nbr IS NOT NULL AND fin_payment.payment_type <> 'V' ) THEN (CASE WHEN (fin_payment.reconciliation_status IS NULL OR fin_payment.reconciliation_status = 'U' ) THEN 'Unreconciled' ELSE 'Reconciled' END ) ELSE (CASE WHEN (fin_payment.payment_nbr < fin_payment.replacement_payment_nbr) THEN 'Reprinted' ELSE (CASE WHEN ((fin_payment.replacement_payment_nbr IS NULL OR fin_payment.payment_nbr >= fin_payment.replacement_payment_nbr ) AND fin_payment.payment_type IN ('V','W') ) THEN 'Voided' ELSE (CASE WHEN (fin_payment.payment_type = 'L') THEN 'Alignment' ELSE (CASE WHEN (fin_payment.reconciliation_status IS NULL OR fin_payment.reconciliation_status = 'U' ) THEN 'Unreconciled' ELSE 'Reconciled' END ) END ) END ) END ) END ) END AS status, fin_payment.check_register_nbr AS register_no, fin_gl_audit_trail.bank_reconciliation_date AS bank_rec, CAST(CASE WHEN COALESCE(s1_business_type_lang.description,'') = '' THEN CAST (NULL AS varchar(50)) ELSE CASE WHEN COALESCE(s1_business_type_lang.description,'') = 'Multi' THEN COALESCE(NULL,'Multi') ELSE s1_business_type_lang.description END END AS varchar(50) ) AS business_type, CAST(COALESCE(naa_pay_loc.short_name,'Multi') AS varchar(10)) AS payment_location, fin_payment.ap_check_reference_nbr FROM fin_payment LEFT OUTER JOIN s1_payment_method_lang ON COALESCE(fin_payment.payment_method_code,'C') = s1_payment_method_lang.payment_method_code JOIN s1_currency_lang ON fin_payment.currency_code = s1_currency_lang.currency_code LEFT OUTER JOIN (SELECT DISTINCT fin_transaction.payment_nbr, fin_invoice.location_id, s1_name_and_address.short_name FROM fin_transaction JOIN fin_invoice ON fin_invoice.invoice_nbr = fin_transaction.invoice_nbr JOIN s1_name_and_address ON s1_name_and_address.name_and_address_id = fin_invoice.location_id ) inv_pay ON fin_payment.payment_nbr = inv_pay.payment_nbr LEFT OUTER JOIN fin_gl_audit_trail ON fin_payment.payment_nbr = fin_gl_audit_trail.key_nbr AND fin_gl_audit_trail.source_type = 'P' AND fin_gl_audit_trail.bank_reconciliation_date IS NOT NULL LEFT OUTER JOIN s1_name_and_address naa_pay_loc ON fin_payment.location_id = naa_pay_loc.name_and_address_id, s1_name_and_address s1_name_and_address_a LEFT OUTER JOIN s1_name_and_address s1_name_and_address_d ON s1_name_and_address_a.marketing_company_id = s1_name_and_address_d.name_and_address_id LEFT OUTER JOIN (SELECT nbt.name_and_address_id, CASE WHEN COUNT(nbt.name_and_address_id) = 1 THEN MIN(bt.description) ELSE 'Multi' END AS description FROM s1_na_business_type nbt JOIN s1_business_type_lang bt ON nbt.business_type_code = bt.business_type_code GROUP BY nbt.name_and_address_id ) s1_business_type ON s1_name_and_address_a.name_and_address_id = s1_business_type.name_and_address_id, s1_name_and_address s1_name_and_address_b, s1_currency WHERE fin_payment.customer_vendor_id = s1_name_and_address_a.name_and_address_id AND fin_payment.internal_flag = 'N' AND fin_payment.bank_id = s1_name_and_address_b.name_and_address_id -- AND fin_payment.currency_code = s1_currency.currency_code AND (fin_payment.check_reference IS NOT NULL OR fin_payment.advice_nbr IS NOT NULL ) AND fin_payment.replacement_payment_nbr <> fin_payment.payment_nbr AND fin_payment.financial_source = 'A/P' UNION SELECT NULL, NULL, fin_payment.bank_id, s1_name_and_address_b.full_name AS bank, CASE WHEN (COALESCE(fin_payment.payment_method_code,'C') = 'C' AND fin_payment.check_reference IS NOT NULL ) THEN s1_payment_method_lang.description ELSE 'Payment Advice' END AS payment_type, s1_payment_method_lang.description AS payment_method, fin_payment.check_reference AS check_number, fin_payment.advice_nbr AS payment_number, fin_payment.payment_date AS check_date, fin_payment.bank_amount, fin_payment.currency_code, s1_currency_lang.description AS currency, CASE WHEN (fin_payment.advice_nbr IS NOT NULL AND fin_payment.payment_type = 'V' ) THEN 'Voided' ELSE (CASE WHEN (fin_payment.advice_nbr IS NOT NULL AND fin_payment.payment_type <> 'V' ) THEN (CASE WHEN (fin_payment.reconciliation_status IS NULL OR fin_payment.reconciliation_status = 'U' ) THEN 'Unreconciled' ELSE 'Reconciled' END ) ELSE (CASE WHEN (fin_payment.payment_nbr < fin_payment.replacement_payment_nbr) THEN 'Reprinted' ELSE (CASE WHEN ((fin_payment.replacement_payment_nbr IS NULL OR fin_payment.payment_nbr >= fin_payment.replacement_payment_nbr ) AND fin_payment.payment_type IN ('V','W') ) THEN 'Voided' ELSE (CASE WHEN (fin_payment.payment_type = 'L') THEN 'Alignment' ELSE (CASE WHEN (fin_payment.reconciliation_status IS NULL OR fin_payment.reconciliation_status = 'U' ) THEN 'Unreconciled' ELSE 'Reconciled' END ) END ) END ) END ) END ) END AS status, fin_payment.check_register_nbr AS register_no, fin_gl_audit_trail.bank_reconciliation_date AS bank_rec, CAST(CASE WHEN COALESCE(s1_business_type_lang.description,'') = '' THEN CAST (NULL AS varchar(50)) ELSE CASE WHEN COALESCE(s1_business_type_lang.description,'') = 'Multi' THEN COALESCE(NULL,'Multi') ELSE s1_business_type_lang.description END END AS varchar(50) ) AS business_type, CAST(COALESCE(naa_pay_loc.short_name,'Multi') AS varchar(10)) AS payment_location, fin_payment.ap_check_reference_nbr FROM fin_payment LEFT OUTER JOIN s1_payment_method_lang ON COALESCE(fin_payment.payment_method_code,'C') = s1_payment_method_lang.payment_method_code JOIN s1_currency_lang ON fin_payment.currency_code = s1_currency_lang.currency_code LEFT OUTER JOIN (SELECT DISTINCT fin_transaction.payment_nbr, fin_invoice.location_id, s1_name_and_address.short_name FROM fin_transaction JOIN fin_invoice ON fin_invoice.invoice_nbr = fin_transaction.invoice_nbr JOIN s1_name_and_address ON s1_name_and_address.name_and_address_id = fin_invoice.location_id ) inv_pay ON fin_payment.payment_nbr = inv_pay.payment_nbr LEFT OUTER JOIN fin_gl_audit_trail ON fin_payment.payment_nbr = fin_gl_audit_trail.key_nbr AND fin_gl_audit_trail.source_type = 'P' AND fin_gl_audit_trail.bank_reconciliation_date IS NOT NULL LEFT OUTER JOIN s1_name_and_address naa_pay_loc ON fin_payment.location_id = naa_pay_loc.name_and_address_id, s1_name_and_address s1_name_and_address_b, s1_currency, s1_name_and_address s1_name_and_address_a LEFT OUTER JOIN s1_name_and_address s1_name_and_address_d ON s1_name_and_address_a.marketing_company_id = s1_name_and_address_d.name_and_address_id LEFT OUTER JOIN (SELECT nbt.name_and_address_id, CASE WHEN COUNT(nbt.name_and_address_id) = 1 THEN MIN(bt.description) ELSE 'Multi' END AS description FROM s1_na_business_type nbt JOIN s1_business_type_lang bt ON nbt.business_type_code = bt.business_type_code GROUP BY nbt.name_and_address_id ) s1_business_type ON s1_name_and_address_a.name_and_address_id = s1_business_type.name_and_address_id WHERE fin_payment.customer_vendor_id = s1_name_and_address_a.name_and_address_id AND fin_payment.internal_flag = 'N' AND fin_payment.bank_id = s1_name_and_address_b.name_and_address_id -- AND fin_payment.currency_code = s1_currency.currency_code AND (fin_payment.check_reference IS NOT NULL OR fin_payment.advice_nbr IS NOT NULL ) AND fin_payment.financial_source = 'A/P' AND fin_payment.payment_method_code IS NULL AND fin_payment.payment_date >= DATE(STRING('20151209')) AND s1_business_type.name_and_address_id IS NULL AND fin_payment.payment_type = 'L' ;However, it produces this error: could not execute statement. Illegal reference to correlation name 'fin_payment' SQLCODE=-824, ODBC 3 State="42502" Line 1, column 1 |
Please ignore this question; consider it answered. |
Without the sample schema + sample data I don't see the error either ( . •́ _ʖ •̀ .)
The view consists of several UNIONs. Perhaps you can narrow down which SELECT block is reporting this error.
TL;DR... :(