This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
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 OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA 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: 158
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: 158
Taking the alias outside did the trick. This works:

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  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: 158
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