Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how to write the case statement

Posted on 2013-01-04
17
Medium Priority
?
625 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 2000 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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

886 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