Solved

SQL query build where clause using case statement

Posted on 2014-02-21
14
3,843 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
  • 6
  • 5
  • 3
14 Comments
 
LVL 76

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

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 76

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 76

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 76

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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 take different types of Oracle backups using RMAN.

839 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