• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4340
  • Last Modified:

SQL query build where clause using case statement

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
newtoperlpgm
Asked:
newtoperlpgm
  • 6
  • 5
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
newtoperlpgmAuthor Commented:
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
 
flow01Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
flow01Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
newtoperlpgmAuthor Commented:
I want to build my query in Oracle APEX in a report.
0
 
slightwv (䄆 Netminder) Commented:
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
 
newtoperlpgmAuthor Commented:
No, they don't cause a problem, but I was trying to speed up performance.
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
newtoperlpgmAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
flow01Commented:
When used in Apex did you verify the value of  :P1_SEARCH ?
0
 
newtoperlpgmAuthor Commented:
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
 
newtoperlpgmAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now