Re: create table AS question

awesome Alan.  to be clear, I would use this on the insert rather than
the create table, right?

Thanks a bunch.

On 8/22/06, Alan Shore <AlanShore@xxxxxxxx> wrote:

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.



--
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.