File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases 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
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Can I Select column A OR column B ?" Watch "Can I Select column A OR column B ?" New topic

Can I Select column A OR column B ?

Kim Kantola
Ranch Hand

Joined: May 17, 2001
Posts: 276
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?
Deva Sagar

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,....]

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: 276
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: 862
For anyone that uses Sybase look into the case command. It can be very useful.

other examples:;pt=39091 - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Jan Cumps

Joined: Dec 20, 2006
Posts: 2565

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

OCUP UML fundamental and ITIL foundation
I agree. Here's the link:
subject: Can I Select column A OR column B ?
jQuery in Action, 3rd edition