RE: SQL file not hardcoded

["Paul Nelson" <nelsonp@xxxxxxxxxxxxx>]

Not to belabor a point, but you can do this with SEQUEL's run time
variables:

SELECT *
FROM  &library/&filename

And so on.

Paul Nelson
Cell 708-670-6978
Office 512-392-2577
nelsonp@xxxxxxxxxxxxx


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Friday, September 14, 2007 8:48 AM
To: 'Midrange Systems Technical Discussion'
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.