?
Solved

IF Statement in a View or Stored Procedure

Posted on 2003-02-26
11
Medium Priority
?
282 Views
Last Modified: 2012-06-21
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
Comment
Question by:Krys_Wilson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 14

Expert Comment

by:adwiseman
ID: 8028681
--Try This

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




--adw
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8028691
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
 
LVL 14

Expert Comment

by:adwiseman
ID: 8028693
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 2

Expert Comment

by:Sharper
ID: 8028703
WhatArea = Case
     When Area1 = 1 then 'Washington'
     When Area2 = 1 then 'Oregon'
     Else 'Idaho'
End
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 8028707
Note, my case statement is wrong, bit are 0=False 1=True as apposed to Access.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8028708
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
 
LVL 5

Author Comment

by:Krys_Wilson
ID: 8029105
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8029238
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
 
LVL 2

Accepted Solution

by:
Sharper earned 200 total points
ID: 8029299
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
 
LVL 5

Author Comment

by:Krys_Wilson
ID: 8029396
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
 
LVL 2

Expert Comment

by:Sharper
ID: 8035445
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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question