wood burning stoves 2.0*
The moose likes JDBC and the fly likes Can I Select column A OR column B ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Can I Select column A OR column B ?" Watch "Can I Select column A OR column B ?" New topic
Author

Can I Select column A OR column B ?

Kim Kantola
Ranch Hand

Joined: May 17, 2001
Posts: 274
Hi All,
Just wondering if this is a possibility. I need to do a pretty complicated select statement, and one of the things I need to do is select a time value. The problem I have is, I need to use the time value in Column A, only if the value in Column B is null, if the value in Column B is not null, I need to use its value. I can't just select both values and then determine what to use AFTER the query, because I also need to sort on this value.
I have never done this sort of thing and wonder if it is doable in SQL or in a stored procedure.
In psuedo code, here is what I want to do:

Select x, y, z, (A as duration if B isNull OR B as duration if B is not null) from myTable ORDER BY duration;

then hoping I get a result set with several rows like this, ordered by the duration value:

xval, yval, zval, duration (where the value of duration could have come from either column A or column B).

Any tips?
Thanks.
Deva Sagar
Greenhorn

Joined: May 21, 2007
Posts: 7
You can do this usually with a built-in function specific to your RDBMS (e.g.) Oracle has a decode function and i believe SQL Server has one called "iif".

For the Oracle decode function, you can pass in parameters of the following form:

decode(Expression1, Value1, WhatToSelectIfExpression1EqualsValue1,
[Expression2, Value2, WhatToSelectIfExpression2EqualsValue2,....]
WhatToSelectIfNoneOfTheConditionsAreTrue)

So for your example, to "compare B to null, if true select A else select B" it would be something like:

Select x, y, z, decode(B,null,A,B) from myTable
Kim Kantola
Ranch Hand

Joined: May 17, 2001
Posts: 274
Awesome, it worked!
I am using mySQL , and the method I had to use for that was IFNULL(a,b).

So, if column a is null, the value of column b is returned, if column a is not null, that value is returned. EXCELLENT!

Thank you thank you!
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 860
For anyone that uses Sybase look into the case command. It can be very useful.



other examples:
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/39235;pt=39091


http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

And now for Oracle - with NVL(),Regards, Jan
[ May 23, 2007: Message edited by: Jan Cumps ]

OCUP UML fundamental and ITIL foundation
youtube channel
 
Don't get me started about those stupid light bulbs.
 
subject: Can I Select column A OR column B ?
 
Similar Threads
Checking variable initialization...
how to pass a value from an applet to a html field
Inserting data using HQL on multiple tables.
Queries and "column not found" error
Oracle: using value of column in sub query