Chuck, I appreciate your attempt to keep things articulate.
For the meantime, I will change my code to try the SET CURRENT SCHEMA statement.
Below is my SQL code, as run interactively, one at a time, with the current schema set to one of the data libraries.
DELETE FROM ALAN.MM ;;
DELETE FROM ALAN.SUPBILG ;;
INSERT INTO ALAN.MM
SELECT distinct char( 'AD1',3) DTASET, MMFAM#, MMCLM#
FROM MMEMO
WHERE MMTEXT =
'Supplemental carrier billing suppressed because of EOB applied.'
ORDER BY MMFAM#, MMCLM#
;;
insert into ALAN.SUPBILG
SELECT DISTINCT
DTASET DTASET,
RFFINCLASS FINCLASS,
RFFAM# ACCT#,
RFCLM# CLAIM#,
CHAR( RFFRDT, ISO )
SERVICEDAT,
RFCHG$ CHARGES,
LOSTAT STATE, CLAIMBAL BALANCE,
INS1.INNAME PRIMARYINS,
COALESCE( CHAR(RFINS1.RFIBILLED,ISO), ' ' )
PRIBILDATE,
COALESCE( INS2.INNAME, ' ') SECDRYINS,
COALESCE( CHAR(RFINS2.RFIBILLED,ISO), ' ')
SECBILDATE
FROM alan.MM
JOIN REFNO ON ACCT# = RFFAM# AND CLAIM# = RFCLM#
JOIN LOC ON RFLOCATION = LONUM
JOIN TRANS ON RFFAM# = TRFAM# AND RFCLM# = TRREF#
JOIN CLMTRAXPF ON TRFAM# = CXFAM# AND TRREF# = CXTRN#
JOIN CFSBS70.EOBACTPF ON CXEOBGRUP = EAEOBGRUP AND
CXEOBCODE = EAEOBCODE
JOIN RFINS RFINS1 ON RFINS1.RFIFAM# = RFFAM#
AND RFINS1.RFIREF# = RFCLM#
AND RFINS1.RFISEQ# = 1
JOIN INSUR INS1 ON RFINS1.RFIINS# = INS1.INNUM
JOIN RFINS RFINS2 ON RFINS2.RFIFAM# = RFFAM#
AND RFINS2.RFIREF# = RFCLM#
AND RFINS2.RFISEQ# = 2
JOIN INSUR INS2 ON RFINS2.RFIINS# = INS2.INNUM
ORDER BY DTASET, ACCT#, CLAIM#
;;
----- Original Message -----
From: "CRPence" <crpbottle@xxxxxxxxx>
To: rpg400-l@xxxxxxxxxxxx
Sent: Thursday, May 26, 2016 12:58:45 PM
Subject: Re: Results returned with interactive SQL, but not embedded
On 26-May-2016 09:19 -0500, Alan wrote:
<<SNIP>>
I have a sequence of SQL statements that <<SNIP>>
Why not just offer the actual statements; both the iNav Run SQL
script, and the embedded code. IMO, the actual SQL is much easier to
understand [and to see possible errors], per the SQL being a much more
strictly structured language than English.
BUT! ?? When I run it embedded, I get nothing.
Seems a fup reply suggests that /nothing/ means sqlcode=100.
With the EXACT same statement and syntax, with two differences:
And the actual statements, as coded, would be much clearer. Per use
of :HV, the declaration of that variable from the HLL [and any DDL that
might be relevant] could also help clarify.
One, I’m running the interactive version over one database schema
(library) at a time, with no overrides, whereas the embedded version
runs an override first because this is going to run using the data
from a bunch of schemas.
And the actual OVRDBF, as coded [and any changed defaults noted, that
are assumed due to a custom environment], would be much clearer; to
correlate with the statement(s).
Two, Because of the above, I use the host-variable :Dtaset to
include the library name in the embedded run, but in the
one-library interactive version it’s CHAR(‘ABC’, 3) as Dtaset.
Addendum included here; from a fup reply to clarify the above:
|> My apologies, I misspoke. I should have said,
|> "I use the host-variable :Dtaset to include a library
|> prefix string as a column value in the embedded run,
|> but in the one-library interactive version
|> it’s CHAR(‘ABC’, 3) as Dtaset."
|> In other words, a column that is hard-coded when
|> run interactive, but it comes from a host variable
|> when run embedded.
While I think I understand what is alluded [an expression on the
column\expression-list of the SELECT is coded either as ":Dtaset as
Dtaset" or "CHAR('ABC', 3) as Dtaset", again, the actual statements
likely would elucidate; requires far less inference and imagination.
FWiW: The SQL CREATE VARIABLE can be used to enable the
non-embedded\dynamic interface(s) to specify a variable name, quite the
same as embedded does; using Dtaset as the variable name, just drop the
colon from the host-variable name in the dynamic version of the statement.
The overrides are done using a service program procedure. The
program is compiled with ACTGRP(*NEW), and the service program with
ACTGRP(*CALLER). So it runs in the same activation group, which is
the default for the OVRDBF command.
I’ve done this a lot, I don’t get what’s different now. Any ideas?
I find the SQL debug messages and a review of open files [wrkjob
option(*opnf)] and overrides [wrkjob option(*filovr)] at breakpoints can
be helpful [compare for each scenario; failing and functional], along
with review of the SQLSTATE and SQLCODE after each statement [if the
embedded code is quick&dirty so not yet doing error-checking].
With regard to overrides, I might have coded a CLLE module in a [the
same] service program with ACTGRP(*CALLER) to do Display Override
(DSPOVR), and call that as part of any logging; because as I recall, the
Display Job file-override information seems to be lacking when trying to
see activation-group-scoped overrides, and coding at the command-line
from debug to DSPOVR ACTGRP(whatever_*NEW_became) is a PITA.
midrange.com
