Re: Oracle SQL: CASE Statement

Hi Pablo,

That would work. Except I don't have the permissions to create the table 'date_label', and even if I did the table would have to be replaced/removed frequently as the contents are defined at runtime.

Is there a similar way, in which I could do something like:

SELECT a.*, NVL(b.val, NVL(c.val, 'Unknown')) FROM my_table_a a,

       (SELECT TO_DATE('2000', 'YYYY') start_date, TO_DATE('2001', 'YYYY') end_date, 'Y2K' val) b,

       (SELECT TO_DATE('1998', 'YYYY') start_date, TO_DATE('1999', 'YYYY') end_date, 'Y2K' val) c,
WHERE

        a.someDate BETWEEN b.start_date (+) AND b.end_date (+) AND
        a.someDate BETWEEN c.start_date (+) AND c.end_date (+);

I've tried this, but I'm told that the sub-SELECTs need FROM statements. Using a from which guarantees a single result is not ideal but worked. However, then the join clauses didn't work.

Looked to me like a bug, but the solution was getting too messy anyway.

How can I do a sub-select that would have the same effect as creating a table and joining it in?

Thanks,

"Pablo Sanchez" <pablo_at_dev.null> wrote in message news:<y9ga8.59
> I must be missing something ... why wouldn't you have a table as
> follows:
>
> create table date_label
> id
> start_date
> end_date
> val
>
> Then you would write your query as follows:
>
> SELECT a.*, NVL(b.val, 'Unknown')
> FROM my_table_a a,
> date_label b
> WHERE a.someDate BETWEEN b.start_date (+) AND b.end_date (+);
Received on Thu Feb 14 2002 - 13:46:29 CET