Solved

SQL query build where clause using case statement

Posted on 2014-02-21
14
3,896 Views
Last Modified: 2014-02-25
I have a query and I would like to build my where clause using a case statement but must be missing something because my syntax isn't correct.  I want to build my where clause based on a parameter  (radio button value) that is selected by the user.  My statement is below. Thanks for your help.

SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10
FROM TABLE1
WHERE
CASE :P1_SEARCH
WHEN 'OPTION1'
THEN 'MYID = :P1_CHOICE1'
WHEN 'OPTION2'
THEN 'MY2NDFIELD = :P1_CHOICE2'
WHEN 'OPTION3'
THEN 'MY3RDFIELD = :P1_CHOICE3'
WHEN 'OPTION4'
THEN 'MYFOURTHFIELD = :P1_CHOICE4'
WHEN 'OPTION5'
THEN 'MYFIFTHFIELD = :P1_CHOICE5'
WHEN 'OPTION6'
THEN 'MYSIXTHFIELD = :P1_CHOICE6'
WHEN 'OPTION7'
THEN '(MYDATE >= TO_DATE(:P1_DATE, 'MM/DD/YYYY')'
END CASE
order by MYDATE)
0
Comment
Question by:newtoperlpgm
[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
  • 6
  • 5
  • 3
14 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39877942
You cannot dynamically build a where clause from a case statement.


Build one long statement that is only true when necessary.

Something like this (untested, just typed in it might have a syntax issue or two):
SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10 
FROM TABLE1 
WHERE
( :P1_SEARCH = 'OPTION1' and MYID = :P1_CHOICE1) or
( :P1_SEARCH = 'OPTION2' and MY2NDFIELD = :P1_CHOICE2) or
( :P1_SEARCH = 'OPTION3' and MY3RDFIELD = :P1_CHOICE3) or
( :P1_SEARCH = 'OPTION4' and MYFOURTHFIELD = :P1_CHOICE4 ) or
( :P1_SEARCH = 'OPTION5' and MYFIFTHFIELD = :P1_CHOICE5 ) or
( :P1_SEARCH = 'OPTION6' and MYSIXTHFIELD = :P1_CHOICE6) or
( :P1_SEARCH = 'OPTION7' and (MYDATE >= TO_DATE(:P1_DATE, 'MM/DD/YYYY')
order by MYDATE

Open in new window

0
 

Author Comment

by:newtoperlpgm
ID: 39878098
Is it possible that I could build my select statement based on the :P1_SEARCH value? The only reason I would want to change my current query would be to speed up the query in the database.  My query is essentially the same as your proposed solution above, without the  :P1_SEARCH = 'OPTION1' , :P1_SEARCH = 'OPTION2' etc.  part.
Thank you.
0
 
LVL 20

Expert Comment

by:flow01
ID: 39878158
You should change
END CASE
to
END
when using CASE in an ORACLE SQL-STATEMENT

However (i can't help it)  when using a CASE in pl/sql  it should indeed be END CASE;

You can use a double CASE if all fields are of the same type

where CASE :P1_SEARCH
      when 'OPTION1' THEN MYID
      when 'OPTION2' THEN MY2NDFIELD
      when 'OPTION3' THEN MY3RDFIELD
      when 'OPTION4' THEN MYFOURTHFIELD
      when 'OPTION5' THEN MYFIFTHFIELD
      when 'OPTION6' THEN MYSIXTHFIELD
      when 'OPTION7' THEN TO_CHAR(MYDATE, 'MM/DD/YYYY')   -- convert to varchar MYSIXTHFIELD
      END
=
      CASE :P1_SEARCH
      when 'OPTION1' THEN :P1_CHOICE1
      when 'OPTION2' THEN :P1_CHOICE2
      when 'OPTION3' THEN :P1_CHOICE3
      when 'OPTION4' THEN :P1_CHOICE4
      when 'OPTION5' THEN :P1_CHOICE5
      when 'OPTION6' THEN :P1_CHOICE6
      when 'OPTION7' THEN :P1_CHOICE7  
      END

But I would suggest to follow slightvw suggestion.
It's more clear and avoids type conversions that could make Oracle to avoid the use of available indexes
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 20

Expert Comment

by:flow01
ID: 39878169
And if you are using this query in ORACLE FORMS check your manual how you can change the WHERE CLAUSE of the block at runtime (in the later versions you can also change it for 1 execution only).
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39878224
>>Is it possible that I could build my select statement based on the :P1_SEARCH value?

It may be possible but I do not understand what you are asking.

Using regular SQL, you cannot dynamically alter the where clause the way you had it.

You can change the query however you want as long as you don't try to make it dynamic.

Now, with code, you could dynamically build a string then execute that string.  Say Forms like mentioned above or in PL/SQL, .Net, Java, ???
0
 

Author Comment

by:newtoperlpgm
ID: 39884215
I want to build my query in Oracle APEX in a report.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39884283
Never used APEX so I'll defer to oracle.com:
http://www.oracle.com/technetwork/developer-tools/apex/dynamic-report-092026.html#DYN_REPORT

That said:  I'm still a little confused about what you actually want to do.

Are you saying that the extra column checks that I posted in http:#a39877942 cause a performance problem or incorrect data?
0
 

Author Comment

by:newtoperlpgm
ID: 39884293
No, they don't cause a problem, but I was trying to speed up performance.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39884336
>>No, they don't cause a problem, but I was trying to speed up performance.

Isn't performance a problem?

I have to assume hat you tested the select I posted with a select that limits the where clause to prove increased performance?

If so, can you provide the two select statements and their performance plans?
0
 

Author Comment

by:newtoperlpgm
ID: 39885882
My current query, (and the reason I posted my original question) that I am trying to streamline in order to speed it up is
SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10
FROM TABLE1
WHERE
MYID = :P1_CHOICE1 or
MY2NDFIELD = :P1_CHOICE2 or
MY3RDFIELD = :P1_CHOICE3 or
MYFOURTHFIELD = :P1_CHOICE4 or
MYFIFTHFIELD = :P1_CHOICE5or
MYSIXTHFIELD = :P1_CHOICE6 or
MYDATE >= TO_DATE(:P1_DATE, 'MM/DD/YYYY')
order by MYDATE

I tried to use the solution you posted and couldn't get it to yield my desired results, i.e., I got No Data Found, so I am currently trying to figure out how to make it work in my APEX application.  However, since you said I could not dynamically alter my where clause, I asked the question about the possibility of building my select statement based on the :P1_SEARCH value?  I know your solution works because I tested it in Oracle, now I am just trying to get it to work from my APEX web page.  Hope this makes sense.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39885892
I'm confused:
I tried to use the solution you posted and couldn't get it to yield my desired results
and
I know your solution works because I tested it in Oracle

How can it work and not work?
0
 
LVL 20

Expert Comment

by:flow01
ID: 39886570
When used in Apex did you verify the value of  :P1_SEARCH ?
0
 

Author Comment

by:newtoperlpgm
ID: 39887201
slightvw
I'm confused:
I tried to use the solution you posted and couldn't get it to yield my desired results

and
I know your solution works because I tested it in Oracle


How can it work and not work?   It worked in Oracle SQL but when I ran it in my APEX web page it didn't yield any results.
0
 

Author Comment

by:newtoperlpgm
ID: 39887421
I was now able to get it to work within my APEX web page, I had an incorrectly named parameter, that is why it didn't yield any results.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 …
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

739 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