RE: Differences between STRSQL and embedded SQL

["Thomas Garvey" <tgarvey@xxxxxxxxxx>]

I will try both your ideas.

Thanks 

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, March 20, 2013 5:22 PM
To: midrange-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. The SQL statement works when 
> executed interactively using STRSQL, but fails with SQL0117 (Statement 
> contains wrong number of values).
>
> 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
     the file by that name 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, cast as
     named as
> field idx.
>
> It works fine interactively. But when this statement is performed in 
> RPGLE embedded SQL (as a string, which is then PREPAREd and EXECUTEd), 
> I get the SQL0117. When I look up the SQL0117 explanation, I don't see 
> that any of the conditions exist.

   FWiW the string can be executed with EXECUTE IMMEDIATE instead.

> Anyone have any ideas?  And I always thought: if I could do it 
> interactively, I could certainly do it in embedded SQL.

   That would occur and be expected if the SQL honored an override of the
name F3003 to the database file QGPL/F3003.  Check for overrides when the
application runs, versus when the STRSQL was being utilized; or use
different name for the created file than the selected FROM file.

--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.