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?

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 bb."Kod"

to see if Kod's in the 17111 range are getting returned, or if the
resultset ends before it reaches that range

Regards
G