2 Replies Latest reply on Jan 10, 2017 4:33 AM by UrsSchuermann

    ExecuteSQL   aggregate Function and CASE


      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.