• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Can I Select column A OR column B ?

 
Kim Kantola
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Jan Cumps
Bartender
Posts: 2586
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And now for Oracle - with NVL(),Regards, Jan
[ May 23, 2007: Message edited by: Jan Cumps ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic