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

  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.

Outcomes