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


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
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: 153
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: 153
Taking the alias outside did the trick. This works:

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3433
    
  47

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: 153
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
 
Similar Threads
aggregate function using alias name and fetch the result into the resultset using the Alias name
MySQL Replace and DUPLICATE KEY not giving sum of column value
How to use where with between clause
A little SQL help with SUM()
[Oracle] CASE Syntax in a SELECT