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



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.


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.