RE: Trying to do SQL INSERT INTO with subquery

Dan,

Would this be an equivalent query?

Select *
from oprrfvp b
where b.rvsabb = 'WV'
and not exists ( Select *
from oprrfvp a
where a.rvsabb = 'WV'
and a.rvprcd = b.rvprcd
and a.rvdtcode = b.rvdtcode
and a.rvrrdt > b.rvrrdt )

Instead of finding rows that match the max rvrrdt skip any row with another row that has a later rvrrdt.

Paul

Principal Programmer Analyst
IS Supply Chain/Replenishment

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan
Sent: Monday, October 25, 2010 4:14 PM
To: Midrange Systems Technical Discussion
Subject: Trying to do SQL INSERT INTO with subquery

The following query generates the data I want to see:
With MostRecentRR as (
select DISTINCT a.rvprcd, a.rvdtcode, max(a.rvrrdt) as Max_rvrrdt
from oprrfvp a
where a.rvsabb = 'WV'
group by a.rvprcd, a.rvdtcode )
Select * from oprrfvp b
where b.rvsabb = 'WV'
and exists (Select * from MostRecentRR a
where a.rvprcd = b.rvprcd
and a.rvdtcode = b.rvdtcode
and Max_rvrrdt = b.rvrrdt )

Trouble is, I want to use this in a "Data Transfer from iSeries" transfer in
Excel, so that others don't have to run anything on the i before importing
the data into Excel. So, it would appear that I need to have the main Where
clause contain the subquery in order to 'fit" the Change Data Options
dialog.

IN CASE IT IS NOT POSSIBLE to use a subquery in a "Data Transfer from
iSeries" transfer, I tried to preface the query above with
"INSERT INTO LIB/FILE ("
with a trailing
") WITH DATA"
but got "Token MOSTRECENTRR was not valid." When I tried moving the INSERT
INTO just after the "group by", I got "Keyword INSERT not expected."

Ideas appreciated!

TIA,
- Dan