08-23-2013 03:57 PM
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
------------------------------------------------------------
08-26-2013 10:11 AM
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
10-17-2014 02:13 PM
Hi: did you guys ever correct this?
08-26-2013 10:12 AM
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.
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.