RE: Differences between STRSQL and embedded SQL

That's intriguing. Maybe. But what I do with the table/view may prohibit
use of a View.

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;

What would change if I created the View instead? I would still refer to
qtemp/F3003, correct?

Thanks,

Tom Garvey




-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of CRPence
Sent: Wednesday, March 20, 2013 7:20 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Differences between STRSQL and embedded SQL

On 20 Mar 2013 14:20, Thomas Garvey wrote:

I'm working on an embedded SQL command that would create a temporary
file of random records from another file. <<SNIP>>

Here's the SQL statement...

create table qtemp/F3003 as
(select IREFFF, rand() as idx
from QGPL/F3003
order by idx
fetch first 380 rows only
) with data

So, F3003 [<ed: in QGPL>] exists as a table with 50 fields, and some
40,000 records. This statement should create a new table in QTEMP with
380 records, consisting only of the IREFFF field and a random number
[<ed: named>] as fieldIDX. <<SNIP>>

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

or

create view qtemp/F3003 as
( select * from
( select IREFFF, rand() as idx
from QGPL/F3003
order by idx
fetch first 380 rows only
) as nte_F3003
)

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