Conditional statements in SQL

Is it possible to have conditional statements in an SQL query? For example, if the value in column A is "C" multiply the value in column B by 1, if the value of column A is "R" muliply the value in column B by -1, then sum the values in column B. Thanks Graeme Muirhead g_muirhead@freeuk.com

In article <ES6t4.6402$74.181096@nnrp3.clara.net>, "Graeme Muirhead" <csi@muirhead.freeuk.com> wrote: > Is it possible to have conditional statements in an SQL query? For example, > if the value in column A is "C" multiply the value in column B by 1, if the > value of column A is "R" muliply the value in column B by -1, then sum the > values in column B. > > Thanks > > Graeme Muirhead > g_muirhead@freeuk.com > > Graeme I guess the CASE statement is pretty close, but probably not exactly what you are after, e.g. SELECT purel_orderno, SUM(CASE WHEN purel_orderno = "ES32500" THENpurel_delqty*1 ELSE purel_delqty*-1 END) FROM purel WHERE purel_orderno MATCHES "ES325??" GROUP BY 1; yields purel_orderno (sum) ES32500 10.00 ES32501 -26.00 I believe CASE came in around 7.3x (we are on 7.31.UC2). The only other alternative is to have 2 selects UNIONed together. Regards Mr Creosote -- "Just a waffer thin mint?" Sent via Deja.com http://www.deja.com/ Before you buy.

In version 7.3 and beyond, you can you the CASE statement SELECT SUM(CASE WHEN col_a = "C" THEN col_b WHEN col_a = "R" THEN (col_b * -1) ELSE 0 END), col_x, col_y FROM ... WHERE ... If you don't have an ELSE condition, rows that do not satisfy any of the WHEN conditions return a null in that "column" (SUM will ignore those rows). You can also nest CASE statements. Rudy Graeme Muirhead wrote: > Is it possible to have conditional statements in an SQL query? For example, > if the value in column A is "C" multiply the value in column B by 1, if the > value of column A is "R" muliply the value in column B by -1, then sum the > values in column B. > > Thanks > > Graeme Muirhead > g_muirhead@freeuk.com