List:General Discussion« Previous MessageNext Message »
From:John Kopanas Date:April 8 2007 2:27pm
Subject:Re: How can I do something like this in mySQL...
View as plain text  
Sweet... thanks guys... it is working well now :-).

On 4/8/07, Mogens Melander <mogens@stripped> 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
> > john@stripped
> >
> > 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/mysql?unsub=1
> >
> >
> > --
> > 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/mysql?unsub=1
>
>


-- 
John Kopanas
john@stripped

http://www.kopanas.com
http://www.cusec.net
http://www.soen.info
Thread
How can I do something like this in mySQL...John Kopanas7 Apr
  • Re: How can I do something like this in mySQL...John Meyer7 Apr
  • Re: How can I do something like this in mySQL...Mogens Melander8 Apr
    • Re: How can I do something like this in mySQL...John Kopanas8 Apr