PHP SQL - using 'IF' in a query (MSSQL)

Author

using 'IF' in a query (MSSQL)

Kimmo Laine

2005-09-12, 3:56 am

This is XXXXing ridiculous, but I can't seem to get this working.... I want
to use if-statement inside a query. I remember clearly that something like
this would actually work in MySQL, but so far I haven't got it working in
MSSQL.

Trying something like
"SELECT IF(dsum > 100, 100, dsum) as dsum_2 from view1"

Meaning, that if the particular row has dsum larger than 100, then output
100, but if it's less than 100, then output whatever dsum is. Doesn't work.
Parse errors are all I get. I'm basicly trying to select the smaller of 100
and dsum. dsum is something between 0...120.

Like this:
dataset | result of select
dsum | dsum_2
---------------
100 | 100
50 | 50
120 | 100
75 | 75
105 | 100

Thanks in advance

--
Welcome to Usenet! Please leave tolerance, understanding
and intelligence at the door. They aren't welcome here.
eternal piste erection miuku gmail piste com

Stefan Rybacki

2005-09-12, 7:56 am

Kimmo Laine wrote:
> This is XXXXing ridiculous, but I can't seem to get this working.... I want
> to use if-statement inside a query. I remember clearly that something like
> this would actually work in MySQL, but so far I haven't got it working in
> MSSQL.
>
> Trying something like
> "SELECT IF(dsum > 100, 100, dsum) as dsum_2 from view1"
>
> Meaning, that if the particular row has dsum larger than 100, then output
> 100, but if it's less than 100, then output whatever dsum is. Doesn't work.
> Parse errors are all I get. I'm basicly trying to select the smaller of 100
> and dsum. dsum is something between 0...120.
>
> Like this:
> dataset | result of select
> dsum | dsum_2
> ---------------
> 100 | 100
> 50 | 50
> 120 | 100
> 75 | 75
> 105 | 100
>
>
> Thanks in advance
>

Maybe MSSQL Server supports UNION?

This way you could try something like this:

SELECT dsum FROM view1 WHERE dsum<=100
UNION
SELECT 100 as dsum FROM view1 WHERE dsum>100

Stefan

Kimmo Laine

2005-09-12, 7:56 am

"Stefan Rybacki" <stefan.rybacki@gmx.net> wrote in message
news:3okvp8F6du39U1@individual.net...
> Kimmo Laine wrote:
>
> Maybe MSSQL Server supports UNION?
>
> This way you could try something like this:
>
> SELECT dsum FROM view1 WHERE dsum<=100
> UNION
> SELECT 100 as dsum FROM view1 WHERE dsum>100
>

That's actually not a bad idea. I must try that. Thanks for the tip.

--
Welcome to Usenet! Please leave tolerance, understanding
and intelligence at the door. They aren't welcome here.
eternal piste erection miuku gmail piste com

Andy Hassall

2005-09-12, 7:56 am

On Mon, 12 Sep 2005 11:50:52 +0300, "Kimmo Laine"
<eternal.erectionN05P@Mgmail.com> wrote:

>I want
>to use if-statement inside a query. I remember clearly that something like
>this would actually work in MySQL, but so far I haven't got it working in
>MSSQL.
>
>Trying something like
>"SELECT IF(dsum > 100, 100, dsum) as dsum_2 from view1"

The Standard (with a capital 'S', i.e. SQL92) statement is CASE.

SELECT CASE
WHEN dsum > 100 THEN 100
ELSE dsum
END dsum_2
from view1

If this doesn't work, ask in an MSSQL group.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Kimmo Laine

2005-09-12, 7:56 am

"Stefan Rybacki" <stefan.rybacki@gmx.net> wrote in message
news:3okvp8F6du39U1@individual.net...
> Kimmo Laine wrote:
>
> Maybe MSSQL Server supports UNION?
>
> This way you could try something like this:
>
> SELECT dsum FROM view1 WHERE dsum<=100
> UNION
> SELECT 100 as dsum FROM view1 WHERE dsum>100
>

Works like a charm. Sadly, what Andy suggested as the standard way, didn't
work. MSSQL doesn't support such syntax. F*cking Microsoft piece of crap.
Gotta do it the wrong way then, as long as it works.

--
Welcome to Usenet! Please leave tolerance, understanding
and intelligence at the door. They aren't welcome here.
eternal piste erection miuku gmail piste com

Hilarion

2005-09-12, 6:56 pm

> Works like a charm. Sadly, what Andy suggested as the standard way, didn't
> work. MSSQL doesn't support such syntax. F*cking Microsoft piece of crap.
> Gotta do it the wrong way then, as long as it works.

Checked on MS SQL Server 2000 and it does work.

Hilarion

PS.: From "Microsoft SQL Server 2000 Books Online (Updated)":

CASE

Evaluates a list of conditions and returns one of multiple possible result
expressions.

CASE has two formats:
+ The simple CASE function compares an expression to a set of simple
expressions to determine the result.
+ The searched CASE function evaluates a set of Boolean expressions
to determine the result.
Both formats support an optional ELSE argument.

Syntax

Simple CASE function:

CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Searched CASE function:

CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Arguments

input_expression
Is the expression evaluated when using the simple CASE format.
input_expression is any valid MicrosoftR SQL ServerT expression.
WHEN when_expression
Is a simple expression to which input_expression is compared when
using the simple CASE format. when_expression is any valid SQL
Server expression. The data types of input_expression and each
when_expression must be the same or must be an implicit conversion.
n
Is a placeholder indicating that multiple WHEN when_expression
THEN result_expression clauses, or multiple WHEN Boolean_expression
THEN result_expression clauses can be used.
THEN result_expression
Is the expression returned when input_expression equals
when_expression evaluates to TRUE, or Boolean_expression evaluates
to TRUE. result expression is any valid SQL Server expression.
ELSE else_result_expression
Is the expression returned if no comparison operation evaluates to
TRUE. If this argument is omitted and no comparison operation
evaluates to TRUE, CASE returns NULL. else_result_expression is
any valid SQL Server expression. The data types of
else_result_expression and any result_expression must be the same
or must be an implicit conversion.
WHEN Boolean_expression
Is the Boolean expression evaluated when using the searched CASE
format. Boolean_expression is any valid Boolean expression.

Result Types

Returns the highest precedence type from the set of types in
result_expressions and the optional else_result_expression. For more
information, see Data Type Precedence.

Result Values

Simple CASE function:
+ Evaluates input_expression, and then, in the order specified, evaluates
input_expression = when_expression for each WHEN clause.
+ Returns the result_expression of the first
(input_expression = when_expression) that evaluates to TRUE.
+ If no input_expression = when_expression evaluates to TRUE, SQL Server
returns the else_result_expression if an ELSE clause is specified, or
a NULL value if no ELSE clause is specified.

Searched CASE function:
+ Evaluates, in the order specified, Boolean_expression for each WHEN clause.
+ Returns result_expression of the first Boolean_expression that evaluates
to TRUE.
+ If no Boolean_expression evaluates to TRUE, SQL Server returns the
else_result_expression if an ELSE clause is specified, or a NULL value
if no ELSE clause is specified.

Sponsored Links