Can I Select column A OR column B ?

[Kim Kantola]

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]

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]

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]

For anyone that uses Sybase look into the case command. It can be very useful.

  
// case version
SELECT STUDENT_ID,
       (CASE WHEN COURSE_ID = 101 THEN 1 ELSE 0 END) AS COURSE_101,
       (CASE WHEN COURSE_ID = 105 THEN 1 ELSE 0 END) AS COURSE_105,
       (CASE WHEN COURSE_ID = 201 THEN 1 ELSE 0 END) AS COURSE_201,
       (CASE WHEN COURSE_ID = 210 THEN 1 ELSE 0 END) AS COURSE_210,
       (CASE WHEN COURSE_ID = 300 THEN 1 ELSE 0 END) AS COURSE_300
GROUP BY STUDENT_ID
ORDER BY STUDENT_ID
 
//noncase version
SELECT STUDENT_ID,
    (1- ABS( SIGN( ISNULL( 101 - COURSE_ID, 1)))) COURSE_101,
    (1- ABS( SIGN( ISNULL( 105 - COURSE_ID, 1)))) COURSE_105,
    (1- ABS( SIGN( ISNULL( 201 - COURSE_ID, 1)))) COURSE_201,
    (1- ABS( SIGN( ISNULL( 210 - COURSE_ID, 1)))) COURSE_210,
    (1- ABS( SIGN( ISNULL( 300 - COURSE_ID, 1)))) COURSE_300
GROUP BY STUDENT_ID
ORDER BY STUDENT_ID 
 
  

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

[Jan Cumps]

And now for Oracle - with NVL(),

SELECT STUDENT_ID,
       (decode COURSE_ID, 101, 1, 0) AS COURSE_101, ...
 
SELECT STUDENT_ID,
    ...nvl( 101 - COURSE_ID, 1)...

Regards, Jan
[ May 23, 2007: Message edited by: Jan Cumps ]