Wednesday, March 28, 2012

Max Function - But between a fixed value and a column

Dear Sirs / Ladies
In HQL their was a MAX Function that would compare a fixed value
( 1.95 ) with a Column.
An example is MAX( 1.95, RegularBasePrice )
If the RegularBasePrice was 0.95 it would return 1.95 or
If the RegularBasePrice was 2.49 it would return 2.49
Is their anyway to duplicate that in TSQL
Below is an example of the code under HQL
from Item;
where CurrentUnitCost > 0
and CurrentPrice > 0;
update CurrentMargin : ( ( ( CurrentPrice /
Max( 1, CurrentSplit ) ) - CurrentUnitCost ) /
Max( .01, ( CurrentPrice /
Max( 1, CurrentSplit ) ) ) ) * 100;
MarkThis is also the GREATEST( <exp list> ) function in Oracle.In Standard
SQL, you will need a CASE expression like this:
CASE WHEN x > 1.95 THEN x ELSE 1.95 END

No comments:

Post a Comment