LEFT OUTER JOIN with several tables and conditions.

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Wed, 06 Apr 2011 6:12 pm

I have the below code that brings information on notes payable and not and also amounts paid and date of payment in AP, but there is an error and I need help to fix.

The following code brings all the notes released in the AP_INVOICES_ALL and their respective relationships with the tables mentioned in across the field (INVOICE_ID).

FROM AP_INVOICES_ALL LEFT OUTER JOIN AP_PAYMENT_SCHEDULES_ALL AIA APSA USING (INVOICE_ID) LEFT OUTER JOIN AP_INVOICE_PAYMENTS_ALL AIPA USING (INVOICE_ID) LEFT OUTER JOIN ap_invoice_distributions_all AIDA USING (INVOICE_ID) LEFT OUTER JOIN AP_CHECKS_ALL ACA USING (CHECK_ID) Brings all the records in AP_INVOICES_ALL AIA (left) or no data in tables related below with the field invoice_id: AP_PAYMENT_SCHEDULES_ALL AP_INVOICE_PAYMENTS_ALL ap_invoice_distributions_all AP_CHECKS_ALL AIDA APSA ACA Up there all right the relationship is always 1) INVOICE_ID to 01 or more (many): AP_PAYMENT_SCHEDULES_ALL-Agerndado AP_INVOICE_PAYMENTS_ALL-APSA AIPA--AIDA-ap_invoice_distributions_all-Payment of cost-Centre Accounts and approvals AP_CHECKS_ALL ACA-Payment data in the table is AP_INVOICE_PAYMENTS_ALL there is valid and not payments AIPA valid (reversed) and I don't want to get reversed. But if you put the command--AND (AIPA.REVERSAL_FLAG = 'N' OR AIPA.REVERSAL_FLAG IS NULL) in clause (WHERE) I only get actual payments AIPA.REVERSAL_FLAG = ' Y ' and if the line only has reversed does not bring the record (line) in AP_INVOICE_ALL due to declared table LEFT OUTER JOIN AP_INVOICE_PAYMENTS_ALL USING (INVOICE_ID) AIPA.

Briefly the AIPA.REVERSAL_FLAG has 3 options: null, N or Y, Y, but if there are no Y does not appear the LEFT table (AP_INVOICE_ALL FIELD INVOICE_ID) due to LEFT OUTER JOIN.


How do I fix?


Below the code
Code: Select all
SELECT DISTINCT
INVOICE_ID,
TO_CHAR(AIA.creation_date,'DD/MM/YYYY')    AS Creation_date,
usr.user_name                              AS user_name,
TO_CHAR(AIA.Last_update_date,'DD/MM/YYYY') AS last_update_date,
usrup.user_name                            AS user_processor,
AIA.wfapproval_status                      AS approval_status,
DECODE (UPPER(AIDA.match_status_flag),'A','VALIDATION','N','NEEDS VALIDATION','NEVER VALIDATION')   AS inv_status,
DECODE (UPPER(AIA.invoice_type_lookup_code),'STANDARD', 'padrão', 'AWT', 'RETENCAO DE IMPOSTOS',
'INTEREST', 'JUROS', 'MIXED', 'MISTO', 'PREPAYMENT', 'PAGAMENTO ANTECIPADO',
'QUICKDEFAULT', 'DEFAULT PO', 'QUICKMATCH', 'VINCULACAO RAPIDA', 'CREDIT','AVISO DE CREDITO',
'DEBIT','AVISO DE DEBITO','EXPENSE REPORT','RELATORIO DE DESPESAS') AS invoice_type,
DECODE (UPPER(AIA.payment_status_flag),'Y','PAID','N','NOT PAID','P','PARTIAL PAID') AS Status,
GSOB.SHORT_NAME                                            AS CARRIER,
HOU.NAME                                                   AS ORG_NAME,
(PVSA.global_attribute10|| PVSA.global_attribute11|| PVSA.global_attribute12 )AS CNPJ,
PV.segment1                                        AS SUPPLIER_CODE,
PV.vendor_name                                     AS SUPPLIER_NAME,
PVSA.vendor_site_code                              AS SITE,
AIA.invoice_num                                     AS INVOICE_NUMBER,
TO_CHAR(AIA.invoice_date,'DD/MM/YYYY')              AS INVOICE_DATE,
TO_CHAR(AIA.terms_date, 'DD/MM/YYYY')               AS TERMS_DATE,
ATL.due_days                                        AS DAYS,
TO_CHAR(AIA.terms_date + ATL.due_days,'DD/MM/YYYY') AS DUE_DATE,
AIA.invoice_currency_code                           AS CURRENCY,
NVL (AIA.invoice_amount, 0)                         AS GROSS_AMOUNT,
NVL (AIA.amount_paid, 0)                            AS AMOUNT_PAID,
(NVL(AIA.invoice_amount, 0) - NVL (AIA.amount_paid, 0)) AS BALANCE,
APSA.PAYMENT_PRIORITY                               AS PRIORITY,
APSA.payment_method_lookup_code                AS PAYMENT_METHOD,
AIA.pay_group_lookup_code                      AS PAY_GROUP,
ABA.BATCH_NAME                                      AS BATCH_NAME,
NVL(AIPA.AMOUNT,0)                                  AS PAGO,
TO_CHAR(aca.check_date,'DD/MM/YYYY')                AS payment_date,
AIPA.REVERSAL_FLAG,
ACA.AMOUNT AS CHECK_AMOUNT,
ACA.CHECK_NUMBER,
ACA.BANK_ACCOUNT_NAME,
ACA.BANK_ACCOUNT_NUM, 
ACA.STATUS_LOOKUP_CODE
FROM AP_INVOICES_ALL AIA
LEFT OUTER JOIN AP_PAYMENT_SCHEDULES_ALL APSA USING (INVOICE_ID)
LEFT OUTER JOIN AP_INVOICE_PAYMENTS_ALL AIPA USING (INVOICE_ID)
LEFT OUTER JOIN ap_invoice_distributions_all AIDA USING (INVOICE_ID)
LEFT OUTER JOIN AP_CHECKS_ALL ACA USING (CHECK_ID),
FND_USER USR,
FND_USER USRUP,
GL_SETS_OF_BOOKS GSOB,
HR_ORGANIZATION_UNITS HOU,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVSA,
AP_TERMS ATE,
AP_TERMS_LINES ATL,
AP_BATCHES_ALL ABA
WHERE invoice_id in (3740338,3799833,3799838,3821310,3821311,4575435,4613524)
AND AIA.created_by      = USR.user_id
AND AIA.last_updated_by = USRUP.user_id
AND AIA.SET_OF_BOOKS_ID=GSOB.SET_OF_BOOKS_ID
AND AIA.ORG_ID=HOU.ORGANIZATION_ID
AND AIA.vendor_id       =PV.vendor_id
AND AIA.vendor_site_id  =PVSA.vendor_site_id
AND AIA.terms_id        =ATE.term_id
AND ATE.term_id         =ATL.term_id
AND AIA.batch_id        =ABA.batch_id
AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL)
AND AIA.pay_group_lookup_code IN ('NATIONAL', 'BOLETO','ZERO PAY')
ORDER BY AIA.invoice_num;
Look records that led to such questions in a forum about ERP (approval in AP).
viewtopic.php?p=29997#29997
weslley.castilho
Location: Itajaí - SC

Weslley Castilho
Skype: weslley.castilho
Msn: [email protected]

Poston Thu, 07 Apr 2011 10:04 am

As for your question, the solution is the following: instead of throwing the condition
Code: Select all
AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL)
in WHERE clause, use in the FROM clause along with ON, like so:
Code: Select all
FROM AP_INVOICES_ALL AIA
LEFT OUTER JOIN AP_PAYMENT_SCHEDULES_ALL APSA USING (INVOICE_ID)
LEFT OUTER JOIN AP_INVOICE_PAYMENTS_ALL AIPA
  ON AIA.INVOICE_ID = AIPA.INVOICE_ID
    AND (AIPA.REVERSAL_FLAG='N'
      OR AIPA.REVERSAL_FLAG IS NULL)
.





MAAAAAASSSSSS, I got a doubt in your logic:
But if you put the command--AND (AIPA.REVERSAL_FLAG = 'N' OR AIPA.REVERSAL_FLAG IS NULL) in clause (WHERE) I only get actual payments AIPA.REVERSAL_FLAG = ' Y '
this statement is pretty weird (not to mention incorrect). If you are using the conditions (AIPA.REVERSAL_FLAG = 'N' OR AIPA.REVERSAL_FLAG IS NULL), how do you handle AIPA.REVERSAL_FLAG = ' Y '?

If you want the AIPA.REVERSAL_FLAG = ' Y ', then put straight this condition ... That part got a little confused.
burga
Location: SP

Ricardo H. Tajiri

Poston Thu, 07 Apr 2011 11:53 am

Code: FROM AIA AP_INVOICES_ALL LEFT OUTER JOIN AP_PAYMENT_SCHEDULES_ALL APSA USING (INVOICE_ID) LEFT OUTER JOIN AP_INVOICE_PAYMENTS_ALL AIPA ON AIA.INVOICE_ID = AIPA.INVOICE_ID AND (AIPA.REVERSAL_FLAG = 'N' OR AIPA.REVERSAL_FLAG IS NULL)
thanks Mate, I can't use the ON due the fields being identical, changed to using (invoice_id) put the condition of the table AND (AIPA.REVERSAL_FLAG = 'N' OR AIPA.REVERSAL_FLAG IS NULL) return error: ORA-00933: SQl command in properly ended.

Below the code changed:
Code: Select all
FROM AP_INVOICES_ALL AIA
LEFT OUTER JOIN AP_PAYMENT_SCHEDULES_ALL APSA USING (INVOICE_ID)
LEFT OUTER JOIN AP_INVOICE_PAYMENTS_ALL AIPA USING (INVOICE_ID) AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL)
LEFT OUTER JOIN ap_invoice_distributions_all AIDA USING (INVOICE_ID)
LEFT OUTER JOIN AP_CHECKS_ALL ACA USING (CHECK_ID),
remembering that LEFT OUTER JOIN conditions are with 4 tables.

as the quote below, the error was on my expression:
quote: But if you put the command--AND (AIPA.REVERSAL_FLAG = 'N' OR AIPA.REVERSAL_FLAG IS NULL) in clause (WHERE) I only get actual payments AIPA.REVERSAL_FLAG = ' Y '
the correct is: AND (AIPA.REVERSAL_FLAG = 'N' OR AIPA.REVERSAL_FLAG IS NULL) does not bring ' Y ' and AIPA.REVERSAL_FLAG = ' Y ' not NULL and N.

Sorry
weslley.castilho
Location: Itajaí - SC

Weslley Castilho
Skype: weslley.castilho
Msn: [email protected]

Poston Thu, 07 Apr 2011 12:39 pm

Thanks mate, I can't use the ON because the fields are identical
what is identical in the fields? The name? If Yes, what is the relationship of this with do not use the ON clause?

I don't see a problem that causes something like the code below doesn't work.
Code: Select all
FROM AP_INVOICES_ALL AIA
LEFT OUTER JOIN AP_PAYMENT_SCHEDULES_ALL APSA ON AIA.INVOICE_ID = APSA.INVOICE_ID
LEFT OUTER JOIN AP_INVOICE_PAYMENTS_ALL AIPA ON AIPA.INVOICE_ID = AIA.INVOICE_ID AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL)
LEFT OUTER JOIN ap_invoice_distributions_all AIDA ON AIDA.INVOICE_ID = AIA.INVOICE_ID
LEFT OUTER JOIN AP_CHECKS_ALL ACA ON ACA.CHECK_ID = ...
burga
Location: SP

Ricardo H. Tajiri

Poston Thu, 07 Apr 2011 1:45 pm

My dear good afternoon,
Code: Select all
LEFT OUTER JOIN AP_INVOICE_PAYMENTS_ALL AIPA ON AIPA.INVOICE_ID = AIA.INVOICE_ID AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL)
I can't use this code because of the error:
ORA-25154: column part of USING clause cannot have qualifiter
the error is due to the field being referenced in invoice_id multiple tables and be the same field name on all MyISAM tables.

But also I see no difference between using ON or USING ().

So I believe the AND (AIPA.REVERSAL_FLAG = 'N' OR AIPA.REVERSAL_FLAG IS NULL) in charge FROM that is not viable.

But I can't say that I understand.
weslley.castilho
Location: Itajaí - SC

Weslley Castilho
Skype: weslley.castilho
Msn: [email protected]

Poston Thu, 07 Apr 2011 1:55 pm

The error ORA-25154: column part of USING clause cannot have qualifiter occurs when you use the USING clause, replace ALL JOINS by ON.

In the example I posted, with all JOINS using the ON clause, this error should not occur. And, as posted, using the ON, you can stipulate the join conditions that you need, you can't using the USING.

Another thing, with the ON, you can use the ALIASES that had the names of the tables, and so delete the problem of columns with the same name between the tables. Precisely because of this I have no problem.

The differences of using USING or ON exist, these I put is one of them.
burga
Location: SP

Ricardo H. Tajiri

Poston Thu, 07 Apr 2011 2:43 pm

My friend worked. I switched all USING by ON and renomeiei the columns via ALIAS and, below the final code.

Code: Select all
SELECT DISTINCT
AIA.INVOICE_ID AS ID_AP,
TO_CHAR(AIA.creation_date,'DD/MM/YYYY')    AS CREATION_DATE,
usr.user_name                              AS USER_NAME,
TO_CHAR(AIA.Last_update_date,'DD/MM/YYYY') AS LAST_UPDATE_DATE,
usrup.user_name                            AS USER_PROCESSOR,
AIA.wfapproval_status                      AS APPROVAL_STATUS,
DECODE (UPPER(AIDA.match_status_flag),'A','VALIDATION','N','NEEDS VALIDATION','NEVER VALIDATION')   AS INVOICE_STATUS,
DECODE (UPPER(AIA.invoice_type_lookup_code),'STANDARD', 'padrão', 'AWT', 'RETENCAO DE IMPOSTOS',
'INTEREST', 'JUROS', 'MIXED', 'MISTO', 'PREPAYMENT', 'PAGAMENTO ANTECIPADO',
'QUICKDEFAULT', 'DEFAULT PO', 'QUICKMATCH', 'VINCULACAO RAPIDA', 'CREDIT','AVISO DE CREDITO',
'DEBIT','AVISO DE DEBITO','EXPENSE REPORT','RELATORIO DE DESPESAS') AS INVOICE_TYPE,
DECODE (UPPER(AIA.payment_status_flag),'Y','PAID','N','NOT PAID','P','PARTIAL PAID') AS STATUS,
GSOB.SHORT_NAME                                            AS CARRIER,
HOU.NAME                                                   AS ORG_NAME,
(PVSA.global_attribute10|| PVSA.global_attribute11|| PVSA.global_attribute12 )AS CNPJ,
PV.segment1                                        AS SUPPLIER_CODE,
PV.vendor_name                                     AS SUPPLIER_NAME,
PVSA.vendor_site_code                              AS SITE,
AIA.invoice_num                                     AS INVOICE_NUMBER,
TO_CHAR(AIA.invoice_date,'DD/MM/YYYY')              AS INVOICE_DATE,
TO_CHAR(AIA.terms_date, 'DD/MM/YYYY')               AS TERMS_DATE,
ATL.due_days                                        AS DAYS,
TO_CHAR(AIA.terms_date + ATL.due_days,'DD/MM/YYYY') AS DUE_DATE,
AIA.invoice_currency_code                           AS CURRENCY,
NVL (AIA.invoice_amount, 0)                         AS GROSS_AMOUNT,
NVL (AIA.amount_paid, 0)                            AS AMOUNT_PAID,
(NVL(AIA.invoice_amount, 0) - NVL (AIA.amount_paid, 0)) AS BALANCE,
APSA.PAYMENT_PRIORITY                               AS PRIORITY,
APSA.payment_method_lookup_code                          AS PAYMENT_METHOD,
AIA.pay_group_lookup_code                                  AS PAY_GROUP,
ABA.BATCH_NAME                                      AS BATCH_NAME,
NVL(AIPA.AMOUNT,0)                                  AS PAGO,
nvl(TO_CHAR(aca.check_date,'DD/MM/YYYY'),'*')       AS payment_date,
ACA.AMOUNT                                          AS CHECK_AMOUNT,
AIPA.REVERSAL_FLAG,
ACA.CHECK_NUMBER
FROM AP_INVOICES_ALL AIA
LEFT OUTER JOIN ap_invoice_distributions_all AIDA ON AIA.INVOICE_ID=AIDA.INVOICE_ID
LEFT OUTER JOIN AP_PAYMENT_SCHEDULES_ALL APSA ON AIA.INVOICE_ID=APSA.INVOICE_ID
LEFT OUTER JOIN AP_INVOICE_PAYMENTS_ALL AIPA ON AIA.INVOICE_ID=AIPA.INVOICE_ID AND (AIPA.REVERSAL_FLAG='N' OR AIPA.REVERSAL_FLAG IS NULL)
LEFT OUTER JOIN AP_CHECKS_ALL ACA ON AIPA.CHECK_ID=ACA.CHECK_ID,
FND_USER USR,
FND_USER USRUP,
GL_SETS_OF_BOOKS GSOB,
HR_ORGANIZATION_UNITS HOU,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVSA,
AP_TERMS ATE,
AP_TERMS_LINES ATL,
AP_BATCHES_ALL ABA
WHERE
AIA.created_by      = USR.user_id
AND AIA.last_updated_by = USRUP.user_id
AND AIA.SET_OF_BOOKS_ID=GSOB.SET_OF_BOOKS_ID
AND AIA.ORG_ID=HOU.ORGANIZATION_ID
AND AIA.vendor_id       =PV.vendor_id
AND AIA.vendor_site_id  =PVSA.vendor_site_id
AND AIA.terms_id        =ATE.term_id
AND ATE.term_id         =ATL.term_id
AND AIA.batch_id        =ABA.batch_id
AND AIA.pay_group_lookup_code IN ('NATIONAL', 'BOLETO','ZERO PAY')
--AND UPPER(AIA.payment_status_flag)='Y'
--AND AIA.creation_date BETWEEN '01-JAN-2010' AND SYSDATE
AND AIA.invoice_id in (4519715,3740338,3799833,3799838,3821310,3821311,4575435,4613524)
ORDER BY AIA.INVOICE_ID;
hug and thanks
weslley.castilho
Location: Itajaí - SC

Weslley Castilho
Skype: weslley.castilho
Msn: [email protected]


  • See also
    Replies
    Views
    Last Post


        Return to PL/SQL

        Who is online

        Users browsing this forum: Bing [Bot], Google Adsense [Bot] and 11 guests