This discussion is archived
6 Replies Latest reply: Oct 30, 2013 7:55 PM by user10229350 RSS

select query issue

user10229350 Newbie
Currently Being Moderated
SELECT xxx.payment_id,(select payment_id from (select ipa2.payment_id
         fromiby_payments_all ipa2
         where ipa2.payment_id = xxx.payment_id
         ) )
FROM iby_payments_all xxx
WHERE payment_id=25006 -- ORA-00904: "XXX"."PAYMENT_ID": invalid identifier

 

 

 

 

SELECT xxx.payment_id,(select payment_id from (select ipa2.payment_id

         fromiby_payments_all ipa2
         where ipa2.payment_id = 25006
         ) )
FROM iby_payments_all xxx
WHERE payment_id=25006 -- Works fine
  • 1. Re: select query issue
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Use the second query.

    You don't need the extra level of sub-queries, so even if the first query did work, you shouldn't use it, because it's needlessly complicated.

     

    In general, don't count on correlating a sub-query to its grandparent, or any more distant ancestor.  Correlate a sub-query only to its immediate parent.  Exactly how to do that depends on exactly what you need to do.  If you have another example, where the alternative isn't so obvious post it.  Post CREATE TABLE and INSERT statements for a little sample data from all the tables involved, and the results you want from that data.

    Explain, using specific examples, how you get those results from that data.
    Always say which version of Oracle you're using (e.g., 11.2.0.2.0)

    .

    See the forum FAQ: https://forums.oracle.com/message/9362002

  • 2. Re: select query issue
    user10229350 Newbie
    Currently Being Moderated

    Thanks Frankkulash.

     

    I need first query to work, my actual query is lot more complex.

    I need to pass the payment_id into the sub-query.

     

    Anyone else knows how to make the first query to work?

  • 3. Re: select query issue
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

     

     

    user10229350 wrote:

     

    ...

     

    I need first query to work, my actual query is lot more complex.

    ...

    So post the query that has the problem, instead of a different query that doesn't have the problem.

     

    Don't forget the sample data, results, explantion and version.

  • 4. Re: select query issue
    davidp 2 Pro
    Currently Being Moderated

    As Frank said, you cannot correlate a sub-query to its grandparent, or any more distant ancestor.

    "I need first query to work, my actual query is lot more complex."

    You've given us impossible constraints - "Don't change my query [significantly] but make it work". without defining the implied [significantly].

    This should work, but I can't tell if it satisfies your implied constraints and you haven't given us example table structures.

    SELECT xxx.payment_id,(select payment_id from (select ipa2.payment_id

              from iby_payments_all ipa2

           

              ) ix  where ix.payment_id = xxx.payment_id )

    FROM iby_payments_all xxx

    WHERE payment_id=25006

  • 5. Re: select query issue
    user10229350 Newbie
    Currently Being Moderated

    SELECT ipsr.payment_service_request_id,

           ipsr.call_app_pay_service_req_code,

           ROWNUM,

           cba.bank_account_num,

           ifb.attribute1,

           ifb.attribute2,

           xbae.attribute1,

           ifb.attribute3,

           aisca.check_date,

           aca.check_number,

           aisca.currency_code,

           aca.amount,

           aca.currency_code,

           ieba.bank_account_num,

           ieba.bank_account_name,

           cbb.bank_name,

           cbb.bank_number,

           assa.country,

           cbb.country,

           ifb.attribute10,

           ifb.attribute11,

           xbae.attribute4,

           ass.vendor_name,

           assa.address_line1,

           assa.address_line2,

           assa.address_line3,

           assa.city,

           assa.state,

           assa.zip,

           ifb.attribute7,

           ifb.attribute8,

           ifb.attribute9,

           xbae.attribute10,

           idpa.calling_app_doc_ref_number,

           aia.invoice_date,

           aia.invoice_currency_code,

           idpa.payment_amount,

           aca.check_id,

           ft.territory_short_name,

           cbb.eft_swift_code,

           ifb.attribute12,

           ieba.attribute14,

           cbb.address_line1,

           cbb.address_line2,

           cbb.address_line3,

           cbb.city,

           cbb.state,

           cbb.zip,

           ieba.attribute15,

  • 6. Re: select query issue
    user10229350 Newbie
    Currently Being Moderated

    DECODE (

              assa.country,

              'IT', (SELECT cig_cup

                       FROM (  SELECT cc.cig || ' ' || cc.cup cig_cup,

                                      cc.check_number,

                                      cc.vendor_name,

                                      cc.vendor_site_code

                                 FROM (SELECT SUBSTR (pl.attribute1,

                                                      INSTR (pl.attribute1, 'TIG'),

                                                      13)

                                                 cig,

                                              DECODE (

                                                 INSTR (

                                                    pl.attribute1,

                                                    'TUP',

                                                    INSTR (pl.attribute1, 'TIG')),

                                                 0, NULL,

                                                 SUBSTR (

                                                    pl.attribute1,

                                                    INSTR (pl.attribute1, 'TUP'),

                                                    18))

                                                 cup,

                                              ipa2.paper_document_number

                                                 check_number,

                                              ipa2.payee_party_name vendor_name,

                                              ipa2.payee_supplier_site_name

                                                 vendor_site_code

                                         FROM ap_invoices_all inv,

                                              ap_invoice_distributions_all aid,

                                              po_distributions_all pd,

                                              po_lines_all pl,

                                              po_headers_all ph,

                                              ap_payment_schedules_all ps,

                                              iby_payments_all ipa2,

                                              iby_docs_payable_all idpa2

                                        WHERE     ps.invoice_id = inv.invoice_id

                                              AND pl.attribute1 LIKE '%TIG%'

                                              AND pd.po_header_id = ph.po_header_id

                                              AND pl.line_num = 1

                                              AND pd.po_line_id = pl.po_line_id

                                              AND aid.po_distribution_id =

                                                     pd.po_distribution_id

                                              AND inv.invoice_id = aid.invoice_id

                                              AND inv.invoice_id =

                                                     idpa2.calling_app_doc_unique_ref2

                                              AND ipa2.payment_id =

                                                     idpa2.payment_id

                                              AND ipa2.payment_id = ipa.payment_id) cc

                             GROUP BY cig || ' ' || cup,

                                      cc.check_number,

                                      cc.vendor_name,

                                      cc.vendor_site_code)),

              NULL),

           SYSDATE,

           1234,

           SYSDATE,

           1234

      FROM IBY_PAY_SERVICE_REQUESTS ipsr,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points