Solved

how to write the case statement

Posted on 2013-01-04
17
619 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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.

808 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