Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
Why is this subquery returning NULL? |
Fri, Oct 10 2008 7:13 PM | Permanent Link |
Jeff Dunlop | 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 |
Fri, Oct 10 2008 7:20 PM | Permanent Link |
Jeff Dunlop | Wrapping COALESCE around the subquery does work.
|
Fri, Oct 10 2008 7:26 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Fri, Oct 17 2008 11:03 AM | Permanent Link |
Leslie | 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>] |
Fri, Oct 17 2008 2:56 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Fri, Oct 17 2008 3:09 PM | Permanent Link |
Leslie | 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 |
Fri, Oct 17 2008 5:21 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Fri, Oct 17 2008 5:58 PM | Permanent Link |
Leslie | 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. |
Mon, Oct 20 2008 2:39 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Oct 21 2008 4:53 PM | Permanent Link |
Leslie | Thank you Tim!
Leslie |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |