Computed field with a maximum

Hi all,
I have a table with some fields, and I'm builiding a query upon it
which should have a computed field based upon the values in each row.
For example:
CREATE TABLE mytable(
-- ...
price decimal NOT NULL,
number decimal NOT NULL
);
The computed field should be the product of price and number fields,
multiplied by a coefficient, but should not be greater than a maximum
value. In MySQL, there is a function named IF(), which takes a
condition as the first parameter, and returns the second parameter if
the condition is true, or the third parameter if not. For example, I'd
write something like this in MySQL:
SELECT ..., price, number,
IF(price*number*0.004>10000,10000,price*number*0.004) FROM mytable;
How can I express something like this in SQL Server? I've browsed
through the functions supported by SQL Server, but I have not seen
anything which looks similar to the IF() function in MySQL.
Thanks in advance,The ANSI compliant functionality (which is implemented in SQL Server) is the
CASE expression.
Something like:
SELECT ..., price, number,
CASE WHEN price*number*0.004 > 10000 THEN 10000 ELSE price*number*0.004 END
FROM mytable;
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<ehsan.akhgari@.gmail.com> wrote in message
news:1130663844.123807.231710@.g44g2000cwa.googlegroups.com...
> Hi all,
> I have a table with some fields, and I'm builiding a query upon it
> which should have a computed field based upon the values in each row.
> For example:
> CREATE TABLE mytable(
> -- ...
> price decimal NOT NULL,
> number decimal NOT NULL
> );
> The computed field should be the product of price and number fields,
> multiplied by a coefficient, but should not be greater than a maximum
> value. In MySQL, there is a function named IF(), which takes a
> condition as the first parameter, and returns the second parameter if
> the condition is true, or the third parameter if not. For example, I'd
> write something like this in MySQL:
> SELECT ..., price, number,
> IF(price*number*0.004>10000,10000,price*number*0.004) FROM mytable;
> How can I express something like this in SQL Server? I've browsed
> through the functions supported by SQL Server, but I have not seen
> anything which looks similar to the IF() function in MySQL.
> Thanks in advance,
>|||SELECT answer = case when price * number * 0.004 > 10000 then 10000 else
price * number * 0.004 end
from mytable
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<ehsan.akhgari@.gmail.com> wrote in message
news:1130663844.123807.231710@.g44g2000cwa.googlegroups.com...
> Hi all,
> I have a table with some fields, and I'm builiding a query upon it
> which should have a computed field based upon the values in each row.
> For example:
> CREATE TABLE mytable(
> -- ...
> price decimal NOT NULL,
> number decimal NOT NULL
> );
> The computed field should be the product of price and number fields,
> multiplied by a coefficient, but should not be greater than a maximum
> value. In MySQL, there is a function named IF(), which takes a
> condition as the first parameter, and returns the second parameter if
> the condition is true, or the third parameter if not. For example, I'd
> write something like this in MySQL:
> SELECT ..., price, number,
> IF(price*number*0.004>10000,10000,price*number*0.004) FROM mytable;
> How can I express something like this in SQL Server? I've browsed
> through the functions supported by SQL Server, but I have not seen
> anything which looks similar to the IF() function in MySQL.
> Thanks in advance,
>|||A bit off topic, yet it might prove to be helpful...
Keep in mind that when declaring the decimal datatype the default scale (the
number of decimal digits) is 0.
ML