aspose file tools*
The moose likes JDBC and the fly likes Alias in select when using case statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Alias in select when using case statement " Watch "Alias in select when using case statement " New topic
Author

Alias in select when using case statement

Ahsan Bagwan
Ranch Hand

Joined: Oct 05, 2010
Posts: 162
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

Joined: Oct 05, 2010
Posts: 162
Taking the alias outside did the trick. This works:

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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

Joined: Oct 05, 2010
Posts: 162
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Alias in select when using case statement