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.