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

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

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