• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Alias in select when using case statement

 
Ahsan Bagwan
Ranch Hand
Posts: 254
1
Java MySQL Database Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am getting nulls in my second column in my MySQL query. I want to transform the nulls to 0. So I decided to use case statement in select clause to disallow null in favour of 0's.

Here is the select clause from the query where I try to use case but I keep messing up the alias by using it incorrectly. How do I avoid the MySQL error while doing this?



 
Ahsan Bagwan
Ranch Hand
Posts: 254
1
Java MySQL Database Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Taking the alias outside did the trick. This works:

 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, the case expression ends with the end keyword. The alias must go after it.

Databases usually have a function that allows to convert nulls to some value. In Oracle, for example, that would be NVL, and you could write:

Or, if you want to stick to ANSI syntax, you could convert nulls to zero before putting it to the aggregation function:
I don't expect any performance differences here. These alternatives might just make the expression more readable.
 
Ahsan Bagwan
Ranch Hand
Posts: 254
1
Java MySQL Database Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Wonderful, thanks! Helpful to know that it is the ANSI syntax that keeps the SQL portable across the DBMSes. I tend to be very shortsighted in that regard.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic