Solved

how to write the case statement

Posted on 2013-01-04
17
614 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:pardeshirahul
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
any ideas
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.

 
LVL 23

Expert Comment

by:paquicuba
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
no this rownum=1 is not helping it is still taking the same time
0
 

Author Comment

by:pardeshirahul
Comment Utility
do you want the tkprof file
0
 
LVL 23

Expert Comment

by:paquicuba
Comment Utility
Can you run an explain plan?
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 500 total points
Comment Utility
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
Comment Utility
the last select worked fine
the report ran in 2 seconds
what was the change
0
 
LVL 23

Expert Comment

by:paquicuba
Comment Utility
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

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.

Join & Write a Comment

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now