454,235 Members | 1,476 Online
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