AW: Trying to do SQL INSERT INTO with subquery

Hi,

create a view for the SQL-Statement and use this view in the "Data Transfer
from iSeries".


Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Dan
Gesendet: Monday, 25. October 2010 22:14
An: Midrange Systems Technical Discussion
Betreff: 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