Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Case statement in Where clause

Status
Not open for further replies.

satinsilhouette

Instructor
Feb 21, 2006
625
I found something on a SQL board about using a case statement in the where clause and haven't gotten it to work yet.

Need: a data field that can have one choice, a range of choices or no choices.

Control group is the field and it looks like:

Control Group Start ______ Control Group End ______

The case statement I put together says has missing key word:

SELECT CONTROL_GROUP, JE_TYPE
FROM GLTRANS
WHERE (CASE CONTROL_GROUP WHEN :STARTJENO IS NULL AND :ENDJENO IS NULL THEN 1 ELSE 0 WHEN :STARTJENO IS NOT NULL AND
:ENDJENO IS NULL THEN 1 ELSE 0 WHEN BETWEEN :STARTJENO AND :ENDJENO THEN 1 ELSE 0 END = 1)

Any ideas what I am doing wrong here? This is my first attempt at using a case statement in the where clause.

Thanks so much!
satinsilhouette
 
What are :STARTJENO :WNDJENO etc.?
Also in one CASE statement there is ONLY ONE ELSE statement:
Code:
....
WHERE  CASE WHEN [:STARTJENO] IS NULL AND [:ENDJENO] IS NULL 
                            THEN 1 
            WHEN [:STARTJENO] IS NOT NULL AND [:ENDJENO] IS NULL
                            THEN 1 
            WHEN CONTROL_GROUP BETWEEN [:STARTJENO] AND [:ENDJENO]
                            THEN 1 
            ELSE 0 END = 1)
Check this CASE and tell me if it does the job you want :)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
They are parameter feeds accepted by the report writing tool.

Thanks so much!
satinsilhouette
 
I don't think you need to use a case/when statement here. I think an OR condition would suffice.

Code:
WHERE  [blue][b]([/b][/blue]
         [!]([/!][:STARTJENO] IS NULL AND [:ENDJENO] IS NULL[!])[/!]
       Or
         [!]([/!][:STARTJENO] IS NOT NULL AND [:ENDJENO] IS NULL[!])[/!]
       Or 
         [!]([/!]CONTROL_GROUP BETWEEN [:STARTJENO] AND [:ENDJENO][!])[/!]
       [blue][b])[/b][/blue]

Note: When mixing AND with OR in a where clause, the parenthesis are VERY important. Without the parenthesis, you will probably get the wrong results.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Seems to work bborissov - Thanks!

Thanks so much!
satinsilhouette
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top