Re: Defining a Custom Sort Order in SQL

[vhamberg@xxxxxxxxxxx]

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