Tim,

The query below is generated from Report Builder.  It puts a correlation name for each
table in the FROM clause.  "Client" appears in the FROM clause twice so the correlation
names are "Client" and "Client_2" for the CLIENT table.  When the query is executed, the
following message is displayed.

ElevateDB Error #700 An error was found in the statement at line 7 and column 23 (Invalid
expression "Client" AS "Client_2" found, duplicate table or table correlation name found).

I can make the SQL work by removing the correlation name "Client" from the first reference
to the CLIENT table but I cannot make Report Builder do the same thing.  Should "JOIN
Client AS Client" produce the same result as "JOIN Client"?

SELECT Client.ID, Client.LastName, Client_2.LastName LastName_2,
ClientPractitioner.PractitionerID
FROM ClientPractitioner ClientPractitioner
     RIGHT OUTER JOIN Client Client ON
    (Client.ID = ClientPractitioner.ClientID)
     LEFT OUTER JOIN Client Client_2 ON
    (Client_2.ID = ClientPractitioner.PractitionerID)

Richard Harding

Richard,

<< The query below is generated from Report Builder.  It puts a correlation
name for each table in the FROM clause.  "Client" appears in the FROM clause
twice so the correlation names are "Client" and "Client_2" for the CLIENT
table.  When the query is executed, the following message is displayed.

ElevateDB Error #700 An error was found in the statement at line 7 and
column 23 (Invalid expression "Client" AS "Client_2" found, duplicate table
or table correlation name found). >>

For now, the only workaround is to change the first table's correlation name
(Client Client) to something like this:

Client Client_1

A fix will be available for this in the next build/release.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> For now, the only workaround is to change the first table's correlation
> name (Client Client) to something like this:
>
> Client Client_1
>
> A fix will be available for this in the next build/release.

I believe that this was also corrected in the recent builds of
ReportBuilder.   I reported this back to DM a while back and they sent me a
updated DADE plug-in.

--

---------------------------------------
 Terry Swiers
 Millennium Software, Inc.
 http://www.1000years.com
 http://www.atrex.com

 Atrex Inventory Control/POS -
    Big business features without spending big business bucks!

Atrex Electronic Support Options:
 Atrex Knowledgebase: http://www.atrex.com/atrexkb.asp
 Email: mailto:support@atrex.com
 Newsgroup: news://news.1000years.com/millennium.atrex
 Fax: 1-925-829-1851
 Phone: 1-925-828-5892 (M-F, 9a-5p Pacific)
 ---------------------------------------