|
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'
;
|
|
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... :(