By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,235 Members | 1,476 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,235 IT Pros & Developers. It's quick & easy.

Strange sort result on DB2 LUW v8.1.9

P: n/a
Please explain tome why the third row of my result is not the fourth
row. In most Western alphabets 'dd'>'c'.
>[SQL] Script lines: 1-29 ---------------------------
SELECT t1.*,
lot_numb, lot_suffix, part_of_lot_numb, part_of_lot_suffix,
CASE WHEN(part_of_lot_numb IS NULL AND part_of_lot_suffix IS NULL)
THEN COALESCE(lot_numb, 0)
ELSE COALESCE(part_of_lot_numb, 0)
END,
CASE WHEN(part_of_lot_numb IS NULL AND part_of_lot_suffix IS NULL)
THEN COALESCE(lot_suffix, '')
ELSE COALESCE(part_of_lot_suffix, '')
END,
COALESCE(lot_numb, 0), COALESCE(lot_suffix, ''),
COALESCE(RTRIM(LTRIM(CHAR(t2.lot_numb))), '') ||
COALESCE(t2.lot_suffix, '') AS lot,
t3.namex,
CHAR(t3.birth_date) AS birth_datep,
t3.sex
FROM is3.auction_done_bids t1
JOIN is3.auction_items t2 ON t2.item_id=t1.item_id
JOIN is3.animals t3 ON t3.bhid=t2.bhid
WHERE t1.auction_id=21 AND t1.invoice_no=2
ORDER BY CASE WHEN(part_of_lot_numb IS NULL AND part_of_lot_suffix IS
NULL)
THEN COALESCE(lot_numb, 0)
ELSE COALESCE(part_of_lot_numb, 0)
END,
CASE WHEN(part_of_lot_numb IS NULL AND part_of_lot_suffix IS
NULL)
THEN COALESCE(lot_suffix, '')
ELSE COALESCE(part_of_lot_suffix, '')
END,
COALESCE(lot_numb, 0), COALESCE(part_of_lot_suffix, '')
FOR READ ONLY 

AUCTION_ID BIDDER_ID ITEM_ID AMOUNT WHEN_BID
INVOICE_NO LOT_NUMB LOT_SUFFIX PART_OF_LOT_NUMB
PART_OF_LOT_SUFFIX 11 12 13 14 LOT NAMEX
BIRTH_DATEP SEX
------------- ------------ ---------- ---------
-------------------- ------------- ----------- -------------
------------------- --------------------- ----- ----- ----- -----
------ ------------------------------ -------------- ------
21 1 25 334 8/10/2006 6:35:15
PM 2 1 (null) (null)
(null) 1 1 1 NAME OF THIS
ANIMAL IS AAA8AAA 2005-11-01 C

21 1 23 333 8/10/2006 6:35:15
PM 2 1 a 1
(null) 1 1 a 1a (null)
(null) B

21 1 24 0 8/10/2006 6:35:15
PM 2 1 dd 1
(null) 1 1 dd 1dd (null)
2005-11-07 (null)

21 1 26 333 8/10/2006 6:35:15
PM 2 1 c 1
(null) 1 1 c 1c (null)
2000-11-01 C

4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 78/ms]

[Executed: 8/11/06 2:40:08 AM GMT ] [Execution: 172/ms]
Aug 11 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a

Bob Stearns wrote:
Please explain tome why the third row of my result is not the fourth
row. In most Western alphabets 'dd'>'c'.
>[SQL] Script lines: 1-29 ---------------------------
SELECT t1.*,
lot_numb, lot_suffix, part_of_lot_numb, part_of_lot_suffix,
CASE WHEN(part_of_lot_numb IS NULL AND part_of_lot_suffix IS NULL)
THEN COALESCE(lot_numb, 0)
ELSE COALESCE(part_of_lot_numb, 0)
END,
CASE WHEN(part_of_lot_numb IS NULL AND part_of_lot_suffix IS NULL)
THEN COALESCE(lot_suffix, '')
ELSE COALESCE(part_of_lot_suffix, '')
END,
COALESCE(lot_numb, 0), COALESCE(lot_suffix, ''),
COALESCE(RTRIM(LTRIM(CHAR(t2.lot_numb))), '') ||
COALESCE(t2.lot_suffix, '') AS lot,
t3.namex,
CHAR(t3.birth_date) AS birth_datep,
t3.sex
FROM is3.auction_done_bids t1
JOIN is3.auction_items t2 ON t2.item_id=t1.item_id
JOIN is3.animals t3 ON t3.bhid=t2.bhid
WHERE t1.auction_id=21 AND t1.invoice_no=2
ORDER BY CASE WHEN(part_of_lot_numb IS NULL AND part_of_lot_suffix IS
NULL)
THEN COALESCE(lot_numb, 0)
ELSE COALESCE(part_of_lot_numb, 0)
END,
CASE WHEN(part_of_lot_numb IS NULL AND part_of_lot_suffix IS
NULL)
THEN COALESCE(lot_suffix, '')
ELSE COALESCE(part_of_lot_suffix, '')
END,
COALESCE(lot_numb, 0), COALESCE(part_of_lot_suffix, '')
FOR READ ONLY 

AUCTION_ID BIDDER_ID ITEM_ID AMOUNT WHEN_BID
INVOICE_NO LOT_NUMB LOT_SUFFIX PART_OF_LOT_NUMB
PART_OF_LOT_SUFFIX 11 12 13 14 LOT NAMEX
BIRTH_DATEP SEX
------------- ------------ ---------- ---------
-------------------- ------------- ----------- -------------
------------------- --------------------- ----- ----- ----- -----
------ ------------------------------ -------------- ------
21 1 25 334 8/10/2006 6:35:15
PM 2 1 (null) (null)
(null) 1 1 1 NAME OF THIS
ANIMAL IS AAA8AAA 2005-11-01 C

21 1 23 333 8/10/2006 6:35:15
PM 2 1 a 1
(null) 1 1 a 1a (null)
(null) B

21 1 24 0 8/10/2006 6:35:15
PM 2 1 dd 1
(null) 1 1 dd 1dd (null)
2005-11-07 (null)

21 1 26 333 8/10/2006 6:35:15
PM 2 1 c 1
(null) 1 1 c 1c (null)
2000-11-01 C

4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 78/ms]

[Executed: 8/11/06 2:40:08 AM GMT ] [Execution: 172/ms]
In 2nd to 4th result rows, both of Expression1 and Expression2 of
ORDER BY were not satisfy WHEN condition(in those rows part_of_lot_numb
IS NOT NULL).
So, it is equivalent to
ORDER BY COALESCE(part_of_lot_numb, 0), COALESCE(part_of_lot_suffix,
''),
COALESCE(lot_numb, 0), COALESCE(part_of_lot_suffix, '')

Aug 11 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.