RE: SQL file not hardcoded

Jeff
as you are dynamically creating the SQL, create the entire thing
ActSQL = 'Select Coalesce(Min(INVDT),' + @Q + '0001-01-01' + @Q + ')' +
', Coalesce(Max(INVDT),' + @Q + '0001-01-01' + @Q + ')' +
', Coalesce(Count(*),0) ' +
'Into ' + BGNDAT + ', ' + ENDDAT + ', ' + TOTRECS + ' From '
+ uSlsFil;
Exec SQL Prepare Stmt1 from :ActSQL;

Exec SQL Execute Stmt1;

Not knowing how TOTRECS is defined, you may have to wrap it with
%editc(TOTRECS:'X')
NOW IT BECOMES STRICTLY RPG



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill

midrange-l-bounces@xxxxxxxxxxxx wrote on 09/14/2007 10:25:12 AM:

I just tried this:

ActSQL = 'Select Coalesce(Min(INVDT),' + @Q + '0001-01-01' + @Q + ')' +
', Coalesce(Max(INVDT),' + @Q + '0001-01-01' + @Q + ')' +
', Coalesce(Count(*),0) ' +
'Into ?, ?, ? From ' + uSlsFil;

Exec SQL Prepare Stmt1 from :ActSQL;

Exec SQL Execute Stmt1
Using :BgnDat, :EndDat, :TotRecs;

And it doesn't work. The joblog has:

Token ? was not valid. Valid tokens: : <IDENTIFIER>.

Followed by:

Prepared statement STMT1 not found.

I found the following in the SQL reference:

Reference and execution rules: Prepared statements can be referred to in

the

following kinds of statements, with the following restrictions shown:

Statement The prepared statement restrictions
DESCRIBE None
DECLARE CURSOR Must be SELECT when the cursor is opened
EXECUTE Must not be SELECT

Note the restriction on EXECUTE. Does this mean I'm toast here?

--
Jeff Crosby
UniPro FoodService/Dilgard
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531

The opinions expressed are my own and not necessarily the opinion of my
company. Unless I say so.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Friday, September 14, 2007 10:05 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL file not hardcoded

Jeff,

It can be done, although I wish your solution was supported.

I don't think you use EXECUTE IMMEDIATE when using a PREPARE.
EXECUTE IMMEDIATE is a combination of PREPARE and EXECUTE.
You should be able to do an EXECUTE Stmt1 USING ... But it
still won't let you use the file name.

One way is to issue an OVRDBF prior to the sql statement.

Another way is to concatenate the file name. I think it
would be something like ActSQL = 'Select
Coalesce(Min(INVDT),' + @Q + '0001-01-01' + @Q + ')' +
', Coalesce(Max(INVDT),' + @Q + '0001-01-01' +
@@ + ')' +
', Coalesce(Count(*),0) ' +
'Into ?, ? , ? From ' + Filename;

Exec SQL Prepare Stmt1 from :ActSQL;
Exec SQL Execute Stmt1 using :MinDate, :MaxDate, :Count;


Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Jeff Crosby" <jlcrosby@xxxxxxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
09/14/2007 09:48 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
cc

Fax to

Subject
SQL file not hardcoded






Anyone tired of SQL questions from me yet?

I have the following embedded in a program (it's RPG, but I assume the
answer will be applicable to any language, hence the midrange
list instead
of the RPG list):

Exec SQL Select Coalesce(Min(INVDT),'0001-01-01'),
Coalesce(Max(INVDT),'0001-01-01'),
Coalesce(Count(*),0)
Into :BgnDat,
:EndDat,
:TotRecs
From SLSALL;

My question is, is there a way to not hardcode the file name
SLSALL? It
doesn't accept a host variable there, like :FileName. I
tried setting it
up
with dynamic SQL, parameter markers, and PREPARE, like this:

ActSQL = 'Select Coalesce(Min(INVDT),' + @Q + '0001-01-01' +
@Q + ')' +
', Coalesce(Max(INVDT),' + @Q + '0001-01-01' +
@@ + ')' +
', Coalesce(Count(*),0) ' +
'Into ?, ? , ? From ?';

Exec SQL Prepare Stmt1 from :ActSQL;

but then couldn't figure out how to execute it. The EXECUTE IMMEDIATE
doesn't like USING. All the examples I found in this regard
use a cursor.

I 'spose you're going to tell me, again, this is something
not to be done.
<g>

Thanks.

--
Jeff Crosby
UniPro FoodService/Dilgard
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531

The opinions expressed are my own and not necessarily the
opinion of my
company. Unless I say so.


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


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

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