Simple SQL syntax question (I think)

Simple SQL syntax question (I think)

Post by Jerr » Tue, 02 Nov 1999 04:00:00



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

 
 
 

Simple SQL syntax question (I think)

Post by Tim Fergus » Tue, 02 Nov 1999 04:00:00



>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

--


 
 
 

Simple SQL syntax question (I think)

Post by Jim in Clevelan » Tue, 02 Nov 1999 04:00:00



> 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

 
 
 

Simple SQL syntax question (I think)

Post by anthony_k.. » Wed, 03 Nov 1999 04:00:00


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




> >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.
 
 
 

Simple SQL syntax question (I think)

Post by Tim Fergus » Wed, 03 Nov 1999 04:00:00



>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

--

 
 
 

Simple SQL syntax question (I think)

Post by Tim Fergus » Thu, 04 Nov 1999 04:00:00




>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

--

 
 
 

Simple SQL syntax question (I think)

Post by Durai Mahesh Nataraja » Fri, 05 Nov 1999 04:00:00


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)

> 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

 
 
 

1. Newbie - Easy (I think) SQL Syntax Question, 3 table join

I have three identical tables, t1, t2, t3

Each table contains a complete list of files on a particular server.

One of the fields in each table is owner (the owner of an individual
file)

What is the SQL syntax to get a list of all the files owned by a
single user "abcde" from the three tables ?

I can do this in three statements:

Select * from t1 where t1.owner = "abcde";
Select * from t2 where t2.owner = "abcde";
Select * from t3 where t3.owner = "abcde";

The question is how can I do this in one statement?

Here is my first attempt:

Select * from t1,t2,t3
where t1.owner = "abcde"
and t1.owner = t2.owner
and t2.owner = t3.owner

This fails to give the correct answer.

select * from (t1 INNER JOIN t2 on t1.owner = t2.owner) INNER JOIN t3
on t3.owner = t1.owner

This also fails to give the correct answer.

Thanks,

John

2. SQL Select question

3. Simple SQL Question I think

4. Declarative Referential Integrity vs. Triggers?

5. a simple SQL question, i think.

6. Internal Server Error 500

7. Simple SQL question (I think)

8. Wanted: C/ESQL code!

9. Simple SQL syntax question

10. simple SQL syntax question

11. Simple SQL syntax question on Select command

12. Simple SQL syntax question