You say no other objects exist in the library list but to confirm you have the correct library list, can you find and rename any other occurrences of the table on the entire system?
Glenn
-----Original Message-----
From: "Mark Murphy/STAR BASE Consulting Inc." <mmurphy@xxxxxxxxxxxxxxx>
Sender: midrange-l-bounces@xxxxxxxxxxxx
Date: Wed, 8 Jan 2014 10:35:43
To: Midrange Systems Technical Discussion<midrange-l@xxxxxxxxxxxx>
Reply-To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: SQL Strangeness
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
