Error on adding DISTINCT to a SELECT DB2 UDB v8.1.9 Linux

[Bob Stearns]

I am getting unwanted duplicate rows in my result set, so I added the
DISTINCT keyword to my outermost SELECT. My working query then returned
the following message:

DB2 SQL error: SQLCODE: -214, SQLSTATE: 42822, SQLERRMC: CASE...;ORDER BY;2
Message: An expression in the ORDER BY clause in the following
position, or starting with "CASE..." in the "ORDER BY" clause is not
valid. Reason code = "2".

More exceptions ... DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098,
SQLERRMC: 2;-214;42822;CASE. ..|ORDER BY|2

The ORDER BY clause is below:

ORDER BY CASE
WHEN t01.lot_numb IS NULL AND
t01.lot_suffix IS NOT NULL
THEN 0
ELSE t01.lot_numb
END,
COALESCE(t01.lo t_suffix, ''),
animal_id

I didn't include the whole query because of its length.

My problem is that I don't understand DISTINCT's effect on ORDER BY. The
clause works when there is no DISTINCT.

[Serge Rielau]

Re: Error on adding DISTINCT to a SELECT DB2 UDB v8.1.9 Linux

Bob Stearns wrote:[color=blue]
> I am getting unwanted duplicate rows in my result set, so I added the
> DISTINCT keyword to my outermost SELECT. My working query then returned
> the following message:
>
> DB2 SQL error: SQLCODE: -214, SQLSTATE: 42822, SQLERRMC: CASE...;ORDER BY;2
> Message: An expression in the ORDER BY clause in the following
> position, or starting with "CASE..." in the "ORDER BY" clause is not
> valid. Reason code = "2".
>
> More exceptions ... DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098,
> SQLERRMC: 2;-214;42822;CASE. ..|ORDER BY|2
>
> The ORDER BY clause is below:
>
> ORDER BY CASE
> WHEN t01.lot_numb IS NULL AND
> t01.lot_suffix IS NOT NULL
> THEN 0
> ELSE t01.lot_numb
> END,
> COALESCE(t01.lo t_suffix, ''),
> animal_id
>
> I didn't include the whole query because of its length.
>
> My problem is that I don't understand DISTINCT's effect on ORDER BY. The
> clause works when there is no DISTINCT.[/color]
I take a guess and assume that perhaps some of the columns/expressions
in the order by are not in the select list. That's why Db2 is cranky on
the DISTINCT. The hidden columns get in the way.
Push the DISTINCT into a subquery an then ORDER the result:
SELECT ... FROM (SELECT DISTINCT....) AS X
ORDER BY ...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Bob Stearns]

Re: Error on adding DISTINCT to a SELECT DB2 UDB v8.1.9 Linux

Serge Rielau wrote:[color=blue]
> Bob Stearns wrote:
>[color=green]
>> I am getting unwanted duplicate rows in my result set, so I added the
>> DISTINCT keyword to my outermost SELECT. My working query then
>> returned the following message:
>>
>> DB2 SQL error: SQLCODE: -214, SQLSTATE: 42822, SQLERRMC: CASE...;ORDER
>> BY;2
>> Message: An expression in the ORDER BY clause in the following
>> position, or starting with "CASE..." in the "ORDER BY" clause is not
>> valid. Reason code = "2".
>>
>> More exceptions ... DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098,
>> SQLERRMC: 2;-214;42822;CASE. ..|ORDER BY|2
>>
>> The ORDER BY clause is below:
>>
>> ORDER BY CASE
>> WHEN t01.lot_numb IS NULL AND
>> t01.lot_suffix IS NOT NULL
>> THEN 0
>> ELSE t01.lot_numb
>> END,
>> COALESCE(t01.lo t_suffix, ''),
>> animal_id
>>
>> I didn't include the whole query because of its length.
>>
>> My problem is that I don't understand DISTINCT's effect on ORDER BY.
>> The clause works when there is no DISTINCT.[/color]
>
> I take a guess and assume that perhaps some of the columns/expressions
> in the order by are not in the select list. That's why Db2 is cranky on
> the DISTINCT. The hidden columns get in the way.
> Push the DISTINCT into a subquery an then ORDER the result:
> SELECT ... FROM (SELECT DISTINCT....) AS X
> ORDER BY ...
>[/color]
You got it in one. I'm selecting functions of t01.lot_numb and
t01.lot_suffix, but not those columns by themselves.

I had the sql reference manual open to page 555 ff. (for v8) and saw no
mention of having order by columns in the select list when distinct is
used. While the message correct in this case, it could be more informative.

[Serge Rielau]

Re: Error on adding DISTINCT to a SELECT DB2 UDB v8.1.9 Linux

Bob Stearns wrote:[color=blue]
> Serge Rielau wrote:[color=green]
>> Bob Stearns wrote:
>>[color=darkred]
>>> I am getting unwanted duplicate rows in my result set, so I added the
>>> DISTINCT keyword to my outermost SELECT. My working query then
>>> returned the following message:
>>>
>>> DB2 SQL error: SQLCODE: -214, SQLSTATE: 42822, SQLERRMC:
>>> CASE...;ORDER BY;2
>>> Message: An expression in the ORDER BY clause in the following
>>> position, or starting with "CASE..." in the "ORDER BY" clause is not
>>> valid. Reason code = "2".
>>>
>>> More exceptions ... DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098,
>>> SQLERRMC: 2;-214;42822;CASE. ..|ORDER BY|2
>>>
>>> The ORDER BY clause is below:
>>>
>>> ORDER BY CASE
>>> WHEN t01.lot_numb IS NULL AND
>>> t01.lot_suffix IS NOT NULL
>>> THEN 0
>>> ELSE t01.lot_numb
>>> END,
>>> COALESCE(t01.lo t_suffix, ''),
>>> animal_id
>>>
>>> I didn't include the whole query because of its length.
>>>
>>> My problem is that I don't understand DISTINCT's effect on ORDER BY.
>>> The clause works when there is no DISTINCT.[/color]
>>
>> I take a guess and assume that perhaps some of the columns/expressions
>> in the order by are not in the select list. That's why Db2 is cranky
>> on the DISTINCT. The hidden columns get in the way.
>> Push the DISTINCT into a subquery an then ORDER the result:
>> SELECT ... FROM (SELECT DISTINCT....) AS X
>> ORDER BY ...
>>[/color]
> You got it in one. I'm selecting functions of t01.lot_numb and
> t01.lot_suffix, but not those columns by themselves.
>
> I had the sql reference manual open to page 555 ff. (for v8) and saw no
> mention of having order by columns in the select list when distinct is
> used. While the message correct in this case, it could be more informative.[/color]
Punch the feedback button on the topic of the DB2 zOS information center :-)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Bob Stearns]

Re: Error on adding DISTINCT to a SELECT DB2 UDB v8.1.9 Linux

Serge Rielau wrote:[color=blue]
> Bob Stearns wrote:
>[color=green]
>> Serge Rielau wrote:
>>[color=darkred]
>>> Bob Stearns wrote:
>>>
>>>> I am getting unwanted duplicate rows in my result set, so I added
>>>> the DISTINCT keyword to my outermost SELECT. My working query then
>>>> returned the following message:
>>>>
>>>> DB2 SQL error: SQLCODE: -214, SQLSTATE: 42822, SQLERRMC:
>>>> CASE...;ORDER BY;2
>>>> Message: An expression in the ORDER BY clause in the following
>>>> position, or starting with "CASE..." in the "ORDER BY" clause is not
>>>> valid. Reason code = "2".
>>>>
>>>> More exceptions ... DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098,
>>>> SQLERRMC: 2;-214;42822;CASE. ..|ORDER BY|2
>>>>
>>>> The ORDER BY clause is below:
>>>>
>>>> ORDER BY CASE
>>>> WHEN t01.lot_numb IS NULL AND
>>>> t01.lot_suffix IS NOT NULL
>>>> THEN 0
>>>> ELSE t01.lot_numb
>>>> END,
>>>> COALESCE(t01.lo t_suffix, ''),
>>>> animal_id
>>>>
>>>> I didn't include the whole query because of its length.
>>>>
>>>> My problem is that I don't understand DISTINCT's effect on ORDER BY.
>>>> The clause works when there is no DISTINCT.
>>>
>>>
>>> I take a guess and assume that perhaps some of the
>>> columns/expressions in the order by are not in the select list.
>>> That's why Db2 is cranky on the DISTINCT. The hidden columns get in
>>> the way.
>>> Push the DISTINCT into a subquery an then ORDER the result:
>>> SELECT ... FROM (SELECT DISTINCT....) AS X
>>> ORDER BY ...
>>>[/color]
>> You got it in one. I'm selecting functions of t01.lot_numb and
>> t01.lot_suffix, but not those columns by themselves.
>>
>> I had the sql reference manual open to page 555 ff. (for v8) and saw
>> no mention of having order by columns in the select list when distinct
>> is used. While the message correct in this case, it could be more
>> informative.[/color]
>
> Punch the feedback button on the topic of the DB2 zOS information center
> :-)
>
> Cheers
> Serge
>[/color]
I must have the wrong URL. I see no feedback button (but it's late). The
URL I'm using is:

http://publib.boulder.ibm.com/infocenter/db2luw/v8//index.jsp

[Gert van der Kooij]

Re: Error on adding DISTINCT to a SELECT DB2 UDB v8.1.9 Linux

In article <rND1g.97$_h5.6 @fe05.lga>, rstearns1241@ch arter.net says...
[color=blue]
> I must have the wrong URL. I see no feedback button (but it's late). The
> URL I'm using is:
>
> http://publib.boulder.ibm.com/infoce.../v8//index.jsp
>[/color]

At the bottom of that page are a couple of links, one of them is the
feedback link.

[Serge Rielau]

Re: Error on adding DISTINCT to a SELECT DB2 UDB v8.1.9 Linux

Gert van der Kooij wrote:[color=blue]
> In article <rND1g.97$_h5.6 @fe05.lga>, rstearns1241@ch arter.net says...
>[color=green]
>> I must have the wrong URL. I see no feedback button (but it's late). The
>> URL I'm using is:
>>
>> http://publib.boulder.ibm.com/infoce.../v8//index.jsp
>>[/color]
>
> At the bottom of that page are a couple of links, one of them is the
> feedback link.[/color]
FYI, in DB2 Viper there actually is a button at the topic level I believe.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab