Solved

how to write the case statement

Posted on 2013-01-04
17
623 Views
Last Modified: 2013-01-08
Hi
I have this Query
SELECT poh.ship_to_location_id,
  poh.po_release_id,
  por.release_num,
         poh.po_type poh_po_type,
         por.release_type poh_release_type,
         poh.po_num
         || DECODE (poh.po_type, 'RELEASE', '-' || por.release_num, NULL)
            poh_po_num,
         poh.po_num security_poh_po_num,
         por.release_num security_por_po_num,
         poh.revision_num poh_revision_num,
         poh.vendor_name poh_vendor_name,
         poh.vendor_address_line1 poh_vendor_address_line1,
         poh.vendor_address_line2 poh_vendor_address_line2,
         poh.vendor_address_line3 poh_vendor_address_line3,
         DECODE (
            poh.vendor_city,
            NULL, poh.vendor_state || ' ' || poh.vendor_postal_code,
               poh.vendor_city
            || ', '
            || poh.vendor_state
            || ' '
            || poh.vendor_postal_code)
            poh_vendor_adr_info,
         (poh.vendor_postal_code || '  ' || poh.vendor_city)
            poh_vendor_adr_info1                        --France customization
                                ,
         poh.vendor_country poh_vendor_country,
         poh.customer_num poh_customer,
         poh.vendor_num poh_vendor_num,
         poh.creation_date poh_creation_date,
         poh.revised_date poh_revised_date,
            SUBSTR (poh.document_buyer_first_name, 1, 1)
         || ' '
         || poh.document_buyer_last_name
            poh_buyer,
            SUBSTR (poh.archive_buyer_first_name, 1, 1)
         || ' '
         || poh.archive_buyer_last_name
            poh_archive_buyer,
         poh.document_buyer_agent_id poh_agent_id,
         poh.archive_buyer_agent_id poh_archive_agent_id -- custom column added
                                                        ,
         poh.payment_terms poh_payment_terms,
         poh.ship_via poh_ship_via,
         poh.fob poh_fob,
         poh.freight_terms poh_freight_terms,
         poh.vendor_contact_first_name || ' ' || poh.vendor_contact_last_name
            poh_vendor_contact_name,
         poh.vendor_phone poh_vendor_phone,
         poh.vendor_contact_phone poh_vendor_contact_phone,
         poh.note_to_vendor poh_note_to_vendor,
         poh.printed_date poh_printed_date,
         poh.amount_agreed poh_amount_agreed,
         poh.cancel_flag poh_cancel_flag,
         poh.confirming_order_flag poh_confirming_order_flag,
         poh.acceptance_required_flag poh_acceptance_req_flag,
         poh.acceptance_due_date poh_acceptance_due_date,
         poh.currency_code poh_currency_code,
         poh.currency_code C_CURRENCY,
         poh.currency_name poh_currency_name,
         poh.currency_conversion_rate poh_currency_conversion_rate,
         poh.bill_to_location_id poh_bill_to_location,
         poh.ship_to_location_id poh_ship_to_location,
         poh.po_header_id poh_po_header_id,
         poh.po_release_id poh_po_release_id,
         poh.po_type poh_po_type,
         poh.approved_flag poh_approved_flag,
         poh.print_count poh_print_count,
         poh.effective_date poh_effective_date,
         poh.expiration_date poh_expiration_date,
         NVL (poh.po_release_id, -1) poh_join_release_id,
         poh.vendor_site_id poh_vendor_site_id,
         poh.vendor_id poh_vendor_id
    FROM po_headers_print poh, po_releases por
   WHERE     poh.po_release_id = por.po_release_id(+)
        and poh.ship_to_location_id=nvl(30342, poh.ship_to_location_id)
               AND exists ( select 1 from tpco_po_lines_print plp--, po_releases por1
                         where plp.po_header_id=poh.po_header_id
                     and plp.po_release_id=poh.po_release_id
                   and poh.po_type = 'RELEASE'
         and plp.ship_to_location_id=poh.ship_to_location_id
                  )
                      AND    (  nvl(por.release_num,-1)
                  BETWEEN nvl(27,nvl(por.release_num,-1))
                  AND     nvl(27,nvl(por.release_num,-1))
         OR    poh.po_type != 'RELEASE')
         AND NVL (poh.consigned_consumption_flag, 'N') <> 'Y'
        AND NVL (por.consigned_consumption_flag, 'N') <> 'Y'
         AND poh.po_header_id = 182789
ORDER BY 1,
         2,
         3,
         4

Now
this AND exists ( select 1 from tpco_po_lines_print plp--, po_releases por1
                         where plp.po_header_id=poh.po_header_id
                     and plp.po_release_id=poh.po_release_id
                   and poh.po_type = 'RELEASE'
         and plp.ship_to_location_id=poh.ship_to_location_id
                  )
 condition inside the query should only get evaluated when
the poh.type_lookup_code = 'STANDARD'
else skip the exists condition
how to write this in the query
0
Comment
Question by:pardeshirahul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 

Author Comment

by:pardeshirahul
ID: 38745538
sorry i mean

condition inside the query should only get evaluated when
the poh.type_lookup_code <> 'STANDARD'
else skip the exists condition
how to write this in the query
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38745646
For that you need a dynamic query, but you don't really need it. just put the "<> 'STANDARD'" condition inside the EXISTS:


SELECT poh.ship_to_location_id,
  poh.po_release_id,
  por.release_num,
         poh.po_type poh_po_type,
         por.release_type poh_release_type,
         poh.po_num
         || DECODE (poh.po_type, 'RELEASE', '-' || por.release_num, NULL)
            poh_po_num,
         poh.po_num security_poh_po_num,
         por.release_num security_por_po_num,
         poh.revision_num poh_revision_num,
         poh.vendor_name poh_vendor_name,
         poh.vendor_address_line1 poh_vendor_address_line1,
         poh.vendor_address_line2 poh_vendor_address_line2,
         poh.vendor_address_line3 poh_vendor_address_line3,
         DECODE (
            poh.vendor_city,
            NULL, poh.vendor_state || ' ' || poh.vendor_postal_code,
               poh.vendor_city
            || ', '
            || poh.vendor_state
            || ' '
            || poh.vendor_postal_code)
            poh_vendor_adr_info,
         (poh.vendor_postal_code || '  ' || poh.vendor_city)
            poh_vendor_adr_info1                        --France customization
                                ,
         poh.vendor_country poh_vendor_country,
         poh.customer_num poh_customer,
         poh.vendor_num poh_vendor_num,
         poh.creation_date poh_creation_date,
         poh.revised_date poh_revised_date,
            SUBSTR (poh.document_buyer_first_name, 1, 1)
         || ' '
         || poh.document_buyer_last_name
            poh_buyer,
            SUBSTR (poh.archive_buyer_first_name, 1, 1)
         || ' '
         || poh.archive_buyer_last_name
            poh_archive_buyer,
         poh.document_buyer_agent_id poh_agent_id,
         poh.archive_buyer_agent_id poh_archive_agent_id -- custom column added
                                                        ,
         poh.payment_terms poh_payment_terms,
         poh.ship_via poh_ship_via,
         poh.fob poh_fob,
         poh.freight_terms poh_freight_terms,
         poh.vendor_contact_first_name || ' ' || poh.vendor_contact_last_name
            poh_vendor_contact_name,
         poh.vendor_phone poh_vendor_phone,
         poh.vendor_contact_phone poh_vendor_contact_phone,
         poh.note_to_vendor poh_note_to_vendor,
         poh.printed_date poh_printed_date,
         poh.amount_agreed poh_amount_agreed,
         poh.cancel_flag poh_cancel_flag,
         poh.confirming_order_flag poh_confirming_order_flag,
         poh.acceptance_required_flag poh_acceptance_req_flag,
         poh.acceptance_due_date poh_acceptance_due_date,
         poh.currency_code poh_currency_code,
         poh.currency_code C_CURRENCY,
         poh.currency_name poh_currency_name,
         poh.currency_conversion_rate poh_currency_conversion_rate,
         poh.bill_to_location_id poh_bill_to_location,
         poh.ship_to_location_id poh_ship_to_location,
         poh.po_header_id poh_po_header_id,
         poh.po_release_id poh_po_release_id,
         poh.po_type poh_po_type,
         poh.approved_flag poh_approved_flag,
         poh.print_count poh_print_count,
         poh.effective_date poh_effective_date,
         poh.expiration_date poh_expiration_date,
         NVL (poh.po_release_id, -1) poh_join_release_id,
         poh.vendor_site_id poh_vendor_site_id,
         poh.vendor_id poh_vendor_id
    FROM po_headers_print poh, po_releases por
   WHERE     poh.po_release_id = por.po_release_id(+)
        and poh.ship_to_location_id=nvl(30342, poh.ship_to_location_id)
               AND exists ( select 1 from tpco_po_lines_print plp--, po_releases por1
                         where plp.po_header_id=poh.po_header_id
                     and plp.po_release_id=poh.po_release_id
                   and poh.po_type = 'RELEASE'
         and plp.ship_to_location_id=poh.ship_to_location_id
                  )
                      AND    (  nvl(por.release_num,-1)
                  BETWEEN nvl(27,nvl(por.release_num,-1))
                  AND     nvl(27,nvl(por.release_num,-1))
         OR    poh.po_type != 'RELEASE')
         AND NVL (poh.consigned_consumption_flag, 'N') <> 'Y'
        AND NVL (por.consigned_consumption_flag, 'N') <> 'Y'
         AND poh.po_header_id = 182789

         AND exists ( select 1 from tpco_po_lines_print plp--, po_releases por1
                         where plp.po_header_id=poh.po_header_id
                     and plp.po_release_id=poh.po_release_id
                   and poh.po_type = 'RELEASE'
         and plp.ship_to_location_id=poh.ship_to_location_id

         and poh.type_lookup_code <> 'STANDARD'

                     )


ORDER BY 1,
         2,
         3,
         4
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38745684
In the EXISTS clause the outer query gets evaluated first, so for each row fetched, Oracle checks the sub-query and if it has a condition that evaluates to FALSE, then it skips the EXISTS clause.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:pardeshirahul
ID: 38747320
what i mean i dont want and poh.type_lookup_code <> 'STANDARD'
at the end

it is only for that and exists condition

the and exists condition should only get evaluated when the and poh.type_lookup_code <> 'STANDARD'
or else the and exists condition sould not get evaluated
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38748680
It's not at the end. It's inside the EXISTS condition:


         AND exists
          (   -- <<--------- That's an opening parenthesis

           select 1 from tpco_po_lines_print plp--, po_releases por1
                         where plp.po_header_id=poh.po_header_id
                     and plp.po_release_id=poh.po_release_id
                   and poh.po_type = 'RELEASE'
         and plp.ship_to_location_id=poh.ship_to_location_id

         and poh.type_lookup_code <> 'STANDARD'  -- Inside the EXISTS condition

          ) -- <<--------- That's an closing parenthesis
0
 

Author Comment

by:pardeshirahul
ID: 38751197
some how this is not working
AND exists
          (   -- <<--------- That's an opening parenthesis

           select 1 from tpco_po_lines_print plp--, po_releases por1
                         where plp.po_header_id=poh.po_header_id
                     and plp.po_release_id=poh.po_release_id
                   and poh.po_type = 'RELEASE'
         and plp.ship_to_location_id=poh.ship_to_location_id

         and poh.type_lookup_code <> 'STANDARD'  -- Inside the EXISTS condition

when i comment the entire and exists part then i am getting the data, is there a another way
0
 

Author Comment

by:pardeshirahul
ID: 38751265
can we write some decode or case statement so it will not come inside the and exists condition when the Purchase order type is 'STANDARD'

i thing why the filter for
and poh.type_lookup_code <> 'STANDARD'
is not working because for 'STANDARD'
there are no releases
0
 

Author Comment

by:pardeshirahul
ID: 38751669
any ideas
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38752424
Can you try this:

AND exists
          (  

           select 1 from tpco_po_lines_print plp--, po_releases por1
                         where
                            (        plp.po_header_id=poh.po_header_id
                              and plp.po_release_id=poh.po_release_id
                              and poh.po_type = 'RELEASE'
                              and plp.ship_to_location_id=poh.ship_to_location_id
                              and decode(poh.type_lookup_code, 'STANDARD',0,1) = 1
                             )
                          or decode(poh.type_lookup_code, 'STANDARD',1,0) = 1

          )
0
 

Author Comment

by:pardeshirahul
ID: 38754896
the last case statement is working fine

only the report is taking 1 hour to run

previously it was taking 1 minute to run for releases

is there a way we can tune this
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38755121
It looks like you're getting a full table scan. Try limiting the condition with rownum pseudo-column:

AND exists
          (  

           select 1 from tpco_po_lines_print plp--, po_releases por1
                         where
                            (        plp.po_header_id=poh.po_header_id
                              and plp.po_release_id=poh.po_release_id
                              and poh.po_type = 'RELEASE'
                              and plp.ship_to_location_id=poh.ship_to_location_id
                              and decode(poh.type_lookup_code, 'STANDARD',0,1) = 1
                             )
                             or
                            (
                                     decode(poh.type_lookup_code, 'STANDARD',1,0) = 1
                             and  rownum = 1
                             )

          )
0
 

Author Comment

by:pardeshirahul
ID: 38755675
no this rownum=1 is not helping it is still taking the same time
0
 

Author Comment

by:pardeshirahul
ID: 38755676
do you want the tkprof file
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38755791
Can you run an explain plan?
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 500 total points
ID: 38755823
Let's also try this:

AND exists
          (  

           select NULL from tpco_po_lines_print plp--, po_releases por1
                         where  plp.po_header_id=poh.po_header_id
                              and plp.po_release_id=poh.po_release_id
                              and poh.po_type = 'RELEASE'
                              and plp.ship_to_location_id=poh.ship_to_location_id
                              and decode(poh.type_lookup_code, 'STANDARD',0,1) = 1
           union all
           select NULL from dual where decode(poh.type_lookup_code, 'STANDARD',1,0) = 1
          )
0
 

Author Comment

by:pardeshirahul
ID: 38756065
the last select worked fine
the report ran in 2 seconds
what was the change
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38756413
Pretty much you have two SELECT statments inside the EXISTS condition, the first SELECT goes agains table tpco_po_lines_print, but only if poh.type_lookup_code <>  'STANDARD'. The second SELECT goes agains table DUAL in case poh.type_lookup_code =  'STANDARD' we return TRUE to the EXISTS condition, but don't have to do much work when scanning DUAL versus fully scanning ttable tpco_po_lines_print.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question