>Hi chaps
>
>This is one of those look-over-my-shoulder jobs:
>
>I added the complex nested IIF() to this SQL. Intellisense matches up all my parenth. and AFAICS the IIF()s are pukka, but the compiler keeps screaming "Function Name is missing) err mess" every time I try to leave the method.
>
>Maybe the SQL has given up on it?
>
>Can anyone see the flaw, cos I can't see the brackets for the parenth any more?
>
>Essentially it's (should be) saying "If there is a matching operator/route combo, in the RteFam table, well if it's dates are current with the ET rec's date, use its SERVTYPE field - else put a '#' (to denote out-of-date). If there is no match, put a '?' to denote 'not known'"
>
>Have that on my desk by the (my) tommorrow morning would you? :-)
>
>'ppreciate it
>
>Terry
>
>
>SELECT DISTINCT ET.Operator, ET.Route, ET.Class, ET.Date, ET.RorF, op.Desc as OpDesc, ;
> IIF( ET.Route == RteFam.ERoute ;
> AND ET.Operator == RteFam.Operator, ;
> IIF( (( ldStartCurr BETWEEN RteFam.Start_date AND RteFam.End_date) ;
> OR ( ldEndCurr BETWEEN RteFam.Start_date AND RteFam.End_date) ;
> OR ( RteFam.Start_date < ldStartCurr AND RteFam.End_date > ldEndCurr) ;
> OR ( RteFam.Start_date => ldStartCurr AND RteFam.End_date <= ldEndCurr) ;
> OR ( ldStartPrev BETWEEN RteFam.Start_date AND RteFam.End_date) ;
> OR ( ldEndPrev BETWEEN RteFam.Start_date AND RteFam.End_date) ;
> OR ( RteFam.Start_date < ldStartPrev AND RteFam.End_date > ldEndPrev) ;
> OR ( RteFam.Start_date => ldStartPrev AND RteFam.End_date <= m.ldEndPrev) ), ;
> RteFam.ServType, "#"), ;
> "?") as ServeType, ;
> IIF( ET.Route == ET.Mcl_Rte, " ", ET.Mcl_Rte) as ContrNum, ET.Family, ;
> SUM( IIF( BETWEEN( ET.date, ldStartCurr, ldEndCurr), ET.Number, 0000000)) AS CurrPass, ;
> SUM( IIF( BETWEEN( ET.date, ldStartPrev, ldEndPrev), ET.Number, 0000000)) AS PrevPass ;
> FROM ETMData ET ;
> JOIN Operator op ;
> On ET.Operator == op.Code ;
> LEFT JOIN RteFam ;
> On ET.Operator == RF1.Operator ;
> and ( ET.Route == RF1.ERoute ;
> or EMPTY( RF1.ERoute) ) ;
> WHERE ( ( ET.date BETWEEN ldStartCurr and ldEndCurr) ;
> OR ( ET.date BETWEEN ldStartPrev and ldEndPrev) ) ;
> AND not DELETED() ;
> GROUP BY ET.Mcl_Rte, ET.Operator, ET.Route, ET.Class ;
> ORDER BY ET.Operator, OpDesc, ET.Route, ET.Class ;
> INTO CURSOR csrETMPassengers NOFILTER
>
>
- BETWEEN..and.. is not allowed into the projection list
- DISTINCT is useless with a GROUP BY select
- DELETED() is not supported with 2 or more tables
- NOFILTER is useless with a JOIN/GROUP/ORDER select
