create table AS question

["rick baird" <rick.baird@xxxxxxxxx>]

Hey all,

I want to create a table based on a left outer join.

For convenience, I want the outer table fields to be defined as 'not
null with default' - I will dynamically fill a QTEMP copy of this file
in an interactive program using SQL, but I don't want to have to
hassle with checking the outer table fields for null values when
processing it.

Is this possible?  running the following statement gives me an error
on the first "NOT".

Thanks,

Rick

create table QTEMP/UPCRECL as (
select UPLVL1,
       UPUPC#,
       UPITEM,
       UPEAN#,
       IBITEM      NOT null with default ' ',
       cast(sum(IBQTTR) as dec(7,0)) as IBQTTR not null with default 0,
       cast(sum(IBQTYB) as dec(7,0)) as IBQTYB not null with default 0,
       ' ' as QQSTTS
  from ICUPCHP
  left outer
  join ICITMBP
    on UPLVL1 = IBLVL1
   and UPITEM = IBITEM
 group by UPLVL1
       UPUPC#,
       UPEAN#,
       UPITEM,
       IBITEM
) with no data