RE: Insert with Temp Table

Michael,

Someone else deserves credit, but I do not remember the name.

However, I think I had a similar sql statement composition problem.

The following is some dynamic code that is in production in river city.

Hope this helps:

INSERT INTO ?DivLib/fsp961p
WITH CTE1 AS
(SELECT TX10.tranum, TX10.tranumsuf,
TX20.artnum, SUM(TX20.uni) AS totuni
FROM ?DivLib/TX10 JOIN ?DivLib/TX20
ON TX10.tranum = TX20.tranum
AND TX10.tranumsuf = TX20.tranumsuf
AND TX20.crycod = 'USD'
AND TX20.artidf IN ('1A', '1R')
AND TX20.eftupdseq = 999
WHERE TX10.batloctyp = 'BIL'
AND TX10.batloc >= ' '
AND TX10.batdat BETWEEN ?FrCymd AND ?ToCymd
AND TX10.mneprcsta = 'BIF'
AND TX10.subprcsta1 = ' '
AND TX10.traidf = '4210'
GROUP BY TX10.tranum, TX10.tranumsuf, TX20.artnum
)
SELECT QDAT.centyr, QDAT.period, TX10.outloc,
OM01.crdfrq1, TX10.outnum, TX22.artnum,
CAST(SUM(ROUND(TX22.adjamt/1000000, 4)) AS DEC(15,4))
AS actcmm, SUM(CTE1.totuni) AS totuni
FROM ?DivLib/TX10 JOIN ?DivLib/TX22
ON TX10.tranum = TX22.tranum
AND TX10.tranumsuf = TX22.tranumsuf
JOIN ?DivLib/OM01 ON TX10.outnum = OM01.outnum
JOIN swire/qdate QDAT ON TX10.batdat = QDAT.cymdat
JOIN CTE1 ON TX10.tranum = CTE1.tranum
AND TX10.tranumsuf = CTE1.tranumsuf
AND TX22.artnum = CTE1.artnum
WHERE TX10.batloctyp = 'BIL'
AND TX10.batloc >= ' '
AND TX10.batdat BETWEEN ?FrCymd AND ?ToCymd
AND TX10.mneprcsta = 'BIF'
AND TX10.subprcsta1 = ' '
AND OM01.crdfrq1 IN (?CFIN)
AND TX22.adjtyp IN ('70', '71')
AND TX10.traidf = '4210'
AND TX22.adjgrp = 'A '
GROUP BY centyr, period, TX10.outloc, TX10.outnum,
crdfrq1, TX22.artnum ORDER BY centyr, period,
TX10.outloc, TX10.outnum, TX22.artnum


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: Wednesday, October 03, 2012 10:59 AM
To: Midrange Systems Technical Discussion
Subject: Re: Insert with Temp Table

Thanks Gary...that didn't seem to work.

On Wed, Oct 3, 2012 at 12:53 PM, Gary Thompson <gthompson@xxxxxxxxxxx> wrote:

Michael, maybe you meant " set tcsls=mysls, SET tcunit=myunit . . ."

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: Wednesday, October 03, 2012 10:49 AM
To: Midrange Systems Technical Discussion
Subject: Insert with Temp Table

Anyone see why this makes STRSQL spit up?

update WCSRTL/WCStmpP set tcsls=mysls, tcunit=myunit with mydata as
(Select SUVNDA, SUMDL, Sum(SUTSLS) As MySls,sum(suunit) as myunit

From SUMSLSP Where SUDATE Between 20120601 and 20120901 SUSTNO =

999 And SUTYPE = 'D') Select SUVNDA, SUMDL, MyCount From MyData

I'm doing the same type of thing with an Insert and it works fine. I'm getting this:

Token MYDATA was not valid. Valid tokens: ALL CS CHG NONE RR RS UR N

with the cursor at the "with mydata as" area.

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

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