Chuck,
I am indeed trying to produce a reduced set of data that exists only for the
duration of this program's analysis. Long term storage of the reduced data
set is not desired. I am also trying to reduce the time involved for this
analysis, so thanks very much for your suggestion.
Best Regards,
Tom Garvey
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of CRPence
Sent: Thursday, March 21, 2013 11:38 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Differences between STRSQL and embedded SQL
On 21 Mar 2013 07:40, Thomas Garvey wrote:
On 20 Mar 2013 17:19, CRPence wrote:
For the particular application, is there any reason the effect could
not or should not be a VIEW instead of a TABLE?
create view qtemp/F3003 as
with
cte_F3003 as
( select IREFFF, rand() as idx
from QGPL/F3003
order by idx
fetch first 380 rows only
)
select * from cte_F3003
<<SNIP>>
That's intriguing. Maybe. But what I do with the table/view may
prohibit use of a View.
I presumed the temporary copy likely was intended to produce a static
reduced-set of data, but I could only guess there was a small chance that
was not the case.
I build up another SQL statement that includes multiple uses of an
external UDF to determine the format of the field's contents using
regular expressions.
Here's a truncated version...
select
count(*)
, sum(RegExpSrv(digits(FieldName),'regular expression')) from
qtemp/F3003
And the results are fetched into 'count' buckets...
Fetch C2 into :RecordCount :NullCount, :CountOne :NullCountOne;
I am not aware of anything that would prevent using VIEWs or table
expressions for that type of processing.
What would change if I created the VIEW instead?
A copy of the data would exist only when the VIEW is open rather than
existing until a DROP TABLE is requested. So mostly an issue of static
results [and thus a copy of data made to a new TABLE] versus dynamic results
whereby the temporary copy of the data [as a true temporary vs a permanent
object with a /temporary/ existence; thus no ownership nor authority
assignments] persists only for the query request.
I would still refer to qtemp/F3003, correct?
Yes. The mention of a VIEW was not in regard to how to resolve the issue
with the -117. Just that, given what was described about what was being
done, actual copies of the data may be unnecessary in some scenarios; that a
VIEW removes the copy for both its time and storage in the front-end, and
would give more dynamic results if required. If the generated queries had
value beyond reference just once, then VIEWs could be created once to
encapsulate [part of] the queries, and have data selected many times...
without maintaining potentially longer-term storage of copied rows.
--
Regards, Chuck
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
