Announcement

Collapse
No announcement yet.

GROUP BY Count Issue?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    GROUP BY Count Issue?

    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>
    Chris Dye
    http://www.kseriesparts.com

    #2
    l.total_count is what is producing the wrong count.
    Chris Dye
    http://www.kseriesparts.com

    Comment

    Working...
    X