none
Column value based on calculation RRS feed

  • 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

Answers

  • 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
    Moderator
  •  

    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
    Moderator

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
    Moderator
  •  

    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
    Moderator
  • 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
    Moderator
  • OK - Cheers.

     

    Thursday, September 11, 2008 11:27 AM