On 29-Mar-2014 06:27 -0700, Billy Waters wrote:
RPG Code:
Exec SQL
update LIb/file1
set var = ( select var1 from lib/file2
where field1 = field2 )
where exists( select * from Lib/file2
where field1 = field2 )
;
Conspicuously, the above embedded SQL as originally given was *not*
the actual code, because the second "FROM" was added by me; added in the
above quoted, but reformatted, SQL.
When presenting an issue, offering the DDL and the *actual* statement
[or as pared, but verified to exhibit the same issue], is best to avoid
multiple messages\replies being posted, attempting to clarify what was
really done.
<<SNIP fluff>>
If I call from command line:
<<SNIP; effectively: SQLSTATE='00000' and SQLCODE=0>>
The effect was restated above with revisions to the quoted text; also
done below, both prefixed with "effectively:". Knowing only the SQL
state and code is sufficiently clear for describing the issue; i.e. what
is the value of an RPG indicator or how the value was set, is not
germane. A note was already made by Vern about possibly avoiding the
test SQLCODE<>0; perhaps using SQLCODE<0 instead.
If I start from MSSQL stored procedure
<<SNIP; effectively: SQLSTATE='01545' and SQLCODE=12 >>
update is successful
While the above invocation additionally states that the "update is
successful", we probably are safe to infer, that was the case for the
prior request as well.?
Different user ids when calling from command line vs from MSSQL.
Please help..
Given the SQL offered, with library-qualified table-references as
shown and fields lacking qualification with correlation-identifiers as
shown [and properly including the "from" in the query for the EXISTS
predicate as shown (revised)], then the latter effect should have been
the same effect seen for the former request, irrespective the
authorization identifier [i.e. user profile]. However we know that the
given SQL was not literally what was used, because what was given would
have failed with a syntax error. Thus we can only *assume* that the
[since rewritten] SQL statement should *consistently* exhibit that
warning condition in both scenarios.
FWiW: If the UPDATE request had been typed into a STRSQL interactive
session, then the effect [per the SQL0012] should have been a "Confirm
Statement" panel being presented with some text stating something
similar to: "You have entered a subquery that contains a correlation
without qualification for:
Field. . . . . . . . . . . . .: FIELD2
Press Enter to confirm your UPDATE statement.
Press F12=Cancel to return and cancel your UPDATE statement. "
Irrespective the possibility there may be a defect for one invocation
failing to properly identify the 01545 condition, the statement can be
rewritten to avoid the Subject issue; i.e. rewritten to provide
Correlation Identifiers for the files and to qualify the field names
with the respective identifier. For example, the following revision
*may* be the means required to make both requests operate without the
warning:
Exec SQL
update LIb/file1 AS A /* Assign FILE1 CorrelationId=A */
set var = ( select var1 from lib/file2
where field1 = A.field2 /* correlate */ )
where exists( select * from Lib/file2
where field1 = A.field2 /* correlate */ )
;
midrange.com
