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.