How can I create IIf in a view

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How can I create IIf in a view

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-11-29 : 10:21:33

I know IIf doesn't exist in SQL. However, I am creating a view and want to check the value of a field and if it is 1 the field returns 'This value' if it is 2 then 'That value' , if 3 then 'Another value'.

How can I do this ?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-29 : 10:34:36

use case when ... then ... else ... end

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-11-29 : 10:38:36

Yes, I found that when I googled it - How do I enter that in the Column line of the field ?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-29 : 10:45:18

You don't, you enter it into the SQL. This means that you can't use your design view any more though.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-29 : 10:45:51

[code]
select case
when field = 1 then 'This value'
when field = 2 then 'That value'
when field = 3 then 'Another value'
else ''
end as MyColumnAlias,
<other columns>
from YourTable
[/code]

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-11-29 : 10:48:45

I need a view though as I have several tables I need to build this query on

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-29 : 10:50:27

Just to add to spirit1's code:


IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vmyview]'))
	drop view [dbo].[vmyview]
GO

create view dbo.vmyview
as
select case 
            when field = 1 then 'This value' 
            when field = 2 then 'That value' 
            when field = 3 then 'Another value'
            else ''
       end as MyColumnAlias, 
       <other columns>
from YourTable

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-11-29 : 10:56:31

Whew !! I'll have ago tomorrow - as the forum is called 'New to SQL Server'..Thanks for your help :-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-29 : 10:57:52

come on... you can't still be new to sql server with 340 posts

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-11-30 : 04:37:24

Yeah - they're all 'how to' questions though - I can't help many other people unfortunately. :-( I'm just self taught too - no expensive courses..........my sql atarted with Access queries !

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-30 : 04:40:55

That's not too bad a way to start, but I will suggest getting a reference manual. I am also self taught (over quite a few years though..lol) and I have now started studying properly for the first time ever as I want the Microsoft qualifications (will make it easier when I finally emigrate)..

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-11-30 : 05:41:26

Going back to your last example RickD - would I put this into a stored procedure ? Ultimately, I want to base a crystal report on what it returns.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-30 : 07:05:44

quote:
Originally posted by Pinto

Going back to your last example RickD - would I put this into a stored procedure ? Ultimately, I want to base a crystal report on what it returns.

You can create a crystal report based on a view

Madhivanan

Failing to plan is Planning to fail

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-30 : 08:04:42

The best practice for this is to create a stored procedure. Yolu can create your view as above and then create an sp to just call this view. The benefit of doing it this way is that you have a better security model than just using the view and selecting from it directly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources