Re: SQL Create table with a UNION query returns error

Hi Dan,

I notice that in the error message, it states table name "CA0119W".

In your SQL, it builds table name "AR0119W", a different name.

Make sure you don't have table name "CA0119W" present.

In your STRSQL Session Attributes accessed via F13, make sure you don't
have the output directed to a file of name "CA0119W".

The only other thing I can think of that comes close to being related, is
the only time I've ever seen a symptom like that (table name in error does
not match table name being created), is when I accidentally reuse a system
table name that is already present.

This set of statements would create that scenario:

create table qtemp/MY_LONG_TABLE_NAME for system name MY_TABLE ( TEST
char(1) )
;
create table qtemp/MY_SECOND_TABLE_NAME for system name MY_TABLE ( TEST
char(1) )
;

Result of running 2nd statement: [SQL0601] MY_TABLE in QTEMP type *FILE
already exists.

I don't think that scenario is your problem though, because your CREATE
TABLE statement does not reference table of name "CA0119W".

Aside from either of those scenarios being the problem, it seems like a
defect to me.

Mike

date: Thu, 8 Feb 2018 00:48:34 -0500
from: Dan <dan27649@xxxxxxxxx>
subject: SQL Create table with a UNION query returns error

I'm testing the following query, the final form of which will be embedded
in an RPG program. When I run the query in STRSQL (after verifying that
qtemp/AR0119W does NOT exist), I get an error, SQL0601 "CA0119W in QTEMP
type *FILE already exists." The file is created, and it has data from both
select statements. If I remove the UNION and the second select statement,
I don't get the error. (But, of course, the new table doesn't have all of
the data I need it to have.)

Also, I'm not sure if the end result of this query could be duplicated with
a query that doesn't involve a UNION. The WHERE clauses are exact
opposites in the two select statements, so that every record in SfMast is
in the created table, with the values of qSelect1 and qPrvSlctd dependent
on the WHERE clause. (I probably shouldn't be writing SQL after midnight,
but here we are.)

- Dan

Create table qtemp/AR0119W as (
select cast( 'X' as char( 1)) as qSelect1,
cast( '*' as char( 1)) as qPrvSlctd,
SFM_SLSPID as q_SLSPID,
SFM_SLID as q_SLID,
SFM_SLNAME as q_SLNAME,
SFM_STATUS as q_STATUS from SfMast, SfXref
where SFX_ORGID = 'AXIP'
and SFX_PVDID = 'HARTFIELD'
and SFM_SLSPID = SFX_SLSPID
UNION
select cast( ' ' as char( 1)) as qSelect1,
cast( ' ' as char( 1)) as qPrvSlctd,
SFM_SLSPID as q_SLSPID,
SFM_SLID as q_SLID,
SFM_SLNAME as q_SLNAME,
SFM_STATUS as q_STATUS from SfMast
where not exists ( select * from SfXref
where SFX_ORGID = 'AXIP'
and SFX_PVDID = 'HARTFIELD'
and SFM_SLSPID = SFX_SLSPID )
order by Q_SLSPID
) WITH DATA