advantage database server - 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...

Answer 1

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!