Re: AW: SQL record exists

Hi Antonio

I will be very strict in my interpretation now.

Number is a column in a table (not a variable)
:Number is a host variable

That is exactly what Luis said, by the way.

I guess I would not use the term "SQL variable" - there is a context for that, maybe, in a user-defined function. But this is not that context.


In the second case, IsFound is an RPG variable. You would not use the colon before the name in a regular RPG expression. You use the colon in SQL statements, because if you don't do that, SQL thinks it is a column in a table. To repeat, the colon is a special signal to SQL and is used only within an SQL statement to refer to something OUTSIDE of SQL.

HTH
Vern

Antonio Fernandez-Vicenti wrote:

Luis, maybe I did not make it very clear in my question:
I already knew that " :variable" (with a colon) refers to a host (RPG) variable.
My question was: (as from your example):
... where Number = :Number;
in this sentence Number and :Number refer to different variables: the one from RPG (:Number) and the SQL variable (Number).
Don't they?

In Brigitta's post I was asking:

Clear IsFound;
Exec SQL Select 1 Into :iSFound
From MyFile
Where ....
Fetch first row only;

my question was relative to the variable being "cleared" IsFound without colon
versus the receiving one in the Select ":iSFound" with colon...
TIA
--------------------------------------------------------------------------------

Luis Rodriguez escribió:

Antonio,

In short, yes.

In embedded RPG SQL, when you prefix a variable with : it means that
variable belongs to the RPG program. For example, if I say:

EXEC SQL
Select 1 from my table
Where Number = :Number;

The First "Number" belongs to the SQL table. The second one to your program.

HTH,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Sun, Feb 7, 2010 at 6:21 AM, Antonio Fernandez-Vicenti <
afvaiv@xxxxxxxxxxxxxx> wrote:

Birgitta Hauser escribió:

To avoid this problem enhance the statement as follows:

Clear IsFound;
Exec SQL Select 1 Into :iSFound
From MyFile
Where ....
Fetch first row only;

Sorry if I'm asking a stupid question, but... are the variables:
- "IsFound" being cleared by "Clear"
- and "*:*iSFound" (prefixed with "colon :"...) in the Select
referring to the same variable?
Sorry if I'm short in SQL knowledge...

--
Antonio Fernandez-Vicenti
afvaiv@xxxxxxxxxxxxxx

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