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.

- is there a C++ equivalent to SQL DENSE_RANK() window function?
- How jump to value in INSERT statement in IntelliJ IDEA query console?
- cume_dist vs percent_rank or difference between
- JOIN for specific row number
- How to Update one tables value with another tables value by joining them?
- Columns participating in a foreign key relationship must be defined with the same length and scale
- remove text between square brackets
- Join a column in a table to multiple columns in another table
- Compare two columns and if not equal, set them equal based off a condition?
- Properly implement DENSE_RANK() or similar function
- Combine values in column week (i.e. week 1, week 2, etc) into week range (week 1-2 or week 1, 2)?
- Get week range (week 1-2 or week 1, 2) for each week where item has the most sales out of items list
- how to Avoid self-join in spark scala
- Formatting Numbers by padding with leading zeros in SQL Server
- PostgreSQL doesn't take into account a DELETE in WITH before the request has ended
- Does pyodbc support any form of named parameters?
- Delete all data in SQL Server database
- Must declare the scalar variable
- Running total over duplicate column values and no other columns
- sqlite and 'constraint failed' error while select and insert at the same time
- How can I use ROW_NUM() to pick one instance where it doesn't have a second record?
- How create a SQL array from a Java List?
- Using SQL to get the SUM of X records
- Oracle SQL foreign key not working
- Hack-A-Rank not accepting mod(column, 2) = 0;
- SAS (PROC SQL) Merge/Join to dates prior and only keeping the nearest entry
- Get selected values after joining multiple tables in Entity Framework 6 using Lambda
- Why does not Hibernate set @DynamicInsert by default
- PHP Problems using NULL with bindvalue
- Need to create a procedure to get the info of all the table that has the column name which is entered in the parameter of the procedure