I have a batchlist select statement that uses a group by with several columns. The total count that I am getting is without the group by but when you select them all it is the correct result. Just curious if I need to do something different to get a proper count.
Code:
<MvEVAL EXPR = "{ [ g.Module_Library_DB ].SQL_Query_SELECT( l.search_query,' DISTINCT EngineBlock.Liter, EngineBlock.Cylinders, EngineDesignation.EngineDesignationName, Mfr.MfrName, ep.product_id AS assigned, ep.note' ) }"> <MvEVAL EXPR = "{ [ g.Module_Library_DB ].SQL_Query_FROM( l.search_query, 'EngineConfig2', 'EngineConfig2' ) }"> <MvEVAL EXPR = "{ [ g.Module_Library_DB ].SQL_Query_LEFT_OUTER_JOIN( l.search_query, 'EngineConfig2', 'EngineBlock', 'EngineBlock', 'EngineBlock.EngineBlockID = EngineConfig2.EngineBlockID', '' ) }"> <MvEVAL EXPR = "{ [ g.Module_Library_DB ].SQL_Query_LEFT_OUTER_JOIN( l.search_query, 'EngineConfig2', 'Mfr', 'Mfr', 'Mfr.MfrID = EngineConfig2.EngineMfrID', '' ) }"> <MvEVAL EXPR = "{ [ g.Module_Library_DB ].SQL_Query_LEFT_OUTER_JOIN( l.search_query, 'EngineConfig2', 'EngineDesignation', 'EngineDesignation', 'EngineDesignation.EngineDesignationID = EngineConfig2.EngineDesignationID', '' ) }"> <MvIF EXPR = "{ g.Assigned AND g.Unassigned }"> <MvEVAL EXPR = "{ [ g.Module_Library_DB ].SQL_Query_LEFT_OUTER_JOIN( l.search_query, 'EngineConfig2', g.Store_Table_Prefix $ 'EngineXProduct', 'ep', 'Mfr.MfrName = ep.make AND EngineBlock.Liter = ep.liter AND EngineBlock.Cylinders = ep.cylinders AND EngineDesignation.EngineDesignationName = ep.designation AND ep.product_id = ?', 'g.Product_ID' ) }"> <MvELSEIF EXPR = "{ g.Unassigned }"> <MvEVAL EXPR = "{ [ g.Module_Library_DB ].SQL_Query_LEFT_OUTER_JOIN( l.search_query, 'EngineConfig2', g.Store_Table_Prefix $ 'EngineXProduct', 'ep', 'Mfr.MfrName = ep.make AND EngineBlock.Liter = ep.liter AND EngineBlock.Cylinders = ep.cylinders AND EngineDesignation.EngineDesignationName = ep.designation AND ep.product_id = ?', 'g.Product_ID' ) }"> <MvEVAL EXPR = "{ [ g.Module_Library_DB ].SQL_Query_WHERE( l.search_query, 'ep.product_id IS NULL', '' ) }"> <MvELSEIF EXPR = "{ g.Assigned }"> <MvEVAL EXPR = "{ [ g.Module_Library_DB ].SQL_Query_FROM( l.search_query, g.Store_Table_Prefix $ 'EngineXProduct', 'ep' ) }"> <MvEVAL EXPR = "{ [ g.Module_Library_DB ].SQL_Query_WHERE( l.search_query, 'ep.product_id = ? AND Mfr.MfrName = ep.make AND EngineBlock.Liter = ep.liter AND EngineBlock.Cylinders = ep.cylinders AND EngineDesignation.EngineDesignationName = ep.designation', 'g.Product_ID' ) }"> <MvELSE> <MvFUNCTIONRETURN VALUE = "{ [ g.Module_JSON ].JSON_Response_Success() }"> </MvIF> <MvEVAL EXPR = "{ [ g.Module_Library_DB ].SQL_Query_WHERE( l.search_query, '( ( EngineBlock.Liter != \'-\' ) AND ( EngineBlock.Cylinders != \'-\' ) AND ( EngineDesignation.EngineDesignationName != \'-\' ) )', '' ) }"> <MvEVAL EXPR = "{ [ g.Module_Library_DB ].SQL_Query_GROUP_BY( l.search_query, 'EngineBlock.Cylinders, EngineBlock.Liter, EngineDesignation.EngineDesignationName, Mfr.MfrName' ) }"> <MvEVAL EXPR = "{ [ g.Module_JSON ].JSON_Filter( l.search_query, g.Filter,' liter:EngineBlock.Liter, cylinders:EngineBlock.Cylinders, designation:EngineDesignation.EngineDesignationName, make:Mfr.MfrName ') }"> <MvEVAL EXPR = "{ [ g.Module_Library_DB ].SQL_Query_OrderBy_Fields(l.search_query, g.Sort, ' liter:EngineBlock.Liter, cylinders:EngineBlock.Cylinders, designation:EngineDesignation.EngineDesignationName, make:Mfr.MfrName ', '' ) }"> <MvASSIGN NAME = "l.search_sql" VALUE = "{ [ g.Module_Library_DB].SQL_Query_Build( l.search_query, l.search_fields ) }"> <MvIF EXPR = "{ NOT [ g.Module_Library_DB ].SQL_Query_Count( l.search_query,l.total_count ) }"> <MvFUNCTIONRETURN VALUE = "{ [ g.Module_JSON ].JSON_Response_Error( g.Error_Code, g.Error_Message ) }"> <MvELSEIF EXPR = "{ NOT [ g.Module_Library_Native_DBAPI ].DB_OPENVIEW_Range( 'Merchant', 'EngineConfig2', l.search_sql, l.search_fields, g.Offset, g.Count ) }"> <MvFUNCTIONRETURN VALUE = "{ [ g.Module_JSON ].JSON_Response_Error( 'VEHICLE-00001', g.MvOPENVIEW_Error ) }"> </MvIF>
Comment