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

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

    Comment

    Working...
    X

    This website uses cookies to identify visitors, track visitors to our website, store login session information and to remember your user preferences. By continuing to use this site you agree to our use of cookies. Learn More.

    This website uses cookies. By continuing to use this site you agree to our use of cookies. Learn More.

    Accept