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



I use (Mostly for reports)
WITH Temp_Result_Set as
(select column1 as temp_col1, column2 as temp_col2
 from tablea
 where  column1 in (value1, value2)....
)
select temp_col1, temp_col2
from temp_result_set
group by temp_col1
....
....

I did have some applications where this did not help and had to create a
temp table or a view.



-----Original Message-----
From: Mike Naughton [mailto:mnaughton@juddwire.com]
Sent: Thursday, February 21, 2002 9:49 AM
To: rpg400-l@midrange.com
Subject: Re: RE: embedded SQL Precompiler


Joe -
I'll be really surprised if you haven't already thought of this, so maybe
I'm missing something basic, but I think this can actually be done fairly
easily.

First, it sounds as if the basic requirement is that the results of the
first "select" must stick around somewhere so that they can be available
for future selects. Why not just create a file in QTEMP, either by using
CRTPF (or CRTDUPOBJ), or directly with an SQL statement (I guess this
would be instead of creating a view, and I admit I'm not too clear on why
you might or might not want a view)? Once you have the file, your first
SQL select can insert into it (can't it?), and if the file is keyed on
customer number then listing all the customers and then going back and
retrieving the info is simple using either SQL or READs and CHAINs. (If
the file can't be keyed for some reason (?), then SQL would still work).

I guess a downside, performance-wise, might be the overhead of creating
the file in the first place -- some of the more adept programmers on the
list might suggest something fancier using a user space, maybe?

Seems simple enough to me (so I've _gotta_ be missing something. . . . )
rpg400-l@midrange.com writes:
>On the other hand, here's a feature that would be beyond either natigve
>I/O
>or the current state of SQL affairs: create a subset of records from an
>existing result set.  Now THAT would be nice.  Here's the business issue:
>
>1. Create a view of, say, invoices that were over 90 days, by customer and
>date
>2. Subselect out just the customer numbers from that set, showing them to
>the user
>3. Let the user pick a customer, and go back to the original result set
>and
>then subselect for just that customer


Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA  01376
413-863-4357 x444
mnaughton@juddwire.com

_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

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.