Re: SQL Create table

Hello SQL- ers

I've encountered a buffling issue with SQL

a select statement returns data (one row)
But using the same statement to create a table results in an empty table.

The select statement:

select a.DGSCDE,C.DGSCDE as DGSCDEFH , B.SRCHNM, C.DGMFHNBR,
C.DGMYER,C.EYERORD, C.EDGMORD,sum(C.DGMTMS) as DGMTMS,
C.DGMFACTOR, SUM(C.DGMFINLTMS) as DGMFINLTMS ,C.DJDGL
from PRFLIBD/DGMTRNF A JOIN PRFLIBD/DGMFHTRNF C
ON A.DGMYER = C.DGMYER AND A.YERORD = C.EYERORD
AND A.DGMORD = C.EDGMORD
JOIN GVIALIBD/SRCTBLF B
ON C.DGSCDE = B.SRCCDE
where A.DGSCDE = 'FH' AND A.LNGCDE = '1'
AND A.WRKNBR = '000120' AND A.VRSION = '001'
group by A.DGSCDE,C .DGSCDE,SRCHNM,C.DGMFHNBR,C.DGMYER,
C.EYERORD, C.EDGMORD,C.DGMFACTOR,C.DJDGL

result is
FH FH Festivities 2
2,017 1 1,263 2 1.1380507
2

when I add a "create table as (" at the beginning and a ") with data" at
the end
to form a "create table QTEMP/DGMTRNEXF as ( select statement ) with data
I get (with debug) the following:

ODP created.
Blocking used for query.
Query options retrieved file QAQQINI in library QUSRSYS.
**** Starting optimizer debug message for query .
Arrival sequence access was used for file DGMTRNEXF.
Query options retrieved file QAQQINI in library QUSRSYS.
**** Ending debug message for query .

ODP created.
ODP deleted.
ODP deleted.
Row not found for INSERT.
Table DGMTRNEXF created in QTEMP.

Rings a bell anyone?

(this is a 7.3 machine)

TIA
Gad
--
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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD