Thread: CASE SELECT syntax

What about a SELECT syntax like that?

SELECT
    CASE WHEN bar.foo_id IS NULL THEN bar.*
    ELSE foo.*
FROM foo, bar

Anyway, I need a SELECT query that gets data from a FIRST TABLE if a
specific value in a SECOND TABLE is NULL or from SECOND TABLE itself if
the value IS NOT NULL.

Any suggestion?

Thank you!
--
Marco Lazzeri [ n o z e  S.r.l. ]
Via Giuntini, 25/29 - 56023 Navacchio - Cascina (PI)
Tel +39  (0)50 754380 - Fax +39 (0)50 754381
mailto:marcomail@noze.it - http://www.noze.it

On Thu, Jan 15, 2004 at 18:23:47 +0100,
  Marco Lazzeri <marcomail@noze.it> wrote:
> What about a SELECT syntax like that?
>
> SELECT
>     CASE WHEN bar.foo_id IS NULL THEN bar.*
>     ELSE foo.*
> FROM foo, bar
>
> Anyway, I need a SELECT query that gets data from a FIRST TABLE if a
> specific value in a SECOND TABLE is NULL or from SECOND TABLE itself if
> the value IS NOT NULL.
>
> Any suggestion?

You almost certainly want some join condition between foo and bar.
You can't actually use *. You will need a CASE for each column.
CASE statements need and END.

On Thu, 15 Jan 2004, Bruno Wolff III wrote:

> On Thu, Jan 15, 2004 at 18:23:47 +0100,
>   Marco Lazzeri <marcomail@noze.it> wrote:
> > What about a SELECT syntax like that?
> >
> > SELECT
> >     CASE WHEN bar.foo_id IS NULL THEN bar.*
> >     ELSE foo.*
> > FROM foo, bar
> >
> > Anyway, I need a SELECT query that gets data from a FIRST TABLE if a
> > specific value in a SECOND TABLE is NULL or from SECOND TABLE itself if
> > the value IS NOT NULL.
> >
> > Any suggestion?
>
> You almost certainly want some join condition between foo and bar.
> You can't actually use *. You will need a CASE for each column.
> CASE statements need and END.

Alternatively (though it fails to use the 'CASE' syntax) would be to use
COALESCE.  Something like:

    SELECT COALESCE((SELECT foo_id FROM table2 WHERE ...),
            (SELECT bar_id FROM table1 WHERE ...));

HTH
    -frank