Den 2013-01-21 09:23 skrev Gary Benade såhär:

> On 1/20/2013 10:31 PM, Kjell Rilbe wrote:
>> Den 2013-01-19 08:27 skrev Gary Benade såhär:
>>> On 1/18/2013 2:48 PM, Kjell Rilbe wrote:
>>>> Den 2013-01-18 13:30 skrev Gary Benade såhär:
>>>>> On 1/18/2013 2:02 PM, Kjell Rilbe wrote:
>>>>>> Den 2013-01-18 12:56 skrev Kjell Rilbe såhär:
>>>>>>> Hi,
>>>>>>>
>>>>>>> This SQL returns 2533 records, none of which contain B."Kod" starting
>>>>>>> with 1711 or 2111 (checked thoroughly):
>>>>>>> select B.*
>>>>>>> from "Branschkod" B
>>>>>>> inner join "Branschkod" B2 on B2."Namn" = B."Namn"
>>>>>>> where B."ECO_ID" <> B2."ECO_ID"
>>>>>>>
>>>>>>> Now, I add a line to the WHERE:
>>>>>>> select B.*
>>>>>>> from "Branschkod" B
>>>>>>> inner join "Branschkod" B2 on B2."Namn" = B."Namn"
>>>>>>> where B."ECO_ID" <> B2."ECO_ID"
>>>>>>> and B."Kod" in ('17111', '17112', '17113', '21111', '21112', '21113')
>>>>>>>
>>>>>>> This select DOES return six records, containing B."Kod" starting with
>>>>>>> 1711 or 2111 (one for each code in the list).
>>>>>>>
>>>>>>> How can this happen?
>>>>>>>
>>>>>> Note: I tried inserting the results of the first query into a new table.
>>>>>> Then I did
>>>>>>
>>>>>> select *
>>>>>> from newtable
>>>>>> where "Kod" in ('17111', '17112', '17113', '21111', '21112', '21113')
>>>>>>
>>>>>> It did not return any records. Still the second select does return six
>>>>>> records.
>>>>>>
>>>>>> Kjell
>>>>> I have a feeling it my be related to nulls in ECO_ID, what do you get
>>>>> when you run the following query?
>>>>>
>>>>> select B.*
>>>>> from "Branschkod" B
>>>>> inner join "Branschkod" B2 on B2."Namn" = B."Namn"
>>>>> where coalesce(B."ECO_ID",'') <> coalesce(B2."ECO_ID",'')
>>>> No nulls in ECO_ID - it's a pk. Your query returns the same 2533 records
>>>> as the first SQL above.
>>> What does this return?
>>>
>>> select * from
>>> {
>>> select B.*
>>> from "Branschkod" B
>>> inner join "Branschkod" B2 on B2."Namn" = B."Namn"
>>> where B."ECO_ID" <> B2."ECO_ID"
>>> ) bb
>>> where bb."Kod" in ('17111', '17112', '17113', '21111', '21112', '21113')
>> That query returns the same six rows as the one with the "in list"
>> directly in the innner query. I had already tried that approach, actually.
>>
>> Regards,
>> Kjell
> If the query works as a subquery then it has to work as a query, lets
> try something else.
> Is it possible that your client is not returning all of the results?

Not really. I use FlameRobin and did "Fetch all records" to make sure it
fetched the complete result set.

> Can you try:
>
> select B.*
> from "Branschkod" B
> inner join "Branschkod" B2 on B2."Namn" = B."Namn"
> where B."ECO_ID" <> B2."ECO_ID"
> order by B."Kod"
>
> to see if Kod's in the 17111 range are getting returned, or if the
> resultset ends before it reaches that range

This query return the usual 2533 records, without the ones starting with
1711 or 2111.

Addin the and... in as follows, just to see:
select B.*
from "Branschkod" B
inner join "Branschkod" B2 on B2."Namn" = B."Namn"
where B."ECO_ID" <> B2."ECO_ID"
and B."Kod" in ('17111', '17112', '17113', '21111', '21112', '21113')
order by B."Kod"

Again, the six 1711/2111 records magically turn up. Both queries still
have this plan:
PLAN SORT (MERGE (SORT (B2 NATURAL), SORT (B NATURAL)))

Kjell

--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64