SQL View re: comparing a field returning a value

[DennisTheMenace]

Does anyone know how to use an IF/THEN in a SQL View (or can you)?

I want to compare a field and return a NEW field (for example: return a 'Y' if the shoe size is greater than 11?)

The syntax must look similar to something like this in Enterprise Manager:
IIf((tblName.ShoeSize > '11'), "Y", "N") AS SPECIAL_ORDER

Thanks in advance for any input!
-Dennis

=====================
Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

 

[Pattycake245]

You would need to use a case statement. Something like:

case tblName.ShoeSize when > 11 then 'Y' else 'N' end SPECIAL_ORDER

Tim

 

[DennisTheMenace]

Tim,

I get an error:

"The Query Designer does not support the case SQL construct."

-Dennis

=====================
Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

 

[SQLSister]

Never use the Query Designer to build a complex view; it does not support many valid SQL constructions. Use Query Analyzer instead.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[DennisTheMenace]

Does anyone have a sample I could use? I would like to see the structure of a Query Analyzer to create a view. Is it like the stored procedures??

CREATE Procedure sp_MyProcedure
@value1 nvarchar(50)
AS
Insert Into tbleName(field, field)
Values (@value1, "N")
GO

TX!
Dennis

=====================
Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

 

[JamesLean]

Query Analyzer is one of the SQL Server client tools. It is used to execute T-SQL statements directly against a database.

What SQLSister means is, instead of using the Query Designer to create your view (which it sounds like you are currently doing), execute a CREATE VIEW statement directly from Query Analyzer. Look up CREATE VIEW in Books Online for the full syntax but it may look something like this:

CREATE VIEW my_view
AS

SELECT shoeid, CASE WHEN shoesize > 11 THEN 'Y' ELSE 'N' END AS specialorder
FROM mytable

--James

 

[DennisTheMenace]

Thanks James! - You are correct. I was using the Query Designer tool... I use Query Analyzer all the time, I just didn't know that you could create a view from there?!

I'll post back how I made out!

=====================
Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)