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!
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Jim Horn

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Starr Duskk

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jim Horn

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23