December 19, 2014 at 8:21 am
Hi,
I have a requirement to display the parameter values in a particular order.
The folowing values (EDI_PARTNER_CD) should be displayed in the dropdown list first and then the remaining values should be sorted in ascending order:
---------------------------
INTTRA
GTNEXUS
DAKOSY
DAMCO
DBH
BMW_CONT
CATERPILLAR
CARGOSMART
EXPEDITORS
--------------------------
I need to amend the existing sql code which is used for displaying the values to incorporate the above requirement:
SELECT DISTINCT EDI_PARTNER_CD
FROM MG_EDI_PARTNER
UNION
SELECT '(NULL)' AS Expr1
FROM MG_EDI_PARTNER AS MG_EDI_PARTNER_1
WHERE (DELETED_FLG = 'N')
Any suggestions would be deeply appreciated.
Thanks.
December 19, 2014 at 12:46 pm
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.
Gerald Britton, Pluralsight courses
December 22, 2014 at 3:18 am
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.
December 22, 2014 at 12:22 pm
Just wrap it in a CTE:
; WITH cte AS
(
SELECT DISTINCT EDI_PARTNER_CD
FROM MG_EDI_PARTNER
UNION ALL
SELECT TOP 1 '(NULL)', EDI_PARTNER_CD
FROM MG_EDI_PARTNER AS MG_EDI_PARTNER_1
WHERE (DELETED_FLG = 'N')
)
SELECT EDI_PARTNER_CD
FROM cte
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
As a side note: I changed UNION to UNION ALL together with a TOP 1 in order to reduce overhead (UNION is required to remove the duplicates caused by the missing TOP 1).
December 22, 2014 at 3:52 pm
Do you have an underlying "master" table for these codes? If so, add a sort_sequence value to that table and sort based on that. Then you don't have to repeat it for every q -- and change it in all queries if (when) you have to modify the sort sequence.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 23, 2014 at 4:04 am
LutzM (12/22/2014)
Just wrap it in a CTE:
; WITH cte AS
(
SELECT DISTINCT EDI_PARTNER_CD
FROM MG_EDI_PARTNER
UNION ALL
SELECT TOP 1 '(NULL)', EDI_PARTNER_CD
FROM MG_EDI_PARTNER AS MG_EDI_PARTNER_1
WHERE (DELETED_FLG = 'N')
)
SELECT EDI_PARTNER_CD
FROM cte
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
As a side note: I changed UNION to UNION ALL together with a TOP 1 in order to reduce overhead (UNION is required to remove the duplicates caused by the missing TOP 1).
As usual Lutz, Bang on !! Thanks very much for your help !
December 23, 2014 at 4:49 am
ScottPletcher (12/22/2014)
Do you have an underlying "master" table for these codes? If so, add a sort_sequence value to that table and sort based on that. Then you don't have to repeat it for every q -- and change it in all queries if (when) you have to modify the sort sequence.
Thanks Scott. Will follow your advice accordingly.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply