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
>