Results 1 to 5 of 5

Thread: dynamic formula

  1. #1
    Join Date
    Feb 2009
    Posts
    51

    Unanswered: dynamic formula

    I got a situation to create a dynamic formula.
    in that

    it is like
    select col1 - colx as 'Value' from table1

    colx is dynamic it can be

    col_a if col_a value <> 0
    col_b if col_b value <> 0
    col_c if col_c value <> 0

    (priority in order col_a, col_b , col_c)

    Thanks
    .......
    She drank beer with Coke Cola
    and that's the way I like it...
    .......

  2. #2
    Join Date
    Feb 2009
    Posts
    51

    got it as follows

    select col_1 - case when col_a <> 0 then col_a
    when col_b <> 0 then col_b
    when col_c <> 0 then col_c
    else 0
    end as 'final value'
    .......
    She drank beer with Coke Cola
    and that's the way I like it...
    .......

  3. #3
    Join Date
    Feb 2009
    Posts
    51
    Hi all genius pepole

    Any ideas ?
    .......
    She drank beer with Coke Cola
    and that's the way I like it...
    .......

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT col1 - 
           COALESCE(NULLIF(col_a,0)
                   ,NULLIF(col_b,0)
                   ,NULLIF(col_c,0)
                   ,0)
             AS Value
      FROM daTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2009
    Posts
    51
    Hi r937

    Brilliant!!! Thanks heaps!!!
    I did use 'isnull' and 'case' because it can be null but not zero so I thought
    not to use 'coalesce'.

    Wow!!! what nullif can do with coalesce never thought about it.
    .......
    She drank beer with Coke Cola
    and that's the way I like it...
    .......

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •