472,958 Members | 2,316 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 software developers and data experts.

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

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.lot_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.
Apr 19 '06 #1
6 13418
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.lot_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 ...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 19 '06 #2
Serge Rielau wrote:
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.lot_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 ...

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.
Apr 19 '06 #3
Bob Stearns wrote:
Serge Rielau wrote:
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.lot_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 ...

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.

Punch the feedback button on the topic of the DB2 zOS information center :-)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 20 '06 #4
Serge Rielau wrote:
Bob Stearns wrote:
Serge Rielau wrote:
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.lot_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 ...

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.


Punch the feedback button on the topic of the DB2 zOS information center
:-)

Cheers
Serge

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
Apr 20 '06 #5
In article <rN************@fe05.lga>, rs**********@charter.net says...
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


At the bottom of that page are a couple of links, one of them is the
feedback link.
Apr 20 '06 #6
Gert van der Kooij wrote:
In article <rN************@fe05.lga>, rs**********@charter.net says...
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


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

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
Apr 20 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: bhieb | last post by:
I have a data control that I write a sql statement on the fly and populate the recordset like so. Set Rst = Db.OpenRecordset("SELECT DISTINCT * FROM Source_UnitMaster .... join and where...
1
by: Bryan Zash | last post by:
When querying a bit field, I am encountering a problem with MS SQL Server returning a larger number of records for a table than the actual number of records that exist within that table. For...
3
by: JSubadhra | last post by:
Hi, I am very new to ASP. I am trying to change another person's code. I googled my query and read a lot of ideas. But my problem is as I try to change one thing, someother code gets affected....
2
by: YFS DBA | last post by:
Hello again; I've got a small invoicing database I'm trying to create. One of the tables is set up as follows: Client# Date Claim# Amount 1001 10/10/03 ...
6
by: Bob Stearns | last post by:
I am getting duplicate rows back from a select distinct statement of the form: SELECT DISTINCT 'jhough', '000111', t0.bhid FROM (SELECT lots of good stuff) t0 LEFT OUTER JOIN another_table ...
0
by: EJO | last post by:
Thanks everyone! My workgroup is in a mixed enviroment for using an Access 2k mde as the front end of a sql server 2000 which is running on the same machine that some of my users access using...
8
by: sehiser | last post by:
Hello, I've been reading up on xpath and I am able to access elements with it. I haven't been able to figure one thing out though. How would I use XPath to select an element where the text...
5
by: Justin Fancy | last post by:
Hi everyone, I need some help. I'm placing text files into a created database using vb.Net. The problem is that, i need two seperate sql statements to add both files because they are in...
3
by: darkos32 | last post by:
hi,all....i got this error : ORDER BY items must appear in the select list if SELECT DISTINCT is specified. my query is : select distinct(t1.) from as t1, as t2 where t1.=t2. order by t2....
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
2
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.