Sweet... thanks guys... it is working well now :-). On 4/8/07, Mogens Melander <[EMAIL PROTECTED]> wrote:
Well, maybe you want to read up on isnull() and case (..) in the manual. It's in there, somewhere.mysql> SELECT CASE 1 WHEN 1 THEN 'one' -> WHEN 2 THEN 'two' ELSE 'more' END; -> 'one' mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END; -> 'true' mysql> SELECT CASE BINARY 'B' -> WHEN 'a' THEN 1 WHEN 'b' THEN 2 END; -> NULL IF(expr1,expr2,expr3) If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used. mysql> SELECT IF(1>2,2,3); -> 3 mysql> SELECT IF(1<2,'yes','no'); -> 'yes' mysql> SELECT IF(STRCMP('test','test1'),'no','yes'); -> 'no' IFNULL(expr1,expr2) If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used. mysql> SELECT IFNULL(1,0); -> 1 mysql> SELECT IFNULL(NULL,10); -> 10 mysql> SELECT IFNULL(1/0,10); -> 10 mysql> SELECT IFNULL(1/0,'yes'); -> 'yes' -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 On Sat, April 7, 2007 20:19, John Kopanas wrote: > I have a query that looks something like this: > > SELECT (c_o_w_inst_rev - c_o_w_estcost)/c_o_w_inst_rev > FROM tmpGovernmentSummaries > > The problem is that sometimes c_o_w_inst_rev is 0 and dividing by zero > returns a NULL. > > If c_o_w_inst_rev == 0 how can I return 0 for the SELECT above instead > of NULL? Can I test in the SELECT if c_o_w_inst_rev is 0 and return 0 > and if not do the math? > > Insight would be greatly appreciated :-) > > > -- > John Kopanas > [EMAIL PROTECTED] > > http://www.kopanas.com > http://www.cusec.net > http://www.soen.info > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean. > -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
