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.