Solved

# putting IF logic into a calculated column?

Posted on 2010-08-17
Medium Priority
268 Views
With the below query, is it possible to insert some logic which would output a text string into the next column depending on the value of the last column (which is calculated)

e.g. "small" if value <= 10, otherwise "large"

Or do we need to do some logic in TSQL for this?
i.e. open up a cursor, store the value of col#4 in a variable, do our logic on it, and output a string using PRINT depending on its value ?

cheers!
``````select EA.ex_pi_eval_answer_school_id,

``````
0
Question by:zorba111
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 2

Author Comment

ID: 33455172
NB typical output from the current query is like:

2036449      19      5      3.8
4010885      22      10      2.2
1116159      23      5      4.6
2046646      23      5      4.6
4036081      23      5      4.6
1016647      22      5      4.4
2036149      21      5      4.2
4230165      20      5      4

(excel, which I've used as an intermediary to copy this data, has stripped out non-significant trailing zeros)
0

LVL 16

Accepted Solution

vdr1620 earned 1600 total points
ID: 33455212
you can use a CASE Statement Like

OUTPUTColumn = CASE WHEN Value<=10 THEN 'Small' ELSE 'Large' END

The below blog has some good explanation with examples
http://blog.sqlauthority.com/2007/04/14/sql-server-case-statementexpression-examples-and-explanation/
0

Author Comment

ID: 33455333
@vdr1620

Hi this is great, however having read up on CASE WHEN, I fear it will not work.

There are actually three ranges (NOT two) that the values could fall into, and for each value, a different string gets output :-(
0

LVL 16

Assisted Solution

vdr1620 earned 1600 total points
ID: 33455374
Not a problem..you can use

CASE WHEN (condition) THEN Statement
WHEN (condition) THEN Statement
WHEN (condition) THEN Statement END

OUTPUTColumn = CASE WHEN Value<=10 THEN 'Small'
WHEN Value >10 THEN 'Large'
WHEN Value >100 THEN 'SomeotherValue'        END

0

Author Comment

ID: 33455382
@vdr1620

hmm, having looked at Pinal Dave's page (the link you kindly referred me to), I think now that all may not be lost!

I will report back....
0

Author Comment

ID: 33455419
ok, it wasn't in his blog, but in MSDN I found this usage....

SELECT    'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
0

LVL 69

Assisted Solution

Scott Pletcher earned 400 total points
ID: 33460045
You can also do something like this, if you need to:

SELECT
CASE WHEN price IS NULL THEN '...'
WHEN price < 10 THEN '...'
WHEN price >= 10 AND price < 20 THEN '...'
ELSE '...' END +
',' +
CASE
WHEN size < 10 THEN 'Small'
WHEN size > 10 AND size < 20 THEN 'Medium'
WHEN size >= 20 THEN 'Large'
ELSE 'Unknown size' END
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
###### Suggested Courses
Course of the Month12 days, 8 hours left to enroll