Queries - Modifying SQL to only return Distinct Values

wings

THe generated select for the query is this:

SELECT AuthMenu.L1Name FROM HORIZON_AUTHMENU AS AuthMenu ORDER BY AuthMenu.L1Name

This query does return rows.

We want to add DISTINCT to the sql so it looks like this

SELECT DISTINCT AuthMenu.L1Name FROM HORIZON_AUTHMENU AS AuthMenu ORDER BY AuthMenu.L1Name

But when it runs we get this error message in aware:

"Business Object AUTHMENU not found in business space version"

aware_support

The generated query cannot be what you say it is. Each table name in Aware IM has the following format:

BusinessSpaceName_BusinessObjectName

This is what you should use. Note, that in the testing mode there is also BASTESTDomain prefix:

BASTestDomainBusinessSpaceName_BusinessObjectName

wings

It is exactly what I put in my last post therefore the same as shown on the SQL Form tab of the Queries.

I assume that Aware adds the BASTESTDomain or BASDomain to the table name when it runs depending on what mode your in.

aware_support

You are right - it's a small bug. As a workaround modify the query, so that the name of the table after the business space name has exactly the same case as the name of the business object:

SELECT DISTINCT AuthMenu.L1Name FROM HORIZON_AuthMenu AS AuthMenu ORDER BY AuthMenu.L1Name

Note HORIZON_AuthMenu instead of HORIZON_AUTHMENU

This should fix it.

wings

cool thanks