Attaching a column for the case statement results in sql query

Starr Duskk
Starr Duskk used Ask the Experts™
on
I would like to name my case statement values so I can display it as a column to see what's happening. Here is my code:

SELECT NavMenu.*  
 from NavMenu 
 left outer join navmenu as nm on navmenu.parentnavmenuid = nm.navmenuid 
 WHERE 
 NavMenu.NavMenuGroupCode IN (2609) 
 AND 
 NavMenu.NavMenuName  LIKE '%test%'  

 order by navmenu.navmenugroupcode desc,  
 case when navmenu.isparentnavmenu = 1 then navmenu.listorder 
 Else nm.listorder 
 END ASC, 
 case when navmenu.isparentnavmenu = 1 then 0 
 else 1 
 END ASC,
  NavMenu.ListOrder

Open in new window



I have another query that uses:
End as 'GlobalOrder'

Open in new window


But when I try something like that with this, it doesn't work.

Help?

thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ASP.NET VB.NET Developer
Commented:
figured it out....



SELECT NavMenu.*,

sortvalue =
 case when navmenu.isparentnavmenu = 1 then navmenu.listorder
when navmenu.isparentnavmenu = 0 then 0
 else 1
 END
 
 from NavMenu
 left outer join navmenu as nm on navmenu.parentnavmenuid = nm.navmenuid

 WHERE
 NavMenu.NavMenuGroupCode IN (2609)
 AND
 NavMenu.NavMenuName  LIKE '%test%'  

 order by navmenu.navmenugroupcode desc,  
 sortvalue,
  NavMenu.ListOrder
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Not abundantly sure why but CASE blocks are not best practice in WHERE clauses, the recommendation is to use logic like AND and OR and prentheses ( ) to handle filtering.

Also I don't see 'GlobalOrder' in your posted solution, so hard to see how that worked for you.  
Also as long as the name does not include spaces or punctuation marks other than underscore, the single quotes ' ' are not needed.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial