RE: RE: embedded SQL Precompiler

[Phil Groschwitz <sublime78ska@xxxxxxxxx>]

Ramanujam,

Could you post more of an example?  I'd like to learn
how to do this.  Up to now I've created a temporary
table.

Phil

--- "Mangavalli, Ramanujam" <RamM@Mvmills.com> wrote:
> 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.
> _______________________________________________
> 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.
>


__________________________________________________
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com