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



Thanks Chuck, especially for the IBM site references.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Friday, March 21, 2014 10:08 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Run Sql Scripts Global Variables question

On 21-Mar-2014 08:24 -0700, Gary Thompson wrote:
Got latest version of Run Sql Scripts and noticed a new (to me) Global
Variables Tab at the bottom, to the right of the familiar Messages.

I created a variable:

CREATE OR REPLACE @LIB1 VARCHAR(10);
SET @VAR1 = 'MYLIB1'

Variable shows in the Global Variables Tab as:
QGPL/@LIB1 'MYLIB1'

I then try a query:
select * from @LIB1/MYFILE;

QGPL is in the list of schemas in Sys i Nav and in the lib list of the
job supporting my Run Sql Scripts session, yet I've not been able to
run the query ?

Message SQL0204 shows the variable name used as the file name and the
file name used as the member name.

The jog log for my session shows: MYFILE in @LIB1 type *FILE not
found.

Similar results when using Naming convention *SQL ?

They are SQL [Global] Variables, not script[ing] variables. Where a [host or other] variable can be used within a SLQ statement remains the same as before; i.e. "Global variables can be used in any SQL statement that allows a variable."

FWiW, [ignoring the inconsistency in naming as both @VAR1 and @LIB1, thus assuming each reference is the one name @LIB1] even if the variable were supported in that context, the name would need to be explicitly qualified to ensure the value is not ambiguous; i.e. @LIB would need to be referred to in the statement as "select * from qgpl.@LIB/MYFILE"
because, per the docs, "Names that are the same should be explicitly qualified."

<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcvariable.htm>
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference
-> Statements
_CREATE VARIABLE_
"The CREATE VARIABLE statement defines a global variable at the application server. ..."

<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzch2refvar.htm>
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference
-> Language elements
_Variables_
"A variable in an SQL statement specifies a value that can be changed when the SQL statement is executed.

There are several types of variables used in SQL statements:

_global variable_
Global variables are defined using the CREATE VARIABLE statement.
For more information about how to refer to global variables see Global variables.
..."

<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzglobalvar.htm>
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference
-> Language elements -> Variables
_Global variables_
"Global variables are named memory variables that you can access and modify through SQL statements.

Global variables enable you to share relational data between SQL statements without the need for application logic to support this data transfer. ...
...
Global variable names are qualified names. When a global variable is referenced without the schema name, the SQL path is used for name resolution.
...
Global variables can be used in any SQL statement that allows a variable. Global variables can be referenced within any expression except ...
..."

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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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.