Sorry Sergey, I don't understand. I'm not intending to do "strange experiments". Your reply is very vague. I told you originally had that clause in, and got wrong results, and this was before I was advised to LEFT JOIN.
Is there a solution to this or is the whole thread too hard to understand?
Cheers
Terry
>I was refering to your original select not your strange experiments..
>
>>Yes, that was covered in the original message as:
>>
>>>>> IIF( NET.Operator == RF1.Operator ;
>>>>> and NET.Route == RF1.ERoute, RF1.ServType, "?") as ServeType ;
>>>>>
>>>>>
>>>>>but this didn't work cos, I presume, if it didn't meet the join criteria on the RF1 table those recs weren't considered. So I took out the
>>>>>
>>>>>
>>>>> and NET.Route == RF1.ERoute ;
>>>>>
>>
>>
>>>Terry,
>>>
>>>You should compare it with previous select. If you did you would see that something is missing in the later.
>>> and NET.Route == RF1.ERoute ;
>>>
>>>>
>>>>I tried the LEFT JOIN and it seemed to be working, then I started getting spurious results on certain data. Compare this with the previous SQL below(er):
>>>>
>>>>SELECT DISTINCT NET.Operator, NET.Route, NET.Start_Date, NET.End_Date, NET.RorF, NET.Passengers, ;
>>>> IIF( NET.Route == NET.Mcl_Rte, " ", NET.Mcl_Rte) as ContrNum, NET.Family, ;
>>>> 0000000 AS CurrPass, 0000000 AS PrevPass, op.Desc as OpDesc, ;
>>>> IIF( NET.Route == RF1.ERoute, RF1.ServType, "?") as ServeType ;
>>>> FROM NotETM NET ;
>>>> JOIN Operator op ;
>>>> On NET.Operator == op.Code ;
>>>> LEFT JOIN RF1 ;
>>>> On NET.Operator == RF1.Operator ;
>>>> WHERE ( ( m.ldStartCurr BETWEEN NET.Start_date AND NET.End_date) ; ...
>>>>
>>>>
>>>>In this case there is just 1 NotETM (NET) record that meets the criteria for each operator
>>>>If we take Operator 54, it has an RF1 record for route 0604, with a servtype of " "
>>>>and ..............................RF1 record for route 0608, with a servtype of "D"
>>>>
>>>>My results give 2 recs for each: 1 operator's with servetype of " " and "?" and the other's with
>>>>servetype of "?" and "D", but the passengers field is the same number in each case, and so it gets counted twice.
>>>>
>>>>ALSO
>>>>
>>>>I have to run through another similar table ETMDATA (to later get some average values to apply to NotETM data)
>>>>
>>>>
>>>>SELECT DISTINCT ET.Operator, ET.Route, ET.Class, ET.Date, ET.RorF, op.Desc as OpDesc, ;
>>>> IIF( ET.Route == RF1.ERoute, RF1.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 RF1 ;
>>>> On ET.Operator == RF1.Operator ;
>>>> WHERE ( ( ET.date BETWEEN ldStartCurr and ldEndCurr) ;
>>>> OR ( ET.date BETWEEN ldStartPrev and ldEndPrev) ); ...
>>>>
>>>>
>>>>but this, since I had to add the LEFT JOIN, now takes FOREVER. Obviously it's creating too big a dataset, or intermediate data, but I don't see why either code is taking so long or failing. I can't really trust this second SQL now.
>>>>
>>>>Any ideas what's wrong?
>>>>
>>>>REALLY appreciate it.
>>>>
>>>>Terry
>>>>
>>>>>You can use LEFT JOIN to get all records for the table on the left and NVL() to convrt NULLs to something else.
>>>>>
>>>>>>Please consider the SQL below.
>>>>>>The NET table is linked to the Route/Family table (RF1) by Operator and Route keys combo.
>>>>>>For each NET record I need to get its ServType code from the matching RF1 table rec.
>>>>>>BUT some NET recs don't have a matching RF1 rec, but still need to be reported.
>>>>>>
>>>>>>originally I just included RF1.ServType in the selection fields but NET recs with no matching RF1 weren't being reported. So I included the IIF() below (in the bigger picture)
>>>>>>
>>>>>>
>>>>>> IIF( NET.Operator == RF1.Operator ;
>>>>>> and NET.Route == RF1.ERoute, RF1.ServType, "?") as ServeType ;
>>>>>>
>>>>>>
>>>>>>but this didn't work cos, I presume, if it didn't meet the join criteria on the RF1 table those recs weren't considered. So I took out the
>>>>>>
>>>>>>
>>>>>> and NET.Route == RF1.ERoute ;
>>>>>>
>>>>>>
>>>>>>from the join but now get duplicated instances. Bit flummoxed at the moment. ANy ideas?
>>>>>>
>>>>>>'ppreciate it.
>>>>>>
>>>>>>
>>>>>>SELECT NET.Operator, NET.Route, NET.Start_Date, NET.End_Date, NET.RorF, NET.Passengers, ;
>>>>>> IIF( NET.Route == NET.Mcl_Rte, " ", NET.Mcl_Rte) as ContrNum, NET.Family, ;
>>>>>> 0000000 AS CurrPass, 0000000 AS PrevPass, op.Desc as OpDesc, ;
>>>>>> IIF( NET.Operator == RF1.Operator ;
>>>>>> and NET.Route == RF1.ERoute, RF1.ServType, "?") as ServeType ;
>>>>>> FROM NotETM NET ;
>>>>>> JOIN Operator op ;
>>>>>> On NET.Operator == op.Code ;
>>>>>> JOIN RF1 ;
>>>>>> On NET.Operator == RF1.Operator ;
>>>>>> and NET.Route == RF1.ERoute ;
>>>>>> WHERE ( ( m.ldStartCurr BETWEEN NET.Start_date AND NET.End_date) ;
>>>>>> ...
>>>>>>
>>>>>>
- 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.