Hi Boris
DOH!
Yes the old order or evaluation of operators has bitten me. I should have known better and realised.
Thanks. There was no need to explain as this is one oif the first fundamentals of computing that I ever learned - so more fool me! :-)
I did that but unfortunately (and very strangley) I'm still getting the same spurious answer!
I'm curious as to your suggested SQL:
AND ;
OpClass.Operator IS NULL
What's that all about?
Cheers
Terry
Terry
>>Hi Searchers
>>
>>The below SQL is certainly not getting me the results I expect and, worse still gives me 1 spurious result.
>>
>>I've already done a search to multiply the number of journies done on eligible bus services, by a certain factor, to give a "trips" total for each bus operator, route no + (ticket) class, et al, within 2 date ranges.
>>
>>Now I wish to list all the journies that didn't get included in the prev. report because although the (bus) operator had a (ticket) CLASS, the class didn't have an associated MULTIPLIER in the OpClass Table.
>>
>>OpClass Table is as follows:
>>
>>Operator#, Class#, Multiplier, ...
>>
>>And Operator:
>>
>>Operator#, Class#, ...
>>
>>
>>The ETMDATA table holds details of all Electronic Ticket Machine (ETM) transactions over a period.
>>The reason why OpClass needs the 2 keys is that a class is not unique to an operator, and certain class nos mean different things to different operators, but are issued by the ETM manufacturer.
>>
>>Have I got the NOT EXISTS logic wrong or what is it?
>>
>>'ppreciate it.
>>
>>Terry
>>
>>
>>Select DISTINCT ET.Operator as MLOperator, ET.Class as MLClass, ;
>> op.Desc as MLOpDesc, rf.ServType as MLServType, ;
>> SUM( IIF( BETWEEN( ET.date, ldStartCurr, ldEndCurr), ET.Number, 0000000)) AS MLCurrPass, ;
>> SUM( IIF( BETWEEN( ET.date, ldStartPrev, ldEndPrev), ET.Number, 0000000)) AS MLPrevPass ;
>> FROM ETMData ET ;
>> JOIN Operator op ;
>> On ET.Operator == op.Code ;
>> JOIN RteFam rf ;
>> On ET.Operator == rf.Operator ;
>> and ET.Route == rf.ERoute ;
>> WHERE ET.date BETWEEN ldStartCurr and ldEndCurr ;
>> OR ET.date BETWEEN ldStartPrev and ldEndPrev ;
>> AND NOT EXISTS ;
>> ( Select * from OpClass ;
>> WHERE OpClass.Operator == ET.Operator ;
>> AND OpClass.Class == ET.Class ) ;
>> ORDER BY ET.Operator, ET.Class ;
>> INTO Cursor csrMultless
>>
>>
>
>When you use OR and AND in WHERE you must use parenthesis to show which must be evaluated first because with that select you will get ALL records which satisfy following condition:
>WHERE ET.date BETWEEN ldStartCurr and ldEndCurr ;
> OR (ET.date BETWEEN ldStartPrev and ldEndPrev AND NOT EXISTS ...)
>
>BTW I would try this select:
>
>
>
>Select DISTINCT ET.Operator as MLOperator, ET.Class as MLClass, ;
> op.Desc as MLOpDesc, rf.ServType as MLServType, ;
> SUM( IIF( BETWEEN( ET.date, ldStartCurr, ldEndCurr), ET.Number, 0000000)) AS MLCurrPass, ;
> SUM( IIF( BETWEEN( ET.date, ldStartPrev, ldEndPrev), ET.Number, 0000000)) AS MLPrevPass ;
> FROM ETMData ET ;
> INNER JOIN Operator op On ET.Operator == op.Code ;
> INNER JOIN RteFam rf On ET.Operator == rf.Operator and ET.Route == rf.ERoute ;
> LEFT JOIN OpClass ON OpClass.Operator == ET.Operator AND OpClass.Class == ET.Class
> WHERE (ET.date BETWEEN ldStartCurr and ldEndCurr OR;
> ET.date BETWEEN ldStartPrev and ldEndPrev) AND ;
> OpClass.Operator IS NULL
> ORDER BY ET.Operator, ET.Class ;
> INTO Cursor csrMultless
>
- Whoever said that women are the weaker sex never tried to wrest the bedclothes off one in the middle of the night
- Worry is the interest you pay, in advance, for a loan that you may never need to take out.