× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.