g.britton (12/19/2014)
Try adding an ORDER BY clause at the end like this:
ORDER By CASE EDI_PARTNER
WHEN 'INTTRA' THEN 1
WHEN 'GTNEXUS' THEN 2
...
WHEN 'EXPEDITORS' THEN 9
ELSE 10
END
, EDI_PARTNER_CD
Idea is, you have a two-tiered ORDER BY. The first tier will sort any of the entries in the reserved list first, followed by the rest sorted normally.
Thanks.
I tried to implement your code as below but getting the following error -
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
I think this is due to the UNION operator.
SELECT DISTINCT EDI_PARTNER_CD
FROM MG_EDI_PARTNER
UNION
SELECT '(NULL)', EDI_PARTNER_CD
FROM MG_EDI_PARTNER AS MG_EDI_PARTNER_1
WHERE (DELETED_FLG = 'N')
ORDER BY CASE EDI_PARTNER_CD WHEN 'INTTRA' THEN 1 WHEN 'GTNEXUS' THEN 2 WHEN 'DAKOSY' THEN 3 WHEN 'DAMCO' THEN 4 WHEN 'DBH' THEN 5 WHEN 'BMW_CONT'
THEN 6 WHEN 'CATERPILLAR' THEN 7 WHEN 'CARGOSMART' THEN 8 WHEN 'EXPEDITORS' THEN 9 ELSE 10 END, EDI_PARTNER_CD
Do you know how this can be resolved ?
Thanks.