using CASE in CTAS ??

[Horace]

Can I use a CASE statement with a CTAS statement....

What I want to do is have date_created = date_of_inbound
only if the sub-select statement row has communication_code = 'I'.

If it doesn't then I want the default date_created.

The pseudo code(?) is something like this....

create table TEST as
(
select w_member.membership_number,

(case w_communication.communication_code
when 'I' then minval date_created
else date_created),

recruiting_channel

from w_member,(select membership_number,
min(date_of_inbound) minval
from w_communication
group by membership_number) abc
where w_member.membership_number = abc.membership_number(+))
);

Any help would be appreciated.

[slimdave]

Why do you think that you can't use CASE? Getting an error message?
By the way, i notice that you don't have a column alias on the case.

[Horace]

I've not used it before, and need advise on the syntax, plus do I need an alias ?

[patel_dil]

SQL>  ed
Wrote file afiedt.buf
  1  create table case_test as (select case when 0 = 1 then 'ONE' else 'ZERO'
  2          end
  3*       from dual)
SQL> /
create table case_test as (select case when 0 = 1 then 1 else 0
                                  *
ERROR at line 1:
ORA-00998: must name this expression with a column alias


Elapsed: 00:00:00.25


SQL>  ed
Wrote file afiedt.buf
  1   create table test_caseb as (select (case when 0 = 1 then 'ONE' else 'ZERO'
  2           end ) cola
  3*        from dual)
SQL> /

Table created.

Elapsed: 00:00:00.09

So yes, 'alias' is needed.