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

IF Statement in a View or Stored Procedure

Hi all,

I have a table that has Three Bit fields (True/False)  One of these three fields is always checked.

I want my view to have a field that evaluates these three fields and tells me the which area I am in.

Here is the psuedocode that I want translated into sql.

If Area1 is True, then "Washington"
Else
    If Area2 is True, "Oregon"
    Else
        "Idaho"
End If
End if

I want that to process as field WhatArea

Any help is appreciated.

Chris

P.S.  I know this can be done in Access queries as I have already made it there.  I am trying to port that query into a SQL Server 2000 View.  The statement in Access 2000 was:
Iif(Area1=-1,"Washington", Iif(Area2=-1,"Oregon","Idaho")) as Whatarea
0
Krys_Wilson
Asked:
Krys_Wilson
  • 3
  • 3
  • 3
  • +1
1 Solution
 
adwisemanCommented:
--Try This

CASE WHEN Area1=-1 THEN "Washington" WHEN Area2=-1 THEN "Oregon" ELSE "Idaho" END as Whatarea
FROM tableA




--adw
0
 
Scott PletcherSenior DBACommented:
I suggest using a computed column.  This is a virtual column only, it's not physically stored, it's just defined to SQL so that it can be referenced as if it were a column.  For example:

ALTER TABLE yourTable
ADD WhatArea AS CASE WHEN Area1 = 1 THEN 'Washington' WHEN Area2 = 1 THEN 'Oregon' ELSE 'Idaho' END
0
 
adwisemanCommented:
SQL does not have it IIF statement, you use the Case statement for this.  See Microsofts help on Case statements.  There are a few different ways to write them, this is the one you need.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
SharperCommented:
WhatArea = Case
     When Area1 = 1 then 'Washington'
     When Area2 = 1 then 'Oregon'
     Else 'Idaho'
End
0
 
adwisemanCommented:
Note, my case statement is wrong, bit are 0=False 1=True as apposed to Access.
0
 
Scott PletcherSenior DBACommented:
Then you can do this:

SELECT WhatArea, ...
FROM yourTable
WHERE ...

You can even do this:

SELECT *
FROM yourTable
WHERE WhatArea = 'Washington'

It's also easier to add/change the definition this way, because it's defined in only one place.  And it's somewhat more efficient, because SQL has already processed and stored the defintion of the column.  Finally, there is no unnecessary overhead because SQL doesn't materialize the column unless it's referenced in a query.
0
 
Krys_WilsonAuthor Commented:
Thanks for all the Case Statements guys!

Scott,

So what you are saying is use this syntax in my view.

ALTER TABLE yourTable
ADD WhatArea AS CASE WHEN Area1 = 1 THEN 'Washington' WHEN Area2 = 1 THEN 'Oregon' ELSE 'Idaho' END

SELECT *
FROM yourTable
Where Whatarea = "Washington"


Is that correct?  Can I have 2 statements like that in the same view?

Or do I have to do it in one view, then base my second view off the first view?

Thanks for the prompt responses guys!
0
 
Scott PletcherSenior DBACommented:
No, the ALTER TABLE is not part of a view, it is run once again the main table to define the virtual column "WhatArea".  That column can then be referenced by any view or SELECT statement.
0
 
SharperCommented:
To keep the change only in your view use

select
     ...
     ,WhatArea = Case
             When Area1 = 1 then 'Washington'
             When Area2 = 1 then 'Oregon'
             Else 'Idaho'
      End
from
     ...

Scott's method alters the appearance of the underlying table so it seems to have a column called Whatarea which would be included in your view if your view were constructed as

create view MyView
as
select * from Underlying_Table
0
 
Krys_WilsonAuthor Commented:
Thanks for the help on this one guys.  I used a CASE Statement to get the results I needed.  

Not sure who to give the points to as you all helped with a case statement of some sort.  Any suggestions?

That is really interesting about the virtual column.  

Thanks again

Chris
0
 
SharperCommented:
adwiseman actually had the correct answer first with a slightly different syntax.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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