473,703 Members | 2,323 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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

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

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

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**********@ch arter.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**********@ch arter.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
2372
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 clause") set dat_Equip.Recordset = Rst (this is a DAO recordset) This gives me a "operation is not supported by this object" at the second line.
1
2638
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 example, my customer table has 1 million unique records, so the results of the following query are as such: select count(customer_nbr) from customer = 1,000,000 There is bit field in the customer table that denotes whether a
3
2533
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. SO I have decided to modify the code I have to do the task. I really need some help here. The task is -- I have 3 drop down boxes, country, state, city. The state has to populate based on country ....
2
2007
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 22.50 3003 10/11/03 18.00 1001 10/22/03 99.95
6
13759
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 t1 ON relevant_stuff WHERE (lots of conditions) After re-reading the relevant pat ofVol 1 of the SQL Reference I am unablee to see how this is possible.
0
1460
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 Win2003 server terminal services. 1. users on the terminal server, co-located with the sql server, have no problems 2. users on their local machine (Win XP), get an error message:
8
12931
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 equals what I'm looking for? If I select a book topic (in the traditional of XML examples) of Programming and it returns 200 books, and I want to specifically select
5
1510
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 different loops. My output comes out to be as Follows: TABLE ---------------------------------------------------- Field1 Field2
3
2521
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. asc when i used this query at mysql,i didnt got any error,but when i try to change to sql_server,i got error.....i want to get distinct name from table one and order by table2.id..
0
9251
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9121
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8962
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7867
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6588
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5922
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4432
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3123
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2069
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.