Microsoft query in Excel and Case statement

It is frustrating that I need to pull the data from SQL server using complex queries and then format it in Excel to ”prettify” the data. Thankfully, I can use the Microsoft query in Excel to get the data from SQL server and create a table or a PivotTable. Today, I encountered a problem with the case statement.

Although my syntax was right, MS Query kept throwing exception about the CASE statement. I read some discussions on-line, and some people had the wrong syntax, such as including the alias of the case statement in the group by statement. I did not have that problem. Finally, I found the solution: It was the order by clause. I was using the alias of the case statement in the order by clause. I removed it and the query ran just fine.

About the Author

A co-author of Data Science for Fundraising, an award winning keynote speaker, Ashutosh R. Nandeshwar is one of the few analytics professionals in the higher education industry who has developed analytical solutions for all stages of the student life cycle (from recruitment to giving). He enjoys speaking about the power of data, as well as ranting about data professionals who chase after “interesting” things. He earned his PhD/MS from West Virginia University and his BEng from Nagpur University, all in industrial engineering. Currently, he is leading the data science, reporting, and prospect development efforts at the University of Southern California.