Re: iSeries and Microsoft Query (Excel)

["Bill" <brobins3d@xxxxxxxxx>]

Lloyd,

I use MSQuery all the time and have used derived tables a number of times.

lgoodbar@xxxxxxxxxxxxxx wrote:
> Sorry about PRODO. Following your suggestion, starting with:
>
> with temp as
> (select nxkjdt, nxjpt2, sum(NXS7FQ) tran
> from VNGDBDTA.DBNXCPP
> where nxkjdt between 1070301 and 1070307
> group by nxkjdt, nxjpt2),
> temp2 as
> (select dmatdt, dmekcd, sum(dmgzfq) prod
> from VNGDBDTA.DBDMREP
> where dmqwtx = 'SFR' and dmcdcd = ';MRCT' and
> dmatdt between 1070301 and 1070307
> group by dmatdt, dmekcd
> )
> select dmatdt, dmekcd, prod, tran
> from temp2
> left outer join temp on dmatdt = nxkjdt and dmekcd = nxjpt2
> order by 1,2

I think the answer is the same for all of your examples:

Change this line:
select nxkjdt, nxjpt2, sum(NXS7FQ) tran
        to
select nxkjdt, nxjpt2, sum(NXS7FQ) as tran

and you should be fine.

Bill