× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Jeff,

From the manual:
SELECT INTO
-Invocation
This statement can only be embedded in an application program.
It is an executable statement that CANNOT be dynamically prepared.

Try
VALUES INTO
-Invocation
This statement can be embedded in an application program.
It is an executable statement that CAN be dynamically prepared,

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

HTH,
Charles


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Friday, September 14, 2007 10:25 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL file not hardcoded

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.




This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.