The following query works in the Manager, but does not work in TEDBQuery if the subquery evaluates to zero. I've never had an aggregate
evaluate to NULL with other products, or am I missing something here? 2.01 build 4.

UPDATE Product SET StockOnHand = (SELECT SUM(RemainingQuantity) FROM ProductPurchase WHERE ProductCode = 465628) WHERE
ProductCode = 465628

Wrapping COALESCE around the subquery does work.

Jeff,

<< Wrapping COALESCE around the subquery does work. >>

Yep.  If the result of the sub-query is NULL, then you'll have to use
COALESCE to massage it into a zero instead.

--
Tim Young
Elevate Software
www.elevatesoft.com

Jeff Dunlop <jeff.dunlop@symbionhealth.com> wrote:

The following query works in the Manager, but does not work in TEDBQuery if the subquery
evaluates to zero. I've never had an aggregate
evaluate to NULL with other products, or am I missing something here? 2.01 build 4.

UPDATE Product SET StockOnHand = (SELECT SUM(RemainingQuantity) FROM ProductPurchase WHERE
ProductCode = 465628) WHERE
ProductCode = 465628


Wow, I did not now <QueryExpression> is allowed in Update. I suppose it is missing from
the documentation then.  

Syntax

UPDATE <TableName>
SET <ColumnName> = <Value> [,<ColumnName> = <Value>])
[WHERE <FilterCondition>]

Leslie,

<< Wow, I did not now <QueryExpression> is allowed in Update. I suppose it
is missing from the documentation then.  >>

Actually, it is assumed in EDB that any time <Value> is mentioned in the
context of a DML statement, that it also includes a scalar query expression
that returns a single row, single column result.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<Actually, it is assumed in EDB that any time <Value> is mentioned in the
context of a DML statement, that it also includes a scalar query expression
that returns a single row, single column result.>>

It is clear now.  Is this somewhere explained in the documnetation?  I really like this
feature,  would like to check if there is something else  I am not aware of.  


Thank You,
Leslie

Leslie,

<< It is clear now.  Is this somewhere explained in the documnetation? >>

Not explicitly, no.  As the manual indicates, we follow the SQL 2003
standard except where indicated that we don't, which is detailed at the
bottom of the manual topic for each SQL statement.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<Not explicitly, no.  As the manual indicates, we follow the SQL 2003
standard except where indicated that we don't, which is detailed at the
bottom of the manual topic for each SQL statement.>>

I meant the explanations of such as  <Value>,<ColumnName>, <FilterCondition> ...
Until now I thought they were all selfexplanatory.  


Regards,
Leslie

PS.: Did you get my e-mail request to reactivate my account to download the trials? I
would like to take a closer look at ElevateDB at the weekend if possible.

Leslie,

<< I meant the explanations of such as  <Value>,<ColumnName>,
<FilterCondition> ... Until now I thought they were all selfexplanatory.
>>

We usually do so in each topic, but I will make a point to review this in
more detail.

<< PS.: Did you get my e-mail request to reactivate my account to download
the trials? I would like to take a closer look at ElevateDB at the weekend
if possible. >>

No, I did not.  However, I will update your existing trial period, so you
will be all set.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thank you Tim!

Leslie