Order by clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Order by clause

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-31 : 14:21:47

Why am I getting this error? (It's the bold section below)

Msg 104, Level 16, State 1, Line 2
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.



SELECT ''   AS reg, n.RegionAcronym, '<a href=Region.aspx?Reg=' + n.Regionacronym + '>' + 'PHI' + '</a>' as area,
(sum(isnull(t.[PendInFO],0)) + sum(isnull(a.[PendInPSC],0)) + sum(isnull(t.[pendindds],0))) as [Totpenfodds], 		
       SUM(isnull([PendinFo],0))       AS 'PendinFo', 
       SUM(isnull([PendInDDS],0))      AS 'PendInDDS',
       SUM(isnull(a.[PendInPSC],0))       AS 'PendInPSC', 
       SUM(isnull([PendInFo300],0))  AS 'PendInFo300',
       SUM(isnull([PendInDDS300],0))      AS 'PendInDDS300',        
		SUM(isnull([TotPendFoDDS300],0)) AS 'TotPendFoDDS300',
		SUM(isnull([TotPendFoDDS350],0))  AS 'TotPendFoDDS350',
		SUM(isnull([TotPendFoDDS399],0)) AS 'TotPendFoDDS399',
		SUM(isnull([TotPendFoDDS400],0)) AS 'TotPendFoDDS400'
FROM   T2Report t
left join pscreport a on a.doc = t.doc
left join natdocfile n on n.doc = t.doc
where n.reg='c'
group by n.reg, n.RegionAcronym
UNION 
select '' as reg, n.RegionAcronym,   '<a href=areadistlisting.aspx?reg=' + n.reg + '&Area=' + n.Area + '>' +'Area ' + right(n.area,1) + '</a>' as Dist,
(sum(isnull(t.[PendInFO],0)) + sum(isnull(a.[PendInPSC],0)) + sum(isnull(t.[pendindds],0))) as [Totpenfodds], 		
       SUM(isnull([PendinFo],0))       AS 'PendinFo', 
       SUM(isnull([PendInDDS],0))      AS 'PendInDDS',
       SUM(isnull(a.[PendInPSC],0))       AS 'PendInPSC', 
       SUM(isnull([PendInFo300],0))  AS 'PendInFo300',
       SUM(isnull([PendInDDS300],0))      AS 'PendInDDS300',        
		SUM(isnull([TotPendFoDDS300],0)) AS 'TotPendFoDDS300',
		SUM(isnull([TotPendFoDDS350],0))  AS 'TotPendFoDDS350',
		SUM(isnull([TotPendFoDDS399],0)) AS 'TotPendFoDDS399',
		SUM(isnull([TotPendFoDDS400],0)) AS 'TotPendFoDDS400'

FROM  T2Report t
left join pscreport a on a.doc = t.doc
left join natdocfile n on n.doc = t.doc
where n.reg='c'and  n.area='04'
group by n.reg, n.RegionAcronym, n.area
order by n.area desc

What am I missing?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-31 : 14:34:47

I got this to work


SELECT ''   AS reg, n.RegionAcronym, '<a href=Region.aspx?Reg=' + n.Regionacronym + '>' + 'PHI' + '</a>' as area,
(sum(isnull(t.[PendInFO],0)) + sum(isnull(a.[PendInPSC],0)) + sum(isnull(t.[pendindds],0))) as [Totpenfodds], 		
       SUM(isnull([PendinFo],0))       AS 'PendinFo', 
       SUM(isnull([PendInDDS],0))      AS 'PendInDDS',
       SUM(isnull(a.[PendInPSC],0))       AS 'PendInPSC', 
       SUM(isnull([PendInFo300],0))  AS 'PendInFo300',
       SUM(isnull([PendInDDS300],0))      AS 'PendInDDS300',        
		SUM(isnull([TotPendFoDDS300],0)) AS 'TotPendFoDDS300',
		SUM(isnull([TotPendFoDDS350],0))  AS 'TotPendFoDDS350',
		SUM(isnull([TotPendFoDDS399],0)) AS 'TotPendFoDDS399',
		SUM(isnull([TotPendFoDDS400],0)) AS 'TotPendFoDDS400'
FROM   T2Report t
left join pscreport a on a.doc = t.doc
left join natdocfile n on n.doc = t.doc
where n.reg='c'
group by n.reg, n.RegionAcronym
UNION 
select '' as reg, n.RegionAcronym,   '<a href=areadistlisting.aspx?reg=' + n.reg + '&Area=' + n.Area + '>' +'Area ' + right(n.area,1) + '</a>' as Dist,

(sum(isnull(t.[PendInFO],0)) + sum(isnull(a.[PendInPSC],0)) + sum(isnull(t.[pendindds],0))) as [Totpenfodds], 		
       SUM(isnull([PendinFo],0))       AS 'PendinFo', 
       SUM(isnull([PendInDDS],0))      AS 'PendInDDS',
       SUM(isnull(a.[PendInPSC],0))       AS 'PendInPSC', 
       SUM(isnull([PendInFo300],0))  AS 'PendInFo300',
       SUM(isnull([PendInDDS300],0))      AS 'PendInDDS300',        
		SUM(isnull([TotPendFoDDS300],0)) AS 'TotPendFoDDS300',
		SUM(isnull([TotPendFoDDS350],0))  AS 'TotPendFoDDS350',
		SUM(isnull([TotPendFoDDS399],0)) AS 'TotPendFoDDS399',
		SUM(isnull([TotPendFoDDS400],0)) AS 'TotPendFoDDS400'

FROM  T2Report t
left join pscreport a on a.doc = t.doc
left join natdocfile n on n.doc = t.doc
where n.reg='c'and  n.area='04'
group by n.reg, n.RegionAcronym, n.area

but...I want to change the order around I want Area to show up in desc order. I tried this but can't get it to work:

order by
CASE @OrderBy WHEN 'areadesc' THEN area END desc

I got this error message:

Must declare the scalar variable "@OrderBy".

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-31 : 18:05:11

Just what it says. @OrderBy is a variable and it's not defined.

try
declare @OrderBy varchar(20) = 'areadesc'
before the select statement

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 04:51:18

quote:

Originally posted by JJ297

I got this to work


SELECT ''   AS reg, n.RegionAcronym, '<a href=Region.aspx?Reg=' + n.Regionacronym + '>' + 'PHI' + '</a>' as area,
(sum(isnull(t.[PendInFO],0)) + sum(isnull(a.[PendInPSC],0)) + sum(isnull(t.[pendindds],0))) as [Totpenfodds], 		
       SUM(isnull([PendinFo],0))       AS 'PendinFo', 
       SUM(isnull([PendInDDS],0))      AS 'PendInDDS',
       SUM(isnull(a.[PendInPSC],0))       AS 'PendInPSC', 
       SUM(isnull([PendInFo300],0))  AS 'PendInFo300',
       SUM(isnull([PendInDDS300],0))      AS 'PendInDDS300',        
		SUM(isnull([TotPendFoDDS300],0)) AS 'TotPendFoDDS300',
		SUM(isnull([TotPendFoDDS350],0))  AS 'TotPendFoDDS350',
		SUM(isnull([TotPendFoDDS399],0)) AS 'TotPendFoDDS399',
		SUM(isnull([TotPendFoDDS400],0)) AS 'TotPendFoDDS400'
FROM   T2Report t
left join pscreport a on a.doc = t.doc
left join natdocfile n on n.doc = t.doc
where n.reg='c'
group by n.reg, n.RegionAcronym
UNION 
select '' as reg, n.RegionAcronym,   '<a href=areadistlisting.aspx?reg=' + n.reg + '&Area=' + n.Area + '>' +'Area ' + right(n.area,1) + '</a>' as Dist,

(sum(isnull(t.[PendInFO],0)) + sum(isnull(a.[PendInPSC],0)) + sum(isnull(t.[pendindds],0))) as [Totpenfodds], 		
       SUM(isnull([PendinFo],0))       AS 'PendinFo', 
       SUM(isnull([PendInDDS],0))      AS 'PendInDDS',
       SUM(isnull(a.[PendInPSC],0))       AS 'PendInPSC', 
       SUM(isnull([PendInFo300],0))  AS 'PendInFo300',
       SUM(isnull([PendInDDS300],0))      AS 'PendInDDS300',        
		SUM(isnull([TotPendFoDDS300],0)) AS 'TotPendFoDDS300',
		SUM(isnull([TotPendFoDDS350],0))  AS 'TotPendFoDDS350',
		SUM(isnull([TotPendFoDDS399],0)) AS 'TotPendFoDDS399',
		SUM(isnull([TotPendFoDDS400],0)) AS 'TotPendFoDDS400'

FROM  T2Report t
left join pscreport a on a.doc = t.doc
left join natdocfile n on n.doc = t.doc
where n.reg='c'and  n.area='04'
group by n.reg, n.RegionAcronym, n.area

will it be passed as a parameter in your code or is it set via local variable? Any case, you should be declaring it before using

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-11-01 : 08:19:06

set via local variable

I'm now getting this error message:

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 42
Must declare the scalar variable "@OrderBy".


@Orderby varchar(20) = 'areadesc'

SELECT ''   AS reg, n.RegionAcronym, '<a href=RegionT2.aspx?Reg=' + n.Regionacronym + '>' + 'PHI' + '</a>' as area,
(sum(isnull(t.[PendInFO],0)) + sum(isnull(a.[PendInPSC],0)) + sum(isnull(t.[pendindds],0))) as [Totpenfodds], 		
--SUM(Totpenfodds)      AS 'Totpenfodds',
       SUM(isnull([PendinFo],0))       AS 'PendinFo', 
       SUM(isnull([PendInDDS],0))      AS 'PendInDDS',
       SUM(isnull(a.[PendInPSC],0))       AS 'PendInPSC', 
       SUM(isnull([PendInFo300],0))  AS 'PendInFo300',
       SUM(isnull([PendInDDS300],0))      AS 'PendInDDS300',        
		SUM(isnull([TotPendFoDDS300],0)) AS 'TotPendFoDDS300',
		SUM(isnull([TotPendFoDDS350],0))  AS 'TotPendFoDDS350',
		SUM(isnull([TotPendFoDDS399],0)) AS 'TotPendFoDDS399',
		SUM(isnull([TotPendFoDDS400],0)) AS 'TotPendFoDDS400'
FROM   T2Report t
left join pscreport a on a.doc = t.doc
left join natdocfile n on n.doc = t.doc
where n.reg='c'
group by n.reg, n.regionacronym

UNION 

select ''  reg, n.RegionAcronym,   '<a href=areadistlistingT2.aspx?reg=' + n.reg + '&Area=' + n.Area + '>' +'Area ' + right(n.area,1) + '</a>' as Dist,
(sum(isnull(t.[PendInFO],0)) + sum(isnull(a.[PendInPSC],0)) + sum(isnull(t.[pendindds],0))) as [Totpenfodds], 		
       SUM(isnull([PendinFo],0))       AS 'PendinFo', 
       SUM(isnull([PendInDDS],0))      AS 'PendInDDS',
       SUM(isnull(a.[PendInPSC],0))       AS 'PendInPSC', 
       SUM(isnull([PendInFo300],0))  AS 'PendInFo300',
       SUM(isnull([PendInDDS300],0))      AS 'PendInDDS300',        
		SUM(isnull([TotPendFoDDS300],0)) AS 'TotPendFoDDS300',
		SUM(isnull([TotPendFoDDS350],0))  AS 'TotPendFoDDS350',
		SUM(isnull([TotPendFoDDS399],0)) AS 'TotPendFoDDS399',
		SUM(isnull([TotPendFoDDS400],0)) AS 'TotPendFoDDS400'
FROM  T2Report t
left join pscreport a on a.doc = t.doc
left join natdocfile n on n.doc = t.doc
where n.reg='c'and  n.area='4'--@area 
GROUP  BY n.reg, n.regionacronym, n.area
order by 
CASE @OrderBy WHEN 'areadesc' THEN area END desc

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-11-01 : 08:26:58

This is what my code brings up now (without the case statement)


Reg      RegionAcronym     Area      Totpenfodds    Pendinfo
              PHI          Area04       56897        456
              PHI          PHI          145234       54896

I want PHI to come up first over top of Area04 like this:


Reg      RegionAcronym     Area      Totpenfodds    Pendinfo
               PHI          PHI          145234       54896 
               PHI          Area04       56897        456

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-11-01 : 09:02:09

never mind this worked when I took the alias out in front of n.area

order by area desc

I still would like to learn the other way you two propose if you don't mind.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-01 : 09:32:57

An order by works on the resultset.
Usually it can resolve the column from the underlying data and order by that (whether or not that value is in the resultset) - but if there is a union or group by it can't and only the resultset is available. That means the column names from the resultset have to be used.

Apart from that you can do any calculation you like in the order by (apart from bugs).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-11-01 : 09:49:31

Okay thanks I got it that's why n.area wasn't working. Thanks for the explanation!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources