Re: SQL Create table

[Steve Richter <stephenrichter@xxxxxxxxx>]

I would remove the where clause just to see that you can create the as
table and populate it with data. Then work back to find why nothing being
inserted from the original statement.


On Sun, Nov 12, 2017 at 4:16 AM, Gad Miron <gadmiron@xxxxxxxxx> wrote:

> 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