Re: getting error on this view

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#
<<SNIP>>
FROM (ASTDTA.OEINDLID
<<SNIP>>

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