Union and Order by Case Problem 1

[RipH]

I have a stored procedure that unions four select statements together. It works as expected. The problem is that I want to also be able to do a dynamic order by using the case statement. When I check the syntax on the stored procedure it get the following error: ERROR 104: ORDER BY items must appear in the select list if the statement contains a UNION operator. My web app passes the parameter sortfield to the query. Any and all suggestions greatly appreciated. Thanks.

CREATE PROCEDURE CRITERIA_DATABIND_SORT
(
@sortfield varchar(50)
)
AS
SELECT c.audit_report_criteria_id, c.region_id, c.capability_id, c.calculation_name, c.calculation_name_alias,
c.calculation_high_bound, c.calculation_low_bound, c.sort_order, c.active_flag, r.region_name AS region_name,
(p.capability_name + ', ' + p.capability_type) AS capability_name, c.tower_id, t.tower_name AS tower_name
FROM AFT_AUDIT_REPORT_CRITERIA c, REGION r, CAPABILITY p, TOWER t
WHERE c.region_id = r.region_id AND c.capability_id = p.capability_id AND c.tower_id = t.tower_id AND c.active_flag = 'y'

UNION ALL

SELECT c.audit_report_criteria_id, c.region_id, c.capability_id, c.calculation_name, c.calculation_name_alias,
c.calculation_high_bound, c.calculation_low_bound, c.sort_order, c.active_flag, r.region_name AS region_name,
('--- None ---') AS capability_name, c.tower_id, t.tower_name AS tower_name
FROM AFT_AUDIT_REPORT_CRITERIA c, REGION r, TOWER t
WHERE c.region_id = r.region_id AND c.capability_id = 0 AND c.tower_id = t.tower_id AND c.active_flag = 'y'

UNION ALL

SELECT c.audit_report_criteria_id, c.region_id, c.capability_id, c.calculation_name, c.calculation_name_alias,
c.calculation_high_bound, c.calculation_low_bound, c.sort_order, c.active_flag, r.region_name AS region_name,
(p.capability_name + ', ' + p.capability_type) AS capability_name, c.tower_id, ('--- None ---') AS tower_name
FROM AFT_AUDIT_REPORT_CRITERIA c, REGION r, CAPABILITY p
WHERE c.region_id = r.region_id AND c.capability_id = p.capability_id AND c.tower_id = 0 AND c.active_flag = 'y'

UNION ALL

SELECT c.audit_report_criteria_id, c.region_id, c.capability_id, c.calculation_name, c.calculation_name_alias,
c.calculation_high_bound, c.calculation_low_bound, c.sort_order, c.active_flag, r.region_name AS region_name,
('--- None ---') AS capability_name, c.tower_id, ('--- None ---') AS tower_name
FROM AFT_AUDIT_REPORT_CRITERIA c, REGION r
WHERE c.region_id = r.region_id AND c.capability_id = 0 AND c.tower_id = 0 AND c.active_flag = 'y'

ORDER BY
CASE WHEN @sortfield='calculation_name' THEN calculation_name
WHEN @sortfield='calculation_name_alias' THEN calculation_name_alias
WHEN @sortfield='region_name' THEN region_name
WHEN @sortfield='capability_name' THEN capability_name
WHEN @sortfield='tower_name' THEN tower_name
WHEN @sortfield='calculation_high_bound' THEN CONVERT(varchar(50), calculation_high_bound)
WHEN @sortfield='calculation_low_bound' THEN CONVERT(varchar(50), calculation_low_bound)
WHEN @sortfield='sort_order' THEN CONVERT(varchar(50), sort_order)
END
GO


Rip Henry
Lewisberry, PA USA

 

[cmmrfrds]

I don't believe you can use a dervied name at the same level it is created.

try leaving this off to see if it works.
(p.capability_name + ', ' + p.capability_type) AS capability_name

if so, try putting the calculation in the case statement.

 

[bboffin]

I think that you can't use CASE in this situation because CASE returns the VALUE of the named column not a reference to the column. I have only ever succeeded in using CASE when returning values AS a newly named column in a SELECT statement.

You could use dynamic SQL within the SP where the SELECT statements are built up as literal strings in one or more variables.

Finally you EXEC the concatenation of these strings adding the ORDER BY clause:

EXEC (@SELECT1 + ' union ' + @SELECT2 + ' union ' + @SELECT3 + ' union ' + @SELECT4 + ' ORDER BY ' + @sortfield)

This technique can also be used to build complex WHERE clauses based on the user selecting values for different columns.

Surprisingly there appears to be little impact on performance.



Bob Boffin

 

[RipH]

Bob,
Thanks for the suggestion on using dynamic SQL. After a bit of playing with delimiters, I got the sp to work just the way I wanted. The final result is as follows:
CREATE PROCEDURE CRITERIA_DATABIND_SORT
(
@sortfield varchar(50)
)
AS
Declare @SQLselect varchar(5000)

Select @SQLselect = 'SELECT c.audit_report_criteria_id, c.region_id, c.capability_id, c.calculation_name, c.calculation_name_alias,
c.calculation_high_bound, c.calculation_low_bound, c.sort_order, c.active_flag, r.region_name AS region_name,
(p.capability_name + '','' + p.capability_type) AS capability_name, c.tower_id, t.tower_name AS tower_name
FROM AFT_AUDIT_REPORT_CRITERIA c, REGION r, CAPABILITY p, TOWER t
WHERE c.region_id = r.region_id AND c.capability_id = p.capability_id AND c.tower_id = t.tower_id AND c.active_flag = ''y''
UNION ALL '

Select @SQLselect = @SQLselect + 'SELECT c.audit_report_criteria_id, c.region_id, c.capability_id, c.calculation_name, c.calculation_name_alias,
c.calculation_high_bound, c.calculation_low_bound, c.sort_order, c.active_flag, r.region_name AS region_name,
(''--- None ---'') AS capability_name, c.tower_id, t.tower_name AS tower_name
FROM AFT_AUDIT_REPORT_CRITERIA c, REGION r, TOWER t
WHERE c.region_id = r.region_id AND c.capability_id = 0 AND c.tower_id = t.tower_id AND c.active_flag = ''y''
UNION ALL '

Select @SQLselect = @SQLselect + 'SELECT c.audit_report_criteria_id, c.region_id, c.capability_id, c.calculation_name, c.calculation_name_alias,
c.calculation_high_bound, c.calculation_low_bound, c.sort_order, c.active_flag, r.region_name AS region_name,
(p.capability_name + '', '' + p.capability_type) AS capability_name, c.tower_id, (''--- None ---'') AS tower_name
FROM AFT_AUDIT_REPORT_CRITERIA c, REGION r, CAPABILITY p
WHERE c.region_id = r.region_id AND c.capability_id = p.capability_id AND c.tower_id = 0 AND c.active_flag = ''y''
UNION ALL '

Select @SQLselect = @SQLselect + 'SELECT c.audit_report_criteria_id, c.region_id, c.capability_id, c.calculation_name, c.calculation_name_alias,
c.calculation_high_bound, c.calculation_low_bound, c.sort_order, c.active_flag, r.region_name AS region_name,
(''--- None ---'') AS capability_name, c.tower_id, (''--- None ---'') AS tower_name
FROM AFT_AUDIT_REPORT_CRITERIA c, REGION r
WHERE c.region_id = r.region_id AND c.capability_id = 0 AND c.tower_id = 0 AND c.active_flag = ''y'''


EXEC(@SQLselect + ' Order By ' + @sortfield)
GO


Bob you get a STAR.

Rip Henry
Lewisberry, PA USA

 

[hmckillop]

Alternatively wrap your SQL in another select statement and thus having only one set of columns i.e.

CREATE PROCEDURE CRITERIA_DATABIND_SORT
(
@sortfield varchar(50)
)
AS
SELECT * FROM (
SELECT c.audit_report_criteria_id, c.region_id, c.capability_id, c.calculation_name, c.calculation_name_alias, 
c.calculation_high_bound, c.calculation_low_bound, c.sort_order, c.active_flag, r.region_name AS region_name, 
(p.capability_name + ', ' + p.capability_type) AS capability_name, c.tower_id, t.tower_name AS tower_name
FROM AFT_AUDIT_REPORT_CRITERIA c, REGION r, CAPABILITY p, TOWER t 
WHERE c.region_id = r.region_id AND c.capability_id = p.capability_id AND c.tower_id = t.tower_id  AND c.active_flag = 'y'

UNION ALL

SELECT c.audit_report_criteria_id, c.region_id, c.capability_id, c.calculation_name, c.calculation_name_alias, 
c.calculation_high_bound, c.calculation_low_bound, c.sort_order, c.active_flag, r.region_name AS region_name, 
('--- None ---') AS capability_name, c.tower_id, t.tower_name AS tower_name
FROM AFT_AUDIT_REPORT_CRITERIA c, REGION r, TOWER t 
WHERE c.region_id = r.region_id AND c.capability_id = 0 AND c.tower_id = t.tower_id  AND c.active_flag = 'y'

UNION ALL

SELECT c.audit_report_criteria_id, c.region_id, c.capability_id, c.calculation_name, c.calculation_name_alias, 
c.calculation_high_bound, c.calculation_low_bound, c.sort_order, c.active_flag, r.region_name AS region_name, 
(p.capability_name + ', ' + p.capability_type) AS capability_name, c.tower_id, ('--- None ---') AS tower_name
FROM AFT_AUDIT_REPORT_CRITERIA c, REGION r, CAPABILITY p 
WHERE c.region_id = r.region_id AND c.capability_id = p.capability_id AND c.tower_id = 0  AND c.active_flag = 'y'

UNION ALL

SELECT c.audit_report_criteria_id, c.region_id, c.capability_id, c.calculation_name, c.calculation_name_alias, 
c.calculation_high_bound, c.calculation_low_bound, c.sort_order, c.active_flag, r.region_name AS region_name, 
('--- None ---') AS capability_name, c.tower_id, ('--- None ---') AS tower_name
FROM AFT_AUDIT_REPORT_CRITERIA c, REGION r 
WHERE c.region_id = r.region_id AND c.capability_id = 0 AND c.tower_id = 0  AND c.active_flag = 'y'
) tmp
ORDER BY 
CASE   WHEN @sortfield='calculation_name' THEN  calculation_name
       WHEN @sortfield='calculation_name_alias' THEN calculation_name_alias
       WHEN @sortfield='region_name' THEN region_name
       WHEN @sortfield='capability_name' THEN capability_name
       WHEN @sortfield='tower_name' THEN tower_name
       WHEN @sortfield='calculation_high_bound' THEN CONVERT(varchar(50), calculation_high_bound)
       WHEN @sortfield='calculation_low_bound' THEN CONVERT(varchar(50), calculation_low_bound)
       WHEN @sortfield='sort_order' THEN CONVERT(varchar(50), sort_order)
  END
GO

and as long as the return parameters are all named, this should do the job equally as well.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[RipH]

hmckillop,
Thanks for the alternative solution.

Rip Henry
Lewisberry, PA USA