# Column value based on calculation

• ### Question

• Hi,

I am sure this is easy but I dont know the key word I need to search for the answer!

I have column A numeric, Column B numeric I need column C to be 'YES' if  ((B+17.95 - A) <> 0) otherwise 'NO'.

Basically I need to output a column value based on a calculation using two other columns.

How do I do this? Is this an SQL Formula?

Thanks

Jon

Thursday, September 11, 2008 10:59 AM

• A simple CASE statement should do the trick here.

Code Snippet

create table #tbl

(columnA numeric, columnB numeric, columnC varchar(3))

GO

insert into #tbl

values(18.95,1.00,null)

insert into #tbl

values(2,1,null)

GO

select columnA, columnB, CASE WHEN ((ColumnB + cast(17.95 as numeric)) - ColumnA) != 0 THEN 'YES' ELSE 'NO' END

from #tbl

GO

drop table #tbl

HTH!

Thursday, September 11, 2008 11:08 AM
•

Try this

Code Snippet

SELECT CASE WHEN (B+17.95 - A) <> 0 THEN 'YES' ELSE 'NO' end

FROM able_name

Thursday, September 11, 2008 11:10 AM

### All replies

• A simple CASE statement should do the trick here.

Code Snippet

create table #tbl

(columnA numeric, columnB numeric, columnC varchar(3))

GO

insert into #tbl

values(18.95,1.00,null)

insert into #tbl

values(2,1,null)

GO

select columnA, columnB, CASE WHEN ((ColumnB + cast(17.95 as numeric)) - ColumnA) != 0 THEN 'YES' ELSE 'NO' END

from #tbl

GO

drop table #tbl

HTH!

Thursday, September 11, 2008 11:08 AM
•

Try this

Code Snippet

SELECT CASE WHEN (B+17.95 - A) <> 0 THEN 'YES' ELSE 'NO' end

FROM able_name

Thursday, September 11, 2008 11:10 AM
• Thats it! Thanks so much for the quick response.

One other question (If I may!)

One of the column values used in the calculation is returned from a subquery, Am I right in saying you cannot reference the column name you gave it ( as using the " as columnnane"  syntax ) but have to use the whole subquery again in the calculation?

Thanks

Jon

Thursday, September 11, 2008 11:13 AM
• Thats right, columns in a subquery are not available to the parent query. However, in some cases you can rewrite the query to use a JOIN. If you post it to the forums, someone should be able to assist.

HTH!

Thursday, September 11, 2008 11:18 AM
• OK - Cheers.

Thursday, September 11, 2008 11:27 AM