Re: create table AS question

Try
COALESCE(IBITEM,' ')

instead


Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx


                                                                       
             "rick baird"                                              
             <rick.baird@gmail                                         
             .com>                                                      To
             Sent by:                  "Midrange Systems Technical     
             midrange-l-bounce         Discussion"                     
             s@xxxxxxxxxxxx            <midrange-l@xxxxxxxxxxxx>       
                                                                        cc
                                                                       
             08/22/2006 11:53                                      Subject
             AM                        create table AS question        
                                                                       
                                                                       
             Please respond to                                         
             Midrange Systems                                          
                 Technical                                             
                Discussion                                             
             <midrange-l@midra                                         
                 nge.com>                                              
                                                                       
                                                                       




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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.