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

Matching multiple columns from two tables

Hi all..
I have two tables such as cisco and ciscocom. and i wan to compare each
row of ciscocom with cisco having same column values. i wan to get the
count of matching columns for each row in cisco...
eg:
Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
for each row in ciscocom. please provide me with the procedure...
Waiting for your response...Kuttan
SELECT cisco .*, ciscocom.*
FROM cisco
FULL OUTER JOIN
ciscocom
ON cisco .c1 = ciscocom.c1
AND cisco .c2 = ciscocom.c2
..
AND cisco .cn = ciscocom.cn
WHERE cisco .key IS NULL OR ciscocom.key IS NULL
"Kuttan" <vvyshak@.gmail.com> wrote in message
news:1141210889.041213.70320@.v46g2000cwv.googlegroups.com...
> Hi all..
> I have two tables such as cisco and ciscocom. and i wan to compare each
> row of ciscocom with cisco having same column values. i wan to get the
> count of matching columns for each row in cisco...
> eg:
> Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
> columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
> for each row in ciscocom. please provide me with the procedure...
> Waiting for your response...
>|||thanks a lot..
But it doesnt worked.
Actually i wan the columns that matches with each rows of the cisco
from ciscocom...
Anyway thanx a lot for your response...
thank u so much..|||Hope this is what you're looking for
SELECT COUNT (cisco.Products)
FROM cisco INNER JOIN ciscocom
ON
cisco.Products=cisco.Products AND
cisco.fw=cisco.fw AND
cisco.ports=cisco.ports
/*
* If you want to specify a value for a column
*
*/
WHERE cisco.Products='your_value'

Matching multiple columns from two tables

Hi all..
I have two tables such as cisco and ciscocom. and i wan to compare each
row of ciscocom with cisco having same column values. i wan to get the
count of matching columns for each row in cisco...
eg:
Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
for each row in ciscocom. please provide me with the procedure...
Waiting for your response...On 1 Mar 2006 03:01:21 -0800, Kuttan wrote:

>Hi all..
>I have two tables such as cisco and ciscocom. and i wan to compare each
>row of ciscocom with cisco having same column values. i wan to get the
>count of matching columns for each row in cisco...
>eg:
>Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
>columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
>for each row in ciscocom. please provide me with the procedure...
>Waiting for your response...
Hi Kuttan,
Not sure if I fully understand your requirements. If the answer below is
not what you're looking for, then please check www.aspfaq.com/5006 to
find out how to post CREATE TABLE statements, INSERT statements and
required results in order to get better help.
SELECT a.KeyColumn,
CASE WHEN a.DataColumn1 = b.DataColumn1 THEN 1 ELSE 0 END
+ CASE WHEN a.DataColumn2 = b.DataColumn2 THEN 1 ELSE 0 END
.....
+ CASE WHEN a.DataColumnN = b.DataColumnN THEN 1 ELSE 0 END
AS MatchCount
FROM Table1 AS a
INNER JOIN Table2 AS b
ON a.KeyColumn = b.KeyColumn
Hugo Kornelis, SQL Server MVP