sql server new users: Conditional statment question. -- sql server new users

Re: Conditional statment question.

Jim Moberg
10/20/2006 10:42:01 AM

After looking at what I wrote I realized I have you the wrong code. Here is
the correct line of code:

RepClass: IIf(TABLEA.FIELDA=2,IIf(TABLEA.FIELDB=Yes,"1","2"),TABLEB.FIELDG)

This is saying:

If TABLEA.FIELDA=2
THEN
IF TABLEA.FIELDB=YES
THEN
RepClass=1
ELSE
RepClass=2
ENDIF
ELSE
RepClass=TABLEB.FIELDG
ENDIF

Can this be done in T-SQL? If so then where and how do I do this?

[quoted text, click to view]

Re: Conditional statment question.

Hilary Cotter
10/20/2006 11:46:55 AM

I think it should be

Create Table Mytable(NewC1ID int default 0) or

Create Table Mytable(NewC1ID char default "0")

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

[quoted text, click to view]

Re: Conditional statment question.

Warren Brunk
10/23/2006 9:13:08 PM

I am not sure what you are looking for but I wanted to give you some SQL
code that you could look at. If you are passing in the 3 columns as
paramaters then you wont need to do the selects.

[quoted text, click to view]

Declare @TableAFieldAValue varchar(255)
Declare @TableAFieldBValue varchar(255)
Declare @TableBFieldGValue varchar(255)
Declare @repclass int

Select @TableAFieldAValue = fielda, @TableAFieldBValue = fieldb
From tableA

Select @TableBFieldGValue = fieldg
From tableB

if @TableAFieldAValue = 2
begin
if @TableAFieldBValue = 'yes' --if this is a boolen then you could
also do a 1
begin
set @repclass = 1
end
else
begin
set @repclass = 2
end
end
Else
begin
set @repclass = @TableBFieldGValue
end

select @repclass

--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
Tech Blog - www.technologyis.com
*/

[quoted text, click to view]

Re: Conditional statment question.

Hugo Kornelis
10/26/2006 1:06:25 AM

[quoted text, click to view]

Hi Jim,

You can convert the IIf expression to an equivalent CASE expression in
T-SQL. I think that the following expression is the equivalent you need:

SELECT ....,
CASE WHEN A.FieldA = 2 AND A.FieldB = 'YES'
THEN 1
WHEN A.FieldA = 2 /* And A.FieldB <> 'YES' */
THEN 2
/* ELSE A.FieldA <> 2 */
ELSE B.FieldG
END AS RepClass,
...
FROM TableA AS A
INNER JOIN TableB AS B
ON ....
WHERE ....;

--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server new users group.