Case select question 3

[ksbigfoot]

I think I have to use a CASE statement to perform something equivalent of an iff.
I have a select statement that I would to convert to a case statement:
Select F1, F2 FROM Table1
If F1 = F2 Then display this code
[SQL Statements HERE]
Else display this code
[SQL Statements HERE]
End

Can someone point me in the right direction.

Thanks

 

[jjefferson]

Maybe something like this?

select somecode from (
select
case
when F1 = F2 then 'code'
else 'other code'
end as somecode
from Table1 ) as x

Not sure if this completely solves your problem, but hope it helps a little.

Jim

 

[ksbigfoot]

Howdy Jim,
I guess I am curious if a case statement go anywhere in my query.
Ex:

Select * from Table1
Case
  when f1 = f2 Then
    put sql statements here
  else
    put other sql statements here
  end
order by f1

 

[gmmastros]

ksbigfoot,

I apologize for not quite understanding your question. The case statement can appear in multiple places within a sql query. Most of the time, you see them in the select clause, like so...

Select F1, 
       F2,
       Case When F1 = F2
            Then 'Fields are equal'
            Else 'Fields are different'
            End As AreTheyTheSame
From   Table

If this doesn't answer your question, perhaps you could post the original query (with the IIF), or some sample data with expected results.

-George

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

 

[SQLSister]

Case can also be inthe where clause if that is what you are asking.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[ksbigfoot]

Thanks for all your help.
I was trying to do the following which I realize I can't do

Select * from Table1
Case
  when f1 = f2 Then
    where Table1.F4 = 3
     and  Table1.F5 = 2
  else
    where Table1.F6 = 3
     and  Table1.F7 = 2
  end
order by f1
case

Thanks for all your help, I am understanding the CASE statment a lot more know.

 

[RAS56]

Would this work:

Select * from Table1
 where 
  case when f1=f2 
   then table1.f4=3 and table1.f5=2 
   else table1.f6=3 and table1.f7=2 end
 order by f1

I didn't test it but I think I've done things like this before.

 

[gmmastros]

which I realize I can't do

<clears throat>

Whaddya mean? Maybe it can't be done the way want, but there are ways. Here's one...

Select *
From   Table1
Where  Table1.F4     = Case When F1 = F2  Then 3 Else Table1.F4 End
       And Table1.F5 = Case When F1 = F2  Then 2 Else Table1.F5 End
       And Table1.F6 = Case When F1 <> F2 Then 3 Else Table1.F6 End
       And Table1.F7 = Case When F1 <> F2 Then 2 Else Table1.F7 End

Or even....

Select * 
From   Table1 
Where  F1 = F2
       And Table1.F4 = 3
       And Table1.F5 = 2

[!]Union All[/!]

Select * 
From   Table1 
Where  F1 [!]<>[/!] F2
       And Table1.F6 = 3
       And Table1.F7 = 2

-George

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

 

[ksbigfoot]

Howdy RAS56,

I tried your way and it didn't work. Error message said there was an error on line 5 with the = sign (CountyPrimary = 1510).

select * from property
Where
case when CountyPrimary > 1500
then 
CountyPrimary = 1510
Else 
CountyPrimary = 1490
end
order by CompanyID

 

[RAS56]

Oops - I think I was mistaken. I went back and tested this for a bit and couldn't make the dynamic field selection work.

Something like this would work if your data structures would fit somehow:

Select * from Table1
 where f4 = case when f1=f2 then 3 else 2 end
 order by f1

but I don't think my previous example will ever work.

-or-

I think you could do a union and get the results you want. There is probably a more elegant way to do this that some of these folks that are more savvy than me could tell you. I might try something like this:

select * from Table1 where f1=f2 and f4=3 and f5=2
union
select * from Table1 where f1<>f2 and f6=3 and f7=2 order by f1

 

[gmmastros]

select * 
from   property
Where  CountyPrimary = Case When CountyPrimary > 1500
                            Then 1510
                            Else 1490
                            End
order by CompanyID

-George

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

 

[ksbigfoot]

Howdy George,
I just did a simple example to show that it didn't work.
This is what I want to work:

select * 
from property 
Where CountyPrimary = Case When CountyPrimary > 1500
Then 1510 AND PropertyTypeID = 3
Else 1490 AND PropertyTypeID = 2
End
order by CompanyID

 

[vongrunt]

Maybe it's better to explain logic behind in plain English... this query is hard to understand.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[gmmastros]

Let's play 'skin the cat'. There's several ways, you know.

select * 
from   property 
Where  CountyPrimary = Case When CountyPrimary > 1500 Then 1510 Else CountyPrimary End
       AND PropertyTypeID = Case When CountyPrimary > 1500 Then 3 Else PropertyTypeId End
       And CountyPrimary = Case When CountyPrimary <= 1500 Then 1490 Else CountyPrimary End
       And PropertyTypeId = Case When CountyPrimary <= 1500 Then 2 Else PropertyTypeId End
order by CompanyID

Select *
From   Property
Where  CountyPrimary > 1500
       And CountyPrimary = 1510
       And PropertyTypeId = 3

Union All

Select *
From   Property
Where  CountyPrimary <= 1500
       And CountyPrimary = 1490
       And PropertyTypeId = 2
Order By CompanyId

Select *
From   Property
Where  (CountyPrimary = 1510 And PropertyTypeId = 3)
       Or
       (CountyPrimary = 1490 And PropertyTypeID = 2)

-George

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

 

[ksbigfoot]

Thanks everyone for your posts.
It has been very helpful.
Sorry for not being very clear.

Vongrunt, I will do my best on the wording.
In my where statement, I have 3 sections of query statement all separated by an OR

Where A
OR B
OR C

A part has multiple AND statements
B part has multiple AND statements
C part has multiple AND statements

I only want to run the Statements in A if F1 = F2
Otherwise I want to run the Statements in B & C

I guess what I was originally asking was and I realize that it can't be done is:
IF F1 = F2 Then
Display A query statements
Eg.) WHERE f1 = 3 AND F2 > 4
Else
Display B & C query statements
Eg.) WHERE f1 = 2 AND f2 > 5
End
I think I will follow George's example to make what I am doing work.

So, thanks everyone for setting me straight.
ksbigfoot