AnsweredAssumed Answered

ExecuteSQL   aggregate Function and CASE

Question asked by UrsSchuermann on Jan 10, 2017
Latest reply on Jan 10, 2017 by UrsSchuermann

I am trying to count some records and mark them with a Case statement. In Standard SQL-92 this works in FM not.


SELECT  s.ShipperName  , count (o.OrderID) ,


            WHEN  count (o.OrderID) between  50 and 59  THEN  '**'

            WHEN  count (o.OrderID) between  60  and 69 THEN  '***'

            WHEN  count (o.OrderID) between  70 AND 89 THEN '****'


  FROM Orders as o LEFT JOIN Shippers as s ON o.ShipperID=s.ShipperID

                 GROUP BY s.ShipperName

                 ORDER BY count( o.OrderID) DESC


So I took the statement apart and tried the aggregation part in ExecuteSQL:


      SELECT s.ShipperName    , count (o.amount)

                      FROM Orders AS o

                      JOIN Shippers as s   ON o.ShipperID=s.ShipperID

                      GROUP BY  s.ShipperName         

                      ORDER BY  s.ShipperName DESC


as well as  the searched the case statement



       SELECT s.ShipperName , o.amount ,


                                                                              WHEN   o.amount  < 100  THEN '*'

                                                                              WHEN   o.amount  between  100 AND 499 THEN '**'

                                                                               WHEN  o.amount  between 500  AND 700 THEN '***'

                                                                               ELSE   '****'



                      FROM Orders AS o  JOIN Shippers as s   ON o.ShipperID=s.ShipperID

       ORDER BY o.amount


Does anyone has an idea why the combination of both aggregation (count) and 'searched case'  not works.

Help is really appreciated.