Helen

According to Firebird_v15.108_ReleaseNotes.pdf document:

NULLIF: Returns NULL for a sub-expression if it has a specific value,
otherwise returns the value of the subexpression.

So, for example:

select nullif(0,0) from rdb$database -> returns NULL
or
select nullif(3,0) from rdb$database -> returns 3

Then:

select nominator/nullif(denominator, 0) from ....

never will raise a DivideByZero exception because if the
denominator=0, then nullif(denominator, 0) return NULL and
nominator/nullif(denominator, 0) also returns NULL.


Also, if I only use "select * from female", everything works ok,
always. The problem is the combination of the 2 selects (female and
herd). It is a very strange problem for me because it depends on
several factors (combination of selects, number of fields in the
view, type of fields, ....) Any change can minimize the error
frequency. To reach to this example, where it always fail, I have
spent a lot of days.
For example, if you replace the query:

SELECT * FROM HERD WHERE HTYPE=0 AND CODI='372'
by
SELECT * FROM HERD WHERE HERD_ID=473

Everything seems to work fine, ... but it is not true, the problem is
latent and the lost connection message appears in any moment (from
minutes to hours).

Also, for example you can delete 1 field of the view, then everything
seems to work fine, but like always, it is only an illusion.


Thank you

--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:
> At 09:47 AM 31/05/2004 +0000, you wrote:
>
> >It is ok, it include additional ' because the DFM format also
uses '
> >to define the string literal.
> >
> >In the IDE | Properties, I can see:
> >
> >SELECT * FROM HERD WHERE HTYPE=0 AND CODI='372'
> >
> >I am sure that all users are using the correct client for the FB
> >server, some installations have been made in new and clean
computers.
> >In any case, this problem also arises in local computer.
> >
> >You can test it in your computer.
> >
> >Thank you for your help
>
> OK, just extracting your metadata, I see several fields in the view
FEMALE that are the probably cause for the server crashes:
> f.lfliveBorn*1.00 / nullif(h.parts,0),
> f.lfStillBorn*1.00 / nullif(h.parts,0),
> (f.lfliveBorn+f.lfstillborn+f.lfmummies)*1.00 / nullif(h.parts,0),
> ...
> 100.0 - 100.0*lftotalweaned/nullif
(lflivebornlitweaned+lfnetfostered,0),
>
> In cases where the NULLIF() function finds a non-null value, it
returns NULL; in cases where it finds NULL, it returns 0. That
means these fields will always be NULL (so the field is purposeless)
or crash the server (if there is NULL, which causes the expression to
return zero). Why? Because division by zero is an illegal
operation.
>
> I think you should receive an exception at least for the first
DivideByZero error; at least in a regular SELECT statement, you get
it.
> e.g.
> select 100/0 as pht from rdb$database
> throws the good-old-catch-all-bad-data exception:
>
> ISC ERROR CODE:335544321
>
> ISC ERROR MESSAGE:
> arithmetic exception, numeric overflow, or string truncation
>
> However, I don't know the dynamics of error delivery when a view is
being assembled. It would be interesting to find out why it crashes
the server rather than throwing the exception.
>
> Still, it's going to be essential for you to redefine these view
columns using some way that doesn't allow zero to turn up as a
divisor; and also so that you actually get a value when the operand
column is not null.
>
> I think you've just picked the wrong function by mistake. Here's
the description of NULLIF:
>
> "NULLIF( ) substitutes NULL for a value if the value resolves to a
non-null value; otherwise, it
> returns the value of the sub-expression."
>
> I'm pretty sure that's not what you actually wanted...
>
> Helen