Simple SQL syntax question (I think) 
Author Message
 Simple SQL syntax question (I think)

I am selecting data from a dbf via oledb for odbc.  What I want to do is
create a new calculated column similar to this:

select fldOne, fldTwo / fldThree as CalcCol from xyz

The problem that I am having is that fldTwo and or fldThree can be zero and
when I execute the query I get an E_FAIL status error (I am assuming because
of a div by 0).  I have tried an iif(fldTwo = 0 or fldThree = 0, 0 , ,
fldTwo / fldThree ) statement and it does not work because I think it still
executes the division even though the statement is true.  I know the iif
statement does work in my sql but only if the division is always valid.

Thanks for your help in advance!!

Jerry



Fri, 19 Apr 2002 03:00:00 GMT  
 Simple SQL syntax question (I think)

Quote:

>select fldOne, fldTwo / fldThree as CalcCol from xyz

You are right, the help files state that IIF _always_ evaluates both
truepart and falsepart.

In this case, however, you only need to check the numerator:

  SELECT fldOne, fldTwo / IIF(fldTwo=0, 4e4, fldTwo) AS CalcCol

Hope that helps

Tim F

--



Fri, 19 Apr 2002 03:00:00 GMT  
 Simple SQL syntax question (I think)

Quote:

> I am selecting data from a dbf via oledb for odbc.  What I want to do is
> create a new calculated column similar to this:

> select fldOne, fldTwo / fldThree as CalcCol from xyz

> The problem that I am having is that fldTwo and or fldThree can be zero and
> when I execute the query I get an E_FAIL status error (I am assuming because
> of a div by 0).  I have tried an iif(fldTwo = 0 or fldThree = 0, 0 , ,
> fldTwo / fldThree ) statement and it does not work because I think it still
> executes the division even though the statement is true.  I know the iif
> statement does work in my sql but only if the division is always valid.

  As I understand your scenario, the only case that causes div by 0
error is if fldThree = 0.  I just tested this SQL in Access as a Query
on a mock-up of your table:

SELECT fldOne, iif(fldThree = 0, 0, fldTwo / fldThree ) as CalcCol from
xyz

It works fine (well, we know that div by 0 does not *really* equal 0,
but that's the result you want to display, and this displays 0 instead
of Error).  There's no need to deal w/ fldTwo = 0 in your iif, because 0
divided by anything already computes to 0 with no special attention on
our part.
--
Jim in Cleveland
If you're writing to me, in my address
change "REAL_Address.see.below" to "worldnet.att.net"

"What's so funny 'bout peace, love & understanding?"
     - Nick Lowe



Fri, 19 Apr 2002 03:00:00 GMT  
 Simple SQL syntax question (I think)
But why not added the condition "... where fldThree <> 0 " ?? Use
appropriate join to return all fldone, if needed?
Anthony



Quote:

> >select fldOne, fldTwo / fldThree as CalcCol from xyz

> You are right, the help files state that IIF _always_ evaluates both
> truepart and falsepart.

> In this case, however, you only need to check the numerator:

>   SELECT fldOne, fldTwo / IIF(fldTwo=0, 4e4, fldTwo) AS CalcCol

> Hope that helps

> Tim F

> --


Sent via Deja.com http://www.deja.com/
Before you buy.


Sat, 20 Apr 2002 03:00:00 GMT  
 Simple SQL syntax question (I think)

Quote:

>But why not added the condition "... where fldThree <> 0 " ?? Use
>appropriate join to return all fldone, if needed?
>Anthony



>>   SELECT fldOne, fldTwo / IIF(fldTwo=0, 4e4, fldTwo) AS CalcCol

1) Oops: studid type, should have been this:
  SELECT fldOne, fldTwo / IIF(fldThree=0, 4e4, fldThree) AS CalcCol

2) The help states:

Quote:
>IIf always evaluates both truepart and falsepart, even though
>it returns only one of them. Because of this, you should
>watch for undesirable side effects. For example, if
>evaluating falsepart results in a division by zero error, an
>error occurs even if expr is True.

... and this is exactly what the original poster was getting caught
on. This is well known and gravely limits the use of IIF to avoid
UseOfNull errors, DivByZero and so on.

Tim F

--



Sat, 20 Apr 2002 03:00:00 GMT  
 Simple SQL syntax question (I think)

Quote:

>1) Oops: studid type, should have been this:

Oops: even more stupid typo, should have been this:

 1) Oops: stupid typo, should have been this:

I need more sleep :-(

Tim F

--



Sun, 21 Apr 2002 03:00:00 GMT  
 Simple SQL syntax question (I think)
A pretty Simple Way indeed
(select fldOne, fldTwo / fldThree as CalcCol from xyz where fldthree > 0)
union
(select fldOne, fldTwo / 1 as CalcCol from xyz where fldthree =0)
Quote:

> I am selecting data from a dbf via oledb for odbc.  What I want to do is
> create a new calculated column similar to this:

> select fldOne, fldTwo / fldThree as CalcCol from xyz

> The problem that I am having is that fldTwo and or fldThree can be zero and
> when I execute the query I get an E_FAIL status error (I am assuming because
> of a div by 0).  I have tried an iif(fldTwo = 0 or fldThree = 0, 0 , ,
> fldTwo / fldThree ) statement and it does not work because I think it still
> executes the division even though the statement is true.  I know the iif
> statement does work in my sql but only if the division is always valid.

> Thanks for your help in advance!!

> Jerry



Mon, 22 Apr 2002 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. a simple SQL question, i think.

2. Simple SQL question, I think

3. simple syntax i think ?

4. syntax error in my SQL... I think

5. Simple Data Control question (I think)

6. really simple DB question, i think

7. Simple VB6 question - I think!!

8. Very simple question (I think)

9. Simple Question - I think

10. Simple user control question (I think)

11. Simple internet question, I think (or hope)

12. Simple syntax question

 

 
Powered by phpBB® Forum Software