Case statement in Where clause

[satinsilhouette]

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

 

[bborissov]

What are :STARTJENO :WNDJENO etc.?
Also in one CASE statement there is ONLY ONE ELSE statement:

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

 

[satinsilhouette]

They are parameter feeds accepted by the report writing tool.

Thanks so much!
satinsilhouette

 

[gmmastros]

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

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

 

[satinsilhouette]

Seems to work bborissov - Thanks!

Thanks so much!
satinsilhouette