SQL View Compare Columns

Kai

Howdy,

I have a view that has 2 float columns. Lets call them colA and colB. I want to add another column that has 1 as its value if colA is greater than colB, and has 0 as its value if colA is less than colB.

Ive tried IIF and if else statements but its always returned errors.

The closest thing i have is

SELECT dbo.table1.colA
dbo.table1.Sales_Rate, 
dbo.table2.colB,
(
Select 1 where (dbo.table1.colA > table2.colB)
) AS isAboveTarget
FROM dbo.table1 CROSS JOIN
dbo.table2

table2 has only one row. This is on SQL Server 2000 by the way. The above code returns 1 if its greater and NULL if its not.


Anyone got any ideas, im sure the answer is staring me in the face but everything ive tried so far doesnt work.

Merrion

Haven't tried it but would this work:-

SELECT dbo.table1.colA
dbo.table1.Sales_Rate, 
dbo.table2.colB,
(
Select Isnull(1,0) where (dbo.table1.colA > table2.colB)
) AS isAboveTarget
FROM dbo.table1 CROSS JOIN
dbo.table2

Kai

Well that seems to be returning the same as my query, 1 where its greater and NULL when its less.
I need something that will return 0 when its less. Thanks for the reply though.

Kai

Just got it :
(
Select Isnull((Select 1 where (dbo.table1.colA > dbo.table2.colB)),0)
) AS isAboveTarget2

thanks for the ponter

bonkey

Kai wrote:

I have a view that has 2 float columns. Lets call them colA and colB. I want to add another column that has 1 as its value if colA is greater than colB, and has 0 as its value if colA is less than colB.

...
Anyone got any ideas, im sure the answer is staring me in the face but everything ive tried so far doesnt work.

Kai...

read up on case statements. Would make a much easier solution.

SELECT ColA,
	   ColB,
	   CASE
		 WHEN ColA > ColB THEN 1
		 WHEN ColB > ColA THEN 0
		 ELSE null
	     END as myComparisonColumn
FROM   myView
...

I threw in a third case to cater for when neither is greater (i.e. they are equal, or at least one of ColA and ColB is Null).

CASE statements are your friend in MSSQL. Even if you're happy with your solution, I strongly recommend you learn them for this type of thing.

jc