Case in Where Clause.

[ptheriault]

I would rather write this statement without using dynamic sql.

Shouldn't this case statement in the where clause work.

[COLOR=blue]declare[/color] @sql  [COLOR=blue]varchar[/color](200)
        ,@where [COLOR=blue]varchar[/color](200)
        ,@val [COLOR=blue]char[/color](3)

[COLOR=blue]select[/color] @val = [COLOR=red]'med'[/color]

[COLOR=blue]select[/color] @sql = [COLOR=red]'select top 1 * from policy_insuredClaims_IC'[/color]

[COLOR=blue]if[/color] @val = [COLOR=red]'med'[/color]
[COLOR=blue]BEGIN[/color]
[COLOR=blue]SELECT[/color] @where = [COLOR=red]' WHERE IC_claimType = 5'[/color]
[COLOR=blue]END[/color]
[COLOR=blue]ELSE[/color]
[COLOR=blue]BEGIN[/color]
[COLOR=blue]SELECT[/color] @where = [COLOR=red]' WHERE IC_claimType <> 5'[/color]
[COLOR=blue]END[/color]
[COLOR=blue]exec[/color] (@sql+@where)


[COLOR=blue]select[/color] top 1 * [COLOR=blue]from[/color] policy_insuredClaims_IC
[COLOR=blue]WHERE[/color] IC_ClaimType = ([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] @val=[COLOR=red]'med'[/color]
                      [COLOR=blue]THEN[/color]  5
                      [COLOR=blue]ELSE[/color] <> 5 
                      [COLOR=blue]END[/color])

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[AlexCuse]

Paul -

You can't do this in the CASE.

ELSE <> 5

Do you mean this:

ELSE '<> 5'

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ptheriault]

Thanks Alex, That works.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[AlexCuse]

I think this is what you were after:

where (IC_Claim_Type = 5 and @val = 'med')
or (IC_Claim_Type <> 5 and @val <> 'med')

Ignorance of certain subjects is a great part of wisdom

 

[SQLSister]

if this isn;t five your where clause would resolve to:
IC_ClaimType = <> 5



Questions about posting. See faq183-874

 

[AlexCuse]

Hehe, yes it works. But does it do what you want?

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

What is the data type for IC_ClaimType ?



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

Crud!!!

It is currently written as dynamic SQL as above.

So if @val is not med I need all the claims not in 5.



- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

it is an int

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

This is what I need but obvouly this syntax doesn't work.

[COLOR=blue]select[/color] top 1 * [COLOR=blue]from[/color] policy_insuredClaims_IC
[COLOR=blue]WHERE[/color] [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] @val=[COLOR=red]'med'[/color]
           [COLOR=blue]THEN[/color] IC_ClaimType = 5
           [COLOR=blue]ELSE[/color] IC_ClaimType <> 5 
           [COLOR=blue]END[/color]

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

Try Alex's suggestion in post dated: 18 Apr 07 16:47

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

Paul - I think my post of 16:47 will do the trick for you.

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

Yeah. What he said.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

I overlooked that one.

Thanks Guys!


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[AlexCuse]

Nah, what George said!

[COLOR=blue]Select[/color] Post [COLOR=blue]from[/color] TekTips.SQLProg [COLOR=blue]where[/color] DateCol Like [COLOR=red]'%16:47%'[/color] and Handle = [COLOR=red]'AlexCuse'[/color]
<>

[COLOR=blue]Select[/color] Post [COLOR=blue]from[/color] TekTips.SQLProg [COLOR=blue]where[/color] DateCol = [COLOR=red]'2007-04-18 16:47:00'[/color] and Handle = [COLOR=red]'AlexCuse'[/color]

The former might cause a <. in your brain ;-)

Ignorance of certain subjects is a great part of wisdom

 

[ptheriault]

Thanks Guys, It is working just they way I wanted. Now I can have the developer get rid of the dynamic sql.

Time to go home!

- Paul

- If at first you don't succeed, find out if the loser gets anything.