Re: SQL Error table name issue

[CRPence <CRPbottle@xxxxxxxxx>]

  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'