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

I don't se the error.

Murray

asked 18 Jun '18, 17:09

murraysobol's gravatar image

murraysobol
(suspended)
accept rate: 16%

1

I don't se the error.

Without the sample schema + sample data I don't see the error either ( . •́ _ʖ •̀ .)

(18 Jun '18, 17:51) Vlad

The view consists of several UNIONs. Perhaps you can narrow down which SELECT block is reporting this error.

(19 Jun '18, 07:08) Chris Keating
1

TL;DR... :(

(21 Jun '18, 11:41) Volker Barth

Please ignore this question; consider it answered.

permanent link

answered 05 Jun '19, 10:41

murraysobol's gravatar image

murraysobol
(suspended)
accept rate: 16%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×103

question asked: 18 Jun '18, 17:09

question was seen: 4,800 times

last updated: 05 Jun '19, 10:41