Symptoms
A custom SQL query on the 360eyes database returns no data, without any error.
- The query result in the custom report (BOBJ interface) is empty.
- The queried table is visible in the 360eyes universe and in the generated SQL of some documents.
Error Message
No error message is displayed.
Steps to Reproduce
- In the BOBJ interface, create a custom report based on a 360eyes universe.
- Write a SQL query that selects data directly from an alias table (e.g.
CMS_SECURITY_DOC). - Run the query: the result is empty.
Cause
The queried table is an alias table, not a physical table in the 360eyes database.
- Context: 360eyes universes define alias tables in their Data Foundation so that the same physical table can be joined several times in different contexts. These aliases appear in the Data Foundation and in the generated SQL of documents based on 360eyes universes.
- Technical explanation: Alias tables only exist in the universe's semantic layer. They do not exist in the database, so a direct SQL query against them returns nothing.
Resolution
-
Check whether the table is an alias. An alias has the name of its source table plus a suffix.
Example:CMS_SECURITYis the physical table.CMS_SECURITY_DOCandCMS_SECURITY_FINE_DOCare aliases of it.
To confirm, open the Data Foundation of the 360eyes universe: alias tables are displayed with the alias icon and show the source table they refer to. -
Rewrite the query against the source table. Replace the alias name with its source table (e.g.
CMS_SECURITY_DOC→CMS_SECURITY), and reproduce the join condition the alias used in the Data Foundation if needed. - Re-run the query in the custom report and check that data is returned.
Additional Information
- The 360eyes documentation lists all the physical tables of the 360eyes database. A table that is not in this list is an alias table.
- If the query still returns no data after targeting the source table, please contact the support with the SQL query used, a screenshot of the result and the steps you followed in this article.