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 10 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(12,2,3);
- 3
mysql SELECT IF(12,'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]
