Re: SQL Strangeness

"the tables only exist in a single library in the library list"

Not quite the same as saying they only exist in one place. :)

It really seems to me that you're somehow opening another version of the
table.

I've been bitten a couple of times by SQL strangeness that ended up being
cause by a table being used I didn't expect. It's surprising how hard it
is to find. Probably due to the fact it's hard to discard what we know to
be true!

Can you run the program in debug and stop it just prior to the SQL being
run and make sure the library list is what you expect? You might also
double check that your compiling with *SYS naming instead of *SQL.

Alternatively, how about creating an unique alias for the table and
referencing that in the SQL.

Charles




On Wed, Jan 8, 2014 at 10:35 AM, Mark Murphy/STAR BASE Consulting Inc. <
mmurphy@xxxxxxxxxxxxxxx> wrote:

I have validated that both tables actually have the columns in question
using DSPFFD, and the tables only exist in a single library in the library
list. I have no overrides in my programs, or in my job.

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx


-----CRPence <CRPbottle@xxxxxxxxx> wrote: -----
To: midrange-l@xxxxxxxxxxxx
From: CRPence <CRPbottle@xxxxxxxxx>
Date: 01/07/2014 11:53PM
Subject: Re: SQL Strangeness

Mark Murphy on Tuesday, January 07, 2014 2:14 PM wrote:

This should be a very simple SQL, but it is not working out that
way. Can anyone see what I am not?

RPGLE Free Form, V7.1 TR6

<<SNIP>>
select
...
osnam, osad1, osad2, osadx, osad3, ossta, oszip, tscst
from saysv100
<<SNIP>>

Error is SQL1105: Position 63 Object TSCST in *LIBL not found.

Error on line directly above from saysv100.

Strange thing is if I remove the tscst field from that line, the
error shifts to oszip, and so on until the line is gone, and then it
gives me:
SQL0117: Statement contains wrong number of values.

<<SNIP>>
when I run the program, I get:

Message ID: SQL0206 <<SNIP>>
Message: Column or global variable TSCST not found.
Cause: TSCST was not found as a column of table *N in *N and was
not found as a global variable in *N. If the table is *N, TSCST
is not a column of any table or view that can be referenced.
<<SNIP>>

This makes no sense. If I remove columns osnam through tscst in both
the insert column list, and the select column list, all is well, and
the program runs properly. If I cut and paste into green screen SQL,
it works as expected.

Note: The original message never appeared in the newsgroup, so I
/borrowed/ a reply to respond to... but the quoted message above is
apparently [though snipped\edited] the original.

Prior releases did not have /SQL variable/ support, so I have no
access to the message text to know if the text for the -1105 error is
appropriate; its text on v5r3 seems inappropriate in that context
[except if as other suggest, some extraneous characters like () are at
the end of the line out of the visual presentation are]. The "Object
not found" may seem inappropriate at first glance, but would be valid
for an assumption by the SQL that the reference could be a variable vs a
column; similar to how SQL0206 was expanded from only referring to a
missing column.

The SQL1105 could be effectively the same as a -206, but for a
delayed prepare or pre-compile, whereby the error can be ignored until
run-time. I can not find the description\text of the error in the finder:
<
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/rzas2/rzas2finder.htm

That all of those columns get the same error, and that the -117
occurs only after all of those names are removed, and that the run-time
-206 occurs, are all very consistently suggesting that the unqualified
table-reference in the "FROM saysv100" is missing those columns. I
suggest library-qualifying the table-reference, and then if the error
persists, DSPFFD of that specific file and review for those column
names. Perhaps that [VIEW] database file was intended to have its
column-list expanded, but it was never updated; e.g. as a VIEW, perhaps
the underlying TABLE was expanded to have those additional columns, but
the VIEW was not updated by a DROP and the CREATE VIEW to include those
new columns.

FWiW: The "*N in *N" in the SQL0206 seems questionable, because the
file table-reference saysv100 conspicuously must have been located; i.e.
obviously the SQL knows the other columns are there. By its name, the
file is likely a VIEW, and thus apparently is a "view that can be
referenced", so why its name does not appear in the replacement text for
the SQL0206 may be a defect. Not sure about the second-level and
replacement text for the SQL1105; specifying the *SECLVL option for the
pre-compile per OPTION(*SECLVL) should get the full text for the SQL1105
error.

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