Conditional statements in Views

Hi all,
Another interesting question for ya :P
When contructing a view, I hit across a field that internally is stored as a
single charactor to represent a status, like 'P' = Pending, 'C' = cancelled
etc.
Now, when I create a view, I want this view to say the full word 'Pending'
or 'Cancelled' etc, but when I try to write a conditional expression it kick
it out!
When I use IF statement, it assumes its all a string, and if I use the IIF
it says that the function doesn't exist!?
Seems a little strange how something as simple as a conditional statement
can be made so difficult, so please, someone put me out of my misery and tel
l
me how its done! :P
ThanksTry CASE
"-Ldwater" wrote:

> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||> but when I try to write a conditional expression it kick it out!
Can you be more specific? What conditional expression did you try? What
does "kick it out" mean? Do you get an error message? If so, what is it?
What tool are you using to create your view?

> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
(a) you can't use IF in a view. A view is a query, and is not eligible for
logic flow (if is not a conditional expression).
(b) there is no IIF in T-SQL. The closest place you will find this is
Analysis Services, and then Access.
Perhaps you meant to use CASE.
CREATE VIEW dbo.myView
AS
SELECT status = CASE status
WHEN 'P' THEN 'Pending'
WHEN 'C' THEN 'Cancelled'
END, other columns
FROM table
However, the view designer in Enterprise Manager won't allow for CASE, so I
recommend you get in the habit of creating such scripts in Query Analyzer.
See http://www.aspfaq.com/2455
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Use CASE
SELECT
CASE colname
WHEN 'p' THEN 'Pending'
WHEN 'c' THEN 'Cancelled'
ELSE NULL
END
, colname2
FROM...
Or, create another table with two columns, one for the code and another code
the description and do
a join between the tables.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:9C6EAC75-FCAB-4860-9651-F26D31CD05B7@.microsoft.com...
> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||The CASE expression is what you need and it's actually much more
powerful than the IIF function that you are probably familiar with from
Access and VB:
SELECT ... ,
CASE status
WHEN 'P' THEN 'Pending'
WHEN 'C' THEN 'Cancelled'
END AS status
FROM YourTable
or
SELECT ... ,
CASE
WHEN status = 'P' THEN 'Pending'
WHEN status = 'C' THEN 'Cancelled'
END AS status
FROM YourTable
David Portas
SQL Server MVP
--|||Try the case statement.
CASE WHEN [Field]='C' THEN 'Cancelled' WHEN [Field]='P' THEN 'Pending' ...
ELSE 'default text' END AS [Aliased Field Name]
You can put in as many WHEN clauses as you like, using the syntax above.
Also note that you don't need the ELSE clause. Don't forget the END like I
always do.
Note that IIF isn't a SQL function. It works in Jet DB queries, but not SQL
Server.
"-Ldwater" wrote:

> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||Thanks all, were a little new at writing views, and it seems a bit.. well,
stupid if the CASE statement isn't supported in the Enterprise manager
Thanks for the hints, I think were gonna keep looking into it!|||Enterprise Manager really isn't designed to be a full featured query writing
environment. It is a MANAGEMENT tool and is really not used by anyone
writing serious queries.
Query Analzyer is for writing queries.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:8421F4B4-F86C-40BA-B2DF-3597AB73E7DE@.microsoft.com...
> Thanks all, were a little new at writing views, and it seems a bit.. well,
> stupid if the CASE statement isn't supported in the Enterprise manager
> Thanks for the hints, I think were gonna keep looking into it!