Search code examples
sqlcasehana

How to make case in HANA


I have the following query in postgres which gives the meter range and the number of vehicles travelled between the range

SELECT (CASE WHEN meter >= 0 AND meter< 10000 THEN '0-10' 
             WHEN meter >= 10000 AND meter < 20000  THEN '10-20' 
             WHEN meter >= 20000 AND meter <30000 THEN '20-30' 
             WHEN meter >= 30000 AND meter < 40000 THEN '30-40' 
             WHEN meter >= 40000 AND meter < 50000 THEN '40-50' 
             WHEN meter >= 50000 AND meter < 60000  THEN '50-60' 
             WHEN meter >= 60000 AND meter <70000 THEN '60-70' 
             WHEN meter >= 70000 AND meter < 80000 THEN '70-80' 
             WHEN meter >= 80000 AND meter < 100000 THEN '80-90' 
             WHEN meter >=100000 THEN 'above 100'  
       END) as range,count(*) as vehicle_count 
        from ( 



      SELECT extract (day from time) as day, place,vehicle_id,sum(meter_two_points) as meter 
FROM public.datatable where time >= '2015-09-05 00:00:00' and time <= '2015-09-05 23:00:00' and place=10 
  group by day,vehicle_id,place 
  order by day,vehicle_id  



)  as A group by range  order by range limit 10

I now want to do the same query for the same table in HANA.So I tried with

 SELECT (CASE   WHEN meter >= 0 AND meter< 10000 THEN '0-10' 
                WHEN meter >= 10000 AND meter < 20000  THEN '10-20' 
                WHEN meter >= 20000 AND meter <30000 THEN '20-30' 
                WHEN meter >= 30000 AND meter < 40000 THEN '30-40' 
                WHEN meter >= 40000 AND meter < 50000 THEN '40-50' 
                WHEN meter >= 50000 AND meter < 60000  THEN '50-60' 
                WHEN meter >= 60000 AND meter <70000 THEN '60-70' 
                WHEN meter >= 70000 AND meter < 80000 THEN '70-80' 
                WHEN meter >= 80000 AND meter < 100000 THEN '80-90' 
                WHEN meter >=100000 THEN 'above 100'  
           END) as range,count(*) as vehicle_count 
            from ( 


            But it throws with the error `invalid column name range`
          SELECT to_integer(to_varchar(time,'DD')) as day, place,vehicle_id,sum(meter_two_points) as meter 
    FROM public.datatable where time >= '2015-09-05 00:00:00' and time <= '2015-09-05 23:00:00' and place=10 
      group by day,vehicle_id,place 
      order by day,vehicle_id  



    )  as A group by range  order by range limit 10

Solution

  • You may change to this:

    SELECT range, 
        COUNT(*) AS vehicle_count 
    FROM (
        SELECT (CASE   WHEN meter >= 0 AND meter< 10000 THEN '0-10' 
                    WHEN meter >= 10000 AND meter < 20000  THEN '10-20' 
                    WHEN meter >= 20000 AND meter <30000 THEN '20-30' 
                    WHEN meter >= 30000 AND meter < 40000 THEN '30-40' 
                    WHEN meter >= 40000 AND meter < 50000 THEN '40-50' 
                    WHEN meter >= 50000 AND meter < 60000  THEN '50-60' 
                    WHEN meter >= 60000 AND meter <70000 THEN '60-70' 
                    WHEN meter >= 70000 AND meter < 80000 THEN '70-80' 
                    WHEN meter >= 80000 AND meter < 100000 THEN '80-90' 
                    WHEN meter >=100000 THEN 'above 100'  
               END) AS range
        FROM ( 
            SELECT to_integer(to_varchar(time,'DDMMYYYY')) AS day, 
                --You should considering using other way to truncate date from time, 
                --not convert too much,
                --at least you could use only to_varchar is enough
                place, 
                vehicle_id,
                SUM(meter_two_points) AS meter 
            FROM public.datatable 
            WHERE time >= '2015-09-05 00:00:00' 
                AND time <= '2015-09-05 23:00:00' 
                AND place=10 
            GROUP BY to_integer(to_varchar(time,'DDMMYYYY')), 
                vehicle_id, 
                place 
        )  
    ) AS a
    GROUP BY range  
    ORDER BY range 
    LIMIT 10;       --why limit 10 here while you only have 10 ranges?
    

    And as SQL order of operations, I keep wondering if PostgreSQL could GROUP BY after SELECT so group by could group with range column in your first query.