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

    ExecuteSQL   aggregate Function and CASE

    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) ,

        CASE

                  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 '****'

        END

        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 ,

                                                                         CASE

                                                                                    WHEN   o.amount  < 100  THEN '*'

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

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

                                                                                     ELSE   '****'

                                                                         END

                  

                            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.