Re: create table AS question

[Alan Shore <AlanShore@xxxxxxxx>]

That is correct.
At least I'm pretty sure COALESCE is what you are looking for.
Alan


                                                                       
             "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 12:25                                      Subject
             PM                        Re: create table AS question    
                                                                       
                                                                       
             Please respond to                                         
             Midrange Systems                                          
                 Technical                                             
                Discussion                                             
             <midrange-l@midra                                         
                 nge.com>                                              
                                                                       
                                                                       




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