Decision-Making Statement

Question

Post reply

Decision-Making Statement

stankirk

Old Hand

Points: 394
More actions
February 24, 2009 at 6:19 pm

#197453

I need to put data in an empty table named DATA_OBJECT_TEMP.
Something like this works fine. this is for 3 columns.
INSERT INTO DATA_OBJECT_TEMP(column1, column2, column3)
SELECT SAT.ATV, SAT.CUI, CONSO.STR
FROM CONSO INNER JOIN SAT ON CONSO.CUI = SAT.CUI
WHERE (SAT.ATN = 'TestString') AND (SAT.SAB = 'MyString') AND ((CONSO.TTY = 'ABC') OR (CONSO.TTY = 'BCD'))
But the 4th column of DATA_OBJECT_TEMP is where I have a problem. It is integer data type.
I need to look at CONSO.TTY (varchar) and if its value is 'ABC' then I want to make the value of column4 of DATA_OBJECT_TEMP be the integer 1. If CONSO.TTY = 'BCD', then the value of column4 should be the integer 2. If CONSO.TTY is neither 'ABC' nor 'BCD', then column4 should be the integer 3.
I'm a C# programmer, and this kind of decision-making statement is so easy to do in C#. But I'm pulling my hair out :crazy: trying to set this up in SQL Server as a stored procedure. I think I need to use a variable and either IF or CASE, but just can't work out the syntax, after hours of trying.
Any help would be much appreciated.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

SQL has a case statement so you can say exactly that...when somestring then ...

only caveat is all the values returned by the case statement must be the same data type.

but you can test other conditions...like

CASE WHEN FIRSTNAME = '' THEN 1

WHEN LASTNAME = SMITH AND STATE='NY' THEN 2

WHEN CREATEDDATE > GETDATE() THEN 3

ELSE 4

END

INSERT INTO DATA_OBJECT_TEMP(column1, column2, column3,column4)

SELECT SAT.ATV, SAT.CUI, CONSO.STR

CASE

WHEN OBJECT='ABC' THEN 1

WHEN OBJECT='BCD' THEN 2

ELSE 0

END

FROM CONSO INNER JOIN SAT ON CONSO.CUI = SAT.CUI

WHERE (SAT.ATN = 'TestString') AND (SAT.SAB = 'MyString') AND ((CONSO.TTY = 'ABC') OR (CONSO.TTY = 'BCD'))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

stankirk Old Hand Points: 394 More actions · Answer 2

Thank you VERY much.

After looking at what you wrote, I just had to put a comma at the end of the following line.

SELECT SAT.ATV, SAT.CUI, CONSO.STR

And changed your word "Object" to the column name CONSO.TTY.

And it worked. 😀