Skip to Content
0
Oct 14 at 01:32 AM

CCV2 no column name was specified for column 2 of CNTTABLEALIAS

99 Views

We are migrating our on premise Hybris installation (Oracle DB) to SAP Cloud CCv2. We are facing a DB SQL error in an out of the box query. Basically when an ASM agent logs in the code internally invokes a query to retrieve recently started sessions for the ASM agent - DefaultRecentlyStartedSessionCustomerListSearchStrategy.getPagedCustomers().

Internally this executes the following SQL query -

SELECT item_t0.p_customer , MAX( item_t0.createdTS ) FROM sessionevent item_t0 JOIN users item_t1 ON item_t0.p_customer = item_t1.PK WHERE ( item_t0.p_agent =? AND item_t1.p_logindisabled = ? AND ( item_t1.p_deactivationdate IS NULL OR item_t1.p_deactivationdate > ?)) AND ((item_t0.TypePkString=? AND item_t1.TypePkString IN (?,?) )) GROUP BY item_t0.p_customer order by MAX( item_t0.createdTS ) DESC

This query fails with the following error (FlexibleSearchException) -

SQL search error - No column name was specified for column 2 of 'CNTTABLEALIAS'. query = 'SELECT item_t0.p_customer , MAX( item_t0.createdTS ) FROM sessionevent item_t0 JOIN users item_t1 ON item_t0.p_customer = item_t1.PK WHERE ( item_t0.p_agent =? AND item_t1.p_logindisabled = ? AND ( item_t1.p_deactivationdate IS NULL OR item_t1.p_deactivationdate > ?)) AND ((item_t0.TypePkString=? AND item_t1.TypePkString IN (?,?) )) GROUP BY item_t0.p_customer order by MAX( item_t0.createdTS ) DESC', values = [ ]

The query works as-is if we execute it in HAC, both the sql and the flexible search version of it.

Based on some analysis, the error is because the DB expects an alias for the second column in the query - MAX( item_t0.createdTS ) . However, this is an OOTB query, so we want to know if there is any DB config or some Hybris property to resolve the issue?

This issue does not happen on the on premise application where we have Oracle DB.