Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to write the case statement

Posted on 2013-01-04
17
Medium Priority
?
624 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
Industry Leaders: 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 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…

718 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