Table Aliasing

[w1mnk]

Here is a sample of Crystal SQL:
FROM
"IGSVIEW"."FINANCIAL_CUSTOM" FINANCIAL_CUSTOM RIGHT JOIN "IGSVIEW"."IS_INVENTORY" ON FINANCIAL_CUSTOM."IS_INVENTORY_ID" = IS_INVENTORY."IS_INVENTORY_ID"
INNER JOIN "IGSVIEW"."ORGANIZATION" ORGANIZATION ON IS_INVENTORY."ORGANIZATION_ID" = ORGANIZATION."ORGANIZATION_ID"
INNER JOIN "IGSVIEW"."LOCATION" LOCATION ON IS_INVENTORY."LOCATION_ID" = LOCATION."LOCATION_ID"
INNER JOIN "IGSVIEW".ART_FLEX ART_FLEX RIGHT JOIN "IGSVIEW".IS_INV_PHY IS_INV_PHY ON ART_FLEX."ASSET_TAG" = IS_INV_PHY."ASSET_TAG" ON IS_INVENTORY."ASSET_TAG" = IS_INV_PHY."ASSET_TAG"
INNER JOIN "IGSVIEW".ORGANIZATION ORGANIZATION AS ORGANIZATION_1 ON IS_INV_PHY."ORGANIZATION_ID" = ORGANIZATION_1."ORGANIZATION_ID"
INNER JOIN "IGSVIEW".LOCATION LOCATION AS LOCATION_1 ON IS_INV_PHY."LOCATION_ID" = LOCATION_1."LOCATION_ID"
INNER JOIN "IGSVIEW".PEOPLE PEOPLE ON IS_INV_PHY."PEOPLE_ID" = PEOPLE."PEOPLE_ID"

I'm trying to alias some tables in order to get the correct data. The AS statements (near the end) fail with a
(database is IBM DB2)

SQL0199N The use of the reserved word AS following "" is not valid. Expected tokens include ON FULL LEFT INNER RIGHT". SQLSTATE=42601.
Any help is greatly appreciated.

 

[IanWaterman]

THis doesnot look like any SQL from Crystal that I have seen before, generally Crystal requires

Select [Field names]
From [TableNames] at this point you add alias eg
Location "Location_1" etc only table names can be aliased.
Where [table links]

I have only used AS in something like a view generation and that can not be done within Crystal and must be down in your database. The View can then be used like a table as above.

Ian Waterman
UK Crystal Reports Consultant

 

[w1mnk]

Ian, the SQL is from CR 8.5

 

[IanWaterman]

Did Crystal Create it or has someone pasted it into the View SQL statement window? If its the latter this could be you problem.

Try building a new report bringing the tables and fields in and make the joins manually, then review the SQL.

Normally when Crystal aliases a table it simply states it as as I did above.

Ian

 

[slupton1]

You're simply using incorrect syntax for CR aliasing. Don't use the keyword "AS" at all. I'm only showing a portion of one line here, but instead of "INNER JOIN "IGSVIEW".ORGANIZATION ORGANIZATION AS ORGANIZATION_1"
use this instead
INNER JOIN "IGSVIEW".ORGANIZATION ORGANIZATION_1

Please note the alias is identified without the "AS" keyword.

 

[synapsevampire]

Most aren't aware of this, but you can use ADO connectivity and paste in a SQL statement in it's entirety.

As for your solution, just add in the table again in the Visual Linking Expert and join it. You don't need to manually edit the SQL, which can later bite you because you lose the ability to use the CR tools to alter joins, etc.

Better yet, use a Stored Procedure or a View.

-k