Solved

SQL query build where clause using case statement

Posted on 2014-02-21
14
3,783 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
 
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
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.

 

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

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

708 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

14 Experts available now in Live!

Get 1:1 Help Now