On 1/21/2013 10:36 AM, Kjell Rilbe wrote:

> 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
>

One last try, what does this give you?

select B.*
from "Branschkod" B
inner join "Branschkod" B2 on B2."Namn" = B."Namn" and B."ECO_ID" <> B2."ECO_ID"

Regards
Gary