We help IT Professionals succeed at work.

Attaching a column for the case statement results in sql query

58 Views
Last Modified: 2018-10-09
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

ASP.NET VB.NET Developer
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
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.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.