MIDRANGE dot COM Mailing List Archive

Home » MIDRANGE-L » March 2014



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 */ )

Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot 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 here. If you have questions about this, please contact