Re: SQL Error table name issue

Because the naming was initially presumed to be *SYS, and the table-reference was initially written as "FROM OEORH41", conspicuously as unqualified [i.e. no library\schema name], the "table designators" were left as originally composed; i.e. they were not written as "correlation identifiers" into the statement, which would have avoided this error. Since having changed to use *SQL naming, the table designators now have slightly different requirements, as described by the [portion of the remaining unSNIPPED] text of the message SQL5001, found in the OP quoted below my reply.

The statement would most easily be corrected by adding the table-name as the correlation-id on every table-reference. Otherwise for qualified table-reference, the same qualifier must be added to each of its column-name references elsewhere in the query; e.g. the select-list and the predicates in the WHERE-cluase.

See the lines of the query [inline to the quoted OP message] where the comment text "/* @! correlation-id added */" appears, to see the "as correlation-identifier" that was added to each table-reference on the FROM-clause. Note that for each of the unqualified table-references, adding the correlation-id is an *optional* change. However if the statement were modified to library-qualify any of those table-references in the future, then the statement would start failing again with the SQL5001... if either the column-name references were not also updated, or the correlation-id was not added at that time. Best to handle the assumptions up-front, explicitly assign a correlation id, in order to avoid possible future incidents of the same.

Regards, Chuck

On 28 May 2013 11:15, Crystal Reports wrote:

I am making great progress, we are almost there...
This SQL Which was posted last week, needs one thing it seems the
table name.file name is not the correct way. If I understand,
the file name OEORH41 used in the FROM cannot be used also on the
INNER JOIN?

SQL State: 42703
Vendor Code: -5001
Message: [SQL5001] Column qualifier or table OEORH41 undefined.
Cause . . . . . : Name OEORH41 was used to qualify a column name
<<SNIP>> The name is not defined to be a table designator in this SQL
statement or the table designator cannot be referenced where it is
specified in the SQL statement. If a correlation name is specified
following the table name in a FROM clause, the correlation name is
considered to be the table designator. If a correlation name is not
specified, the table name is considered to be the table designator.
If using SQL naming and the table is qualified with authorization
name, then the table designator is authorization-name.table-name. If
the authorization name is not specified, the table designator is the
implicit authorization name followed by the table name. <<SNIP>>
Recovery . . . : Ensure all column names are qualified with a valid
table designator. <<SNIP>> Try the request again.

---- the SQL query:

SELECT
NOTEH6.UARIDC
, NOTEH6.UANOTL
, NOTEP1.UCCMT
, OEORH41.OHORD#
, OEORH41.OHOSTC
, OEORH41.OHORDT
, OEORH41.OHORDD
, OEORH41.OHPO#
, OEORH41.OHSHTC
, OEORH41.OHSLR#
, OETRA95.OTTRNC
, REFER1.RFCAT
, REFER1.RFDAT
FROM ASTCCDTA.OEORH41 as OEORH41 /* @! correlation-id added */
INNER JOIN REFER1 as REFER1 /* @! correlation-id added */
ON OEORH41.OHSHTC = REFER1.RFSLC
INNER JOIN OETRA95 as OETRA95 /* @! correlation-id added */
ON OEORH41.OHCOM# = OETRA95.OTCOM#
AND OEORH41.OHORD# = OETRA95.OTORD#
INNER JOIN NOTEH6 as NOTEH6 /* @! correlation-id added */
ON OEORH41.OHENT# = NOTEH6.UAENT#
AND OEORH41.OHSFX# = NOTEH6.UASFX#
INNER JOIN NOTEP1 as NOTEP1 /* @! correlation-id added */
ON NOTEH6.UANOTT = NOTEP1.UCNOTT
AND NOTEH6.UANTK = NOTEP1.UCNTK
AND NOTEH6.UANOT# = NOTEP1.UCNOT#
WHERE NOTEH6.UARIDC = 'UA'
and OETRA95.OTTRNC = '001'
/* !! Note: use special reg below !! */
and OEORH41.OHORDD = CURRENT DATE
/* !! Note: use IN predicate below !! */
and OEORH41.OHORDT IN ('COC', 'COE', 'COF', 'COR', 'COS')
/* !! Note: missing operator below !! */
AND OEORH41.OHOSTC<> 'CN' /*<-- "AND" was presumed+added */
and REFER1.RFCAT = '0023'