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