File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes SQL syntax for selecting column A or B based on column C Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL syntax for selecting column A or B based on column C" Watch "SQL syntax for selecting column A or B based on column C" New topic
Author

SQL syntax for selecting column A or B based on column C

Thomas Kennedy
Ranch Hand

Joined: Jan 20, 2008
Posts: 137
I have this object called searchResults which is essentially a wrapper for a SQL resultset. This Java method returns the value from column A, or B or C, depending on the value found in column DRUG_PRC_ACTION_CD.

Yes, the getter is taking an int argument. It's not my code.

I want to eliminate all this Java conditional processing and move this work back to the SQL query. In other words I want something like


so that the getter becomes something like

but I am not sure of the syntax. Can anyone show me what the SQL syntax is?

Costs matter. Justice lies in processes not outcomes. Crime is caused by criminals.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18993
    
    8

That looks like a reasonable approximation of working SQL code. Of course the details depend on your database, which may or may not support the "CASE" construct, or may support it in a different way than that. Did you try that? Did you have a problem with it?
Thomas Kennedy
Ranch Hand

Joined: Jan 20, 2008
Posts: 137
Here we are:

I will deal with sticking in the 'N/A' later, somehow.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18993
    
    8

Thomas Kennedy wrote:I will deal with sticking in the 'N/A' later, somehow.


It's going to be hard to stick that string into a column which appears to be declared as a numeric type. NULL would be more practical from the SQL point of view, because you can get a null into a numeric column, but probably nulls would wreak havoc with that wrapper code you have there.
Thomas Kennedy
Ranch Hand

Joined: Jan 20, 2008
Posts: 137
Right. Much later, there's a wrapper, inside the view, that converts all this stuff to Strings. That's probably where I will handle any necessary fiddling. I'm in the process of figuring out how to replace all the scriptlets with JSTL and EL, and where possible I'm pushing the work back into DB2. This is one of the exceptions.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL syntax for selecting column A or B based on column C