SQL Select from case or IF...Then logic?

Question

I'm trying to eliminate making a post pass into the db and in doing so have encountered this problem.

The scenario is that I need to join onto a table that has many rows per item. Think dated values, Item x has a value of n at date y. There's also a row for the same item at date x and z.

I have no influence or power to change this table.

What I'm trying to do is something along the lines of this:

Select 
table1.Name as name, 
table1.date as date,

if (date > x)
   select table2.value as value,
   table2.othervalue as otehrvalue
   from table 2
   where table1.x = table2.x

from table1

but I'm running into difficulties. I've successfully run the test conditions with strings so if date > x display a string and so on but I can't seem to get the select to work(if possible) and I'm not sure where I'm going wrong. I've looked online and not found much to go on which leads me to think I could be barking up the wrong tree...

We're using Advantage DB.

Any help appreciated

Edited as I'd liek to return more than one value from the sub query...

Are you using MySQL and/or SQL Server? (Don't tag product not involved.) — jarlh, Mar 20 '15 at 12:40
@AbhikChakraborty - Hi, the Date is a range, table has an effective to date and an effectiveFrom date. :) — null, Mar 20 '15 at 13:19

score 3 · Answer 1 · answered Mar 20 '15 at 12:42

3

Use a CASE expression:

select table1.Name as name,
       table1.date as date,
       case when date > x then
           (select table2.value
            from table2
            where table1.x = table2.x)
       end as value
from table1

Will return NULL when date <= x.

Note, if the sub-select returns more than one row you'll get an error!

answered Mar 20 '15 at 12:42

jarlh

42,561
8
45
63

thank you, If I want to return more than one field, is this still doable? so select table2.x, table2.y etc. I'm returning an error saying subquery returning more than one column which of course is correct but wondering if there's a workaround? – null Mar 20 '15 at 13:18
1

If you want two (or more) columns, you can do a join, like in StuartLC's answer. If you are having more than one row in sub-query you can either do the join solution, or restrict the sub-query. (fetch max, or fetch limit etc.), depending on which result you want. – jarlh Mar 20 '15 at 13:21

StuartLC · Answer 2 · 2015-03-20T13:31:30.607

3

You can also use case...when with a left outer join:

Select 
  table1.Name as name, 
  table1.date as date,
  case when table1.date > x
     then table2.value
     else null
  end as value
from table1
  left join table2
  on table1.x = table2.x;

Edit, Re : Conditionally return multiple columns from table 2

Yes, you should be able to do this for multiple columns, by using the date > x as a join condition, and retaining the left outer join (this will again project NULL's for any failed joins), and finally using COALESCE to revert any NULLs back to the Table1 condition:

Select 
  table1.Name as name, 
  table1.date as date,
  COALESCE(table2.x, table1.x) as x,
  COALSECE(table2.y, table1.y) as y
from table1
  left join table2  
  on table1.x = table2.x AND table1.date > x;

edited Mar 20 '15 at 13:31

answered Mar 20 '15 at 12:43

StuartLC

104,537
17
209
285

thank you, If I want to return more than one field, is this still doable? so select table2.x, table2.y etc. I'm returning an error saying subquery returning more than one column which of course is correct but wondering if there's a workaround? – null Mar 20 '15 at 13:18
1

Yes, I believe you can do this as outlined in my edit. – StuartLC Mar 20 '15 at 13:31

SQL Select from case or IF...Then logic?

2 Answers2

Linked