Re: Create table with USING clause not working

[Charles Wilt <charles.wilt@xxxxxxxxx>]

When you do the select statement with using, are there two of each key
field (one set from each file?)

That was the incorrect way USING originally worked at v5r4; it was
corrected at v6r1, but maybe you are missing a PTF?

Otherwise, I suggest opening a PMR.

Charles

On Mon, Jun 6, 2011 at 3:29 PM, Tom E Stieger <testieger@xxxxxxxxxxxx> wrote:
> I was trying to create a bakcup of some data before a did a mass update and thoguht this would work:
>
> create table mylib.FILE_BACKUP  as
> (
> select
>  rthd, opseq, opdsc
> from
>  amflibp.rtghdr join
>  amflibp.rtgopr using (STID, RTID, RTVR)
> ) with data
> ;
>
> The underlying SQL for the select statement works, but the create table causes the  following errors:
>
> SQL State: 58004
> Vendor Code: -901
> Message: [SQL0901] SQL system error. Cause . . . . . :   An SQL system error has occurred...The previous message identifier was MCH3601. Internal error type 6101 has occurred...
>
> MCH3601: Pointer not set for location referenced.
>
> When I change my select staement to use on criteria in the join it works just fine.
>
> create table mylib.FILE_BACKUP  as
> (
> select
>  rthd, opseq, opdsc
> from
>  amflibp.rtghdr A join
>  amflibp.rtgopr B on A.stid=B.stid and A.rtid = B.rtid and A.rtvr = B.rtvr
> ) with data
> ;
>
> Does anyone know why the USING clause doesn't work with the "CREATE TABLE as (select statement) with data"?
> We are at 6.1.
>
> -Tom Stieger
> California Fine Wire
> --
> 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.