Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
Error #700 |
Mon, May 26 2008 2:42 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
select _BoxNo , (Select Count(_BoxNo) from emails where _Boxno = mailboxes._Boxno) AS _OnFile , (Select Count(_BoxNo) from emails where _ReadBy DOES NOT CONTAIN 'RL' AND _Boxno = mailboxes._Boxno) AS _Unread, _OnFile - _Unread AS _Read from mailboxes ElevateDB Error #700 An error was found in the statement at line 4 and column 11 (ElevateDB Error #401 The column _Unread does not exist) Popping in a constant rather than _Unread in the subtraction it works. This one is entertaining. I forgot that mailboxes had a column _OnFile. Then I remembered, set that column to 0's. The result you then get is _OnFile shows the records in emails, _Unread shows the correct results, but _Read shows -12 throughout. I think ElevateDB should either 1) cope with it "properly" and use the derived values throughout or 2) bomb out with a warning that you're overriding existing column names. I assume the correct syntax is select _BoxNo , (Select Count(_BoxNo) from emails where _Boxno = mailboxes._Boxno) AS _OnFile , (Select Count(_BoxNo) from emails where _ReadBy DOES NOT CONTAIN 'RL' AND _Boxno = mailboxes._Boxno) AS _Unread, (Select Count(_BoxNo) from emails where _Boxno = mailboxes._Boxno) - (Select Count(_BoxNo) from emails where _ReadBy DOES NOT CONTAIN 'RL' AND _Boxno = mailboxes._Boxno) AS _Read from mailboxes but in the rough and ready time comparison in EDBManager it takes about 3 times as long. Roy Lambert |
Mon, May 26 2008 11:19 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< Popping in a constant rather than _Unread in the subtraction it works. This one is entertaining. I forgot that mailboxes had a column _OnFile. Then I remembered, set that column to 0's. The result you then get is _OnFile shows the records in emails, _Unread shows the correct results, but _Read shows -12 throughout. I think ElevateDB should either 1) cope with it "properly" and use the derived values throughout or 2) bomb out with a warning that you're overriding existing column names. >> You can't use a SELECT column AS correlation name in any subsequent expressions, so what you're saying is basically impossible anyways - you can't "override" table column names via an AS select column correlation name. I assume the correct syntax is << but in the rough and ready time comparison in EDBManager it takes about 3 times as long. >> Did you make sure that the additional sub-queries were optimized ? And yes, I know that you just raised this issue about the query plans and sub-queries. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, May 27 2008 2:13 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>You can't use a SELECT column AS correlation name in any subsequent >expressions, so what you're saying is basically impossible anyways Shame >- you >can't "override" table column names via an AS select column correlation >name. Shouldn't it crash if you try. In my case (not repeatable cos I've restructured the tables) I definitely got the result from the AS _OnFile sub-query in the result set not the _OnFile in the table. ><< but in the rough and ready time comparison in EDBManager it takes about 3 >times as long. >> > >Did you make sure that the additional sub-queries were optimized ? Since the additional sub-queries were the same as the original sub-queries just being subtracted the answer (to the best of my knowledge and understanding, is yes. Roy Lambert |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |