RE: Create table with USING clause not working

Charles-

I never used the USING clause at v5r4 because having the two sets of key fields negated most of the benefit. At v6r1 we are correctly seeing one set and I am happy to use it.

-Tom

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Monday, June 06, 2011 12:38 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Create table with USING clause not working

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.

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