Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server - Case Statement

Tags:

I'm almost certain you cannot do this within the context of the case statement, and I haven't been able to find any documentation about it, but is it possible to do the following:

SELECT CASE WHEN testValue > 2 
THEN testValue(Without Repeating it) ELSE FailValue)
END 
FROM Table 

A better more thorough example:

Select CASE WHEN (Foo-stuff+bar) > 2 
THEN Conditional statement without >2 Else "Fail"
END 
FROM TABLE

I am looking for a way to create a select without repeating the conditional query.

EDIT: Due to a poor example on my part, and the lack of answers I was looking for:

testValue = (Table.A / Table.B) * Table.C Table.D

SELECT CASE WHEN testValue > 2 
THEN testValue ELSE FailValue)
END 
FROM Table 
like image 219
Elias Avatar asked Oct 07 '13 19:10

Elias


People also ask

What is CASE statement in SQL Server?

The SQL CASE ExpressionThe CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

WHERE can we use CASE statement in SQL?

CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.

How do you handle a case statement in SQL?

SQL Server CASE statement syntax The CASE statement has to be included inside the SELECT Statement. It starts with the CASE keyword followed by the WHEN keyword and then the CONDITION. The condition can be any valid SQL Server expression which returns a boolean value.


1 Answers

Like so

DECLARE @t INT=1

SELECT CASE
            WHEN @t>0 THEN
                CASE
                    WHEN @t=1 THEN 'one'
                    ELSE 'not one'
                END
            ELSE 'less than one'
        END

EDIT: After looking more at the question, I think the best option is to create a function that calculates the value. That way, if you end up having multiple places where the calculation needs done, you only have one point to maintain the logic.

like image 77
UnhandledExcepSean Avatar answered Nov 04 '22 00:11

UnhandledExcepSean