Is the MRG wrong about table aliases on sql custom queries?

[Joe_Pineda]

In the OB12 DB Reporting Guide MRG, page 124, the example below is given on a custom sql query. Yet, the same MRG states that table aliases are not supported (but shown in the example below). Then. on this web site, a forum posting states that table aliases for itemdata will cause an error.  I took out the itemdata alias, but left in other table aliases, and the query runs fine.

Hyland needs to correct the MRG or issue an update on what the true correct way of writing a sql custom query is.

Any thoughts? Am I missing something?

--------------------------------

From/Where Clause Syntax Examples

From Clause

hsi.itemdata i, hsi.keyxitem113 ki, hsi.keytable113 kt

Where Clause

i.itemnum= ki.itemnum

and ki.keywordnum=kt.keywordnum

and substring(kt.keyvaluechar,1,1)= 'A’

and i.itemtypenum = 101 and i.status + 0 = 0

------------------------------------------------------------

[Ansley_Ingram]

Hi Jose,

The section of the Database Reporting Guide that you are referring to is incorrect. I will see that it gets updated to reflect the correct information about aliasing with SQL based Custom Queries.

With SQL based Custom Queries, you cannot alias the ITEMDATA table but you can use aliases for any other tables that you JOIN to or subquery. The reason for this is that the SELECT portion of the query is hard coded (i.e. SELECT hsi.itemdata.itemnum, hsi.itemdata.itemname, etc.) so the ITEMDATA reference in the FROM clause needs to be the full name of the table (i.e. HSI.ITEMDATA). Since the SELECT is hard coded and won't include columns from your JOINed tables, you can alias those tables with no ill effect.

Here's a corrected version of the example:

From Clause

hsi.itemdata, hsi.keyxitem113 ki, hsi.keytable113 kt

Where Clause

hsi.itemdata.itemnum= ki.itemnum

and ki.keywordnum=kt.keywordnum

and substring(kt.keyvaluechar,1,1)= 'A’

and hsi.itemdata.itemtypenum = 101 and hsi.itemdata.status + 0 = 0

Hope that helps.

Ansley

[Joe_Pineda]

Hi: did you guys ever correct this?

[Marcus_Christi6]

You have four different areas.  SQL Custom Query, External Autofill, Report Services, direct SQL query.

In my experience I have never gotten itemdata aliases to work in SQL Custom Query.  This is because some elements are "hard coded" and not visible to the query, and aliases will conflict.  This is a very limited way of querying documents for this reason, so you often have to fully qualify every table and field.

In the External Autofill and direct SQL Query (i.e. SQL Server Management Studio), you can write it however you want, and in fact it's preferred to use aliases for simplicity of the statement.