On 15 Sep 2013 06:51, Hoteltravelfundotcom wrote:
I am getting this error: " Column qualifier or table OEINDLID
undefined. " can u see what is wrong here? I am new to VIEWS i have
done only 1 view previously.
CREATE VIEW astccdta.acsusage AS
SELECT ICPRTMIA.IARCC9, OEINDLID.IDDOCD, OEINDLID.IDCOM#
There is nothing about that error that is specific to a VIEW;
instead, generally, to a SQL query. The problem is that the column
qualifiers do not match the identifier used for the table-references.
The *best* option IMNSHO is to [always] specify a correlation-name on a
correlation-clause, and then use that identifier instead of the
table-name as the column qualifier; in the given scenario, the
qualified-table-name would need to be specified for every column
qualifier. Having specified the correlation name instead of the table
name, avoids having to re-qualify the column references throughout the
query *if* ever the table name changes; such a change could include a
change to the name of the library as qualifier or even just having added
the library name as qualifier.
The same error would transpire for the following very simple query
request [using SQL naming], for the same reason:
select OEINDLID.* from ASTDTA.OEINDLID
-- effect: Column qualifier or table OEINDLID undefined.
The above simple query could be revised to specify the fully
qualified name as qualifier, to avoid the error:
select ASTDTA.OEINDLID.* from ASTDTA.OEINDLID
Or [as I allude is the more desirable solution,] the above simple
query could be revised to give the table-reference a correlation
identifier [e.g. FILE01] to be used to qualify the column names:
select FILE01.* from ASTDTA.OEINDLID AS FILE01