Link to home
Start Free TrialLog in
Avatar of Starr Duskk
Starr DuskkFlag for United States of America

asked on

Attaching a column for the case statement results in sql query

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!
ASKER CERTIFIED SOLUTION
Avatar of Starr Duskk
Starr Duskk
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jim Horn
Jim Horn
Flag of United States of America image
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.