Re: Defining a Custom Sort Order in SQL

Yes - the ORDER BY was inside a subquery.

A request to anyone who gets an answer to their question - please don't leave
it as an exercise for us overworked folks to figure out from code what the
answer is - help us by putting in a few words - much appreciated, seriously.
And this is not meant to dump on anyone - just that it happens so often that
someone says, I found the answer, thanks. End of story - no clue as to what
that answer was.

Of course, not thinking is something I always try to do!!

Regards
Vern
-------------- Original message --------------
From: Mike <koldark@xxxxxxxxx>

I got it figured out. I wasn't thinking.

SELECT RVEHNO, RVEHKY, RPARTC, CBDESC, RPARTN, AMFGCD, ADESC, ABIN
FROM (
SELECT RVEHNO, RVEHKY, RPARTC, CBDESC, RPARTN, AMFGCD, ADESC, ABIN, 1 AS
SORT
FROM QMFILES.GA560AP, QMFILES.GA600AP, QMFILES.GA122AP
WHERE RPKEY = AKEY AND
RPARTC = CBCODE AND
RVEHKY IN
(SELECT DISTINCT MAX(RVEHKY) AS RVEHKY
FROM QMFILES.GA560AP
GROUP BY RVEHNO) AND
RVEHNO = 2 AND RPARTC = 2
UNION
SELECT RVEHNO, RVEHKY, RPARTC, CBDESC, RPARTN, AMFGCD, ADESC, ABIN, 2 AS
SORT
FROM QMFILES.GA560AP, QMFILES.GA600AP, QMFILES.GA122AP
WHERE RPKEY = AKEY AND
RPARTC = CBCODE AND
RVEHKY IN
(SELECT DISTINCT MAX(RVEHKY) AS RVEHKY
FROM QMFILES.GA560AP
GROUP BY RVEHNO) AND
RVEHNO = 2 AND
RPARTC <> 2) TABLE
ORDER BY SORT, CBDESC, RPARTN

On 5/29/07, Gene Burns wrote:

Is 'Filters' the only thing in the field you are sorting on or just the
first part of the field?
You may need to use the substring keyword on your field in the CASE.

I have used this on V5R2.


On 5/29/07, Mike wrote:

Doesn't seem to work. Is this supposed to work on V5R3?

On 5/29/07, Gene Burns wrote:

You can use the CASE keyword in the order by clause

ORDER BY CASE fieldname
WHEN 'Filters' THEN 1
ELSE 2
END


Gene

On 5/29/07, Mike wrote:

I am having a hard time getting a custom sort working. The user

wants

"Filters" listed first then everything else after that (they don't

care

about order, but I figured listed as alpha). This is break logic on

part

type. So here is the SQL statement I am trying

SELECT RVEHNO, RVEHKY, RPARTC, CBDESC, RPARTN, AMFGCD, ADESC, ABIN
FROM QMFILES.GA560AP, QMFILES.GA600AP, QMFILES.GA122AP
WHERE RPKEY = AKEY AND
RPARTC = CBCODE AND
RVEHKY IN
(SELECT DISTINCT MAX(RVEHKY) AS RVEHKY
FROM QMFILES.GA560AP
GROUP BY RVEHNO) AND
RVEHNO = 2 AND RPARTC = 2
UNION
SELECT RVEHNO, RVEHKY, RPARTC, CBDESC, RPARTN, AMFGCD, ADESC, ABIN

FROM

(

SELECT RVEHNO, RVEHKY, RPARTC, CBDESC, RPARTN, AMFGCD, ADESC, ABIN
FROM QMFILES.GA560AP, QMFILES.GA600AP, QMFILES.GA122AP
WHERE RPKEY = AKEY AND
RPARTC = CBCODE AND
RVEHKY IN
(SELECT DISTINCT MAX(RVEHKY) AS RVEHKY
FROM QMFILES.GA560AP
GROUP BY RVEHNO) AND
RVEHNO = 2 AND
RPARTC <> 2
ORDER BY CBDESC, RPARTC, RPARTN) AS TABLE

I was hoping that the union work work which it does, but the other

items

are
unsorted. Ideas?

--
Mike Wills