RE: RE: embedded SQL Precompiler

["Mangavalli, Ramanujam" <RamM@xxxxxxxxxxx>]

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.