Skip to Content
0
Former Member
Jan 11, 2017 at 06:41 PM

How to retrive the Count of particular attribute values through flexible search query ?

4249 Views

I am generating a report, which contains "Id, Name & count(id) in a spreadsheet, for this i able to write a query like below:

"select {hus.uid}, {hus.name}, count({hus.uid}) from {user as hus} group by {hus.uid}, {hus.name}"

which is running with out any issues in hAC but if ran it directly, where as through below flexible search code

public List findAllUserCountDetails(){ String query = "select {hus.uid}, {hus.name}, count({hus.uid}) from {user as hus} group by {hus.uid}, {hus.name}"; final FlexibleSearchQuery flexQ = new FlexibleSearchQuery(query); final SearchResult results = flexibleSearchService.search(flexQ); if (results.getResult().isEmpty()) { return null; } return results.getResult(); } We are using the MS SQL Server 2008 for our requirements.

I am getting the below error:

[UserJob::de.hybris.platform.servicelayer.internal.jalo.ServicelayerJob] [172715] translated query was: SELECT item_t0.p_uid , item_t0.p_name , count( item_t0.p_uid ) FROM user item_t0 WHERE (item_t0.TypePkString=? ) GROUP BY item_t0.p_uid , item_t0.p_name INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | de.hybris.platform.servicelayer.search.exceptions.FlexibleSearchException: SQL search error : An error occurred while converting the nvarchar value to JDBC data type BIGINT. query = 'SELECT item_t0.p_uid , item_t0.p_name , count( item_t0.p_uid ) FROM user item_t0 WHERE (item_t0.TypePkString=? ) GROUP BY item_t0.p_uid , item_t0.p_name', values = [8797404758098] INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.servicelayer.search.impl.DefaultFlexibleSearchService$2.execute(DefaultFlexibleSearchService.java:384) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.servicelayer.search.impl.DefaultFlexibleSearchService$2.execute(DefaultFlexibleSearchService.java:1) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.servicelayer.session.impl.DefaultSessionService.executeInLocalView(DefaultSessionService.java:91) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.servicelayer.search.impl.DefaultFlexibleSearchService.getJaloResult(DefaultFlexibleSearchService.java:363) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.servicelayer.search.impl.DefaultFlexibleSearchService.search(DefaultFlexibleSearchService.java:164)

INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | Caused by: de.hybris.platform.jalo.flexiblesearch.FlexibleSearchException: SQL search error : An error occurred while converting the nvarchar value to JDBC data type BIGINT. query = 'SELECT item_t0.p_uid , item_t0.p_name , count( item_t0.p_uid ) FROM user item_t0 WHERE (item_t0.TypePkString=? ) GROUP BY item_t0.p_uid , item_t0.p_name', values = [8797404758098][HY-0] INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.jalo.flexiblesearch.internal.FlexibleSearchExecutor.execute(FlexibleSearchExecutor.java:169)

INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.jalo.flexiblesearch.internal.FlexibleSearchExecutor.execute(FlexibleSearchExecutor.java:169) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.jalo.flexiblesearch.FlexibleSearch.executeSearch(FlexibleSearch.java:1779) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.jalo.flexiblesearch.FlexibleSearchCacheUnit.compute(FlexibleSearchCacheUnit.java:46) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.cache.AbstractCacheUnit.privateGetNoLock(AbstractCacheUnit.java:302) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.cache.AbstractCacheUnit.privateGet(AbstractCacheUnit.java:275) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.cache.AbstractCacheUnit.get(AbstractCacheUnit.java:182) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.jalo.flexiblesearch.FlexibleSearchCacheUnit.myGet(FlexibleSearchCacheUnit.java:85) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.jalo.flexiblesearch.FlexibleSearch.search(FlexibleSearch.java:1455) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.jalo.flexiblesearch.FlexibleSearch.search(FlexibleSearch.java:1628) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.servicelayer.search.impl.DefaultFlexibleSearchService$2.execute(DefaultFlexibleSearchService.java:374) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | ... 13 more INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred while converting the nvarchar value to JDBC data type BIGINT. INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at com.microsoft.sqlserver.jdbc.DDC.convertStreamToObject(DDC.java:475) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValue(dtv.java:2536) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at com.microsoft.sqlserver.jdbc.DTV.getValue(dtv.java:193) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at com.microsoft.sqlserver.jdbc.Column.getValue(Column.java:132) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:2082) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:2067) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getLong(SQLServerResultSet.java:2328) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.jdbcwrapper.ResultSetImpl.getLong(ResultSetImpl.java:243) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.persistence.property.JDBCValueMappings$ItemPropertyValueReaderWriter.readValue(JDBCValueMappings.java:892) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.persistence.property.JDBCValueMappings$ItemPropertyValueReaderWriter.readValue(JDBCValueMappings.java:1) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.persistence.property.JDBCValueMappings$AbstractValueReaderWriter.getValue(JDBCValueMappings.java:533) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.persistence.property.JDBCValueMappings.readValue(JDBCValueMappings.java:500) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.persistence.property.JDBCValueMappings.readColumns(JDBCValueMappings.java:482) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.persistence.property.JDBCValueMappings.getRows(JDBCValueMappings.java:464) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.persistence.property.JDBCValueMappings.getQueryResults(JDBCValueMappings.java:309) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at de.hybris.platform.jalo.flexiblesearch.internal.FlexibleSearchExecutor.execute(FlexibleSearchExecutor.java:135) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | ... 22 more INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | Caused by: java.lang.NumberFormatException: For input string: "xyehswanth" INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at java.lang.NumberFormatException.forInputString(Unknown Source) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at java.lang.Long.parseLong(Unknown Source) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at java.lang.Long.valueOf(Unknown Source) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at com.microsoft.sqlserver.jdbc.DDC.convertStringToObject(DDC.java:305) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | at com.microsoft.sqlserver.jdbc.DDC.convertStreamToObject(DDC.java:438) INFO | jvm 1 | srvmain | 2017/01/11 15:09:48.158 | ... 37 more

Note: For this requirement i have created my own item type in items xml file. The item type is like below:

itemtype code="UserCounts" deployment table="UserCounts" attributes: uid, name, loginDateTime, logoutDateTime, totalTime, noOfLogins

Can any one please do let me know how to write the correct flexible search query code so that i can get the values like below:

alt text

Attachments

7488-1.png (9.5 kB)