SQL CASE WHEN: is there a limit ( in number of characters )?

Question

I'm using the case when statement to group locations ( starting & destination lat, long ) into 3 regions.

Running 1 query for each region works fine.

Just bundle all the regions in a single query, the result comes empty.

select    period
        , case 
          when ( in_polygon("polygon", starting_lng, starting_lat, <coordinates_1__5739_characters> ) = True
            or   in_polygon("polygon",     dest_lng,     dest_lat, <coordinates_1__5739_characters> ) = True )
          then 'Region 1'
          end as region
        , sum(<metrics>)
from source_table
where <conditions>
group by period, starting_lng, starting_lat, dest_lng, dest_lat

select    period
        , case 
          when ( in_polygon("polygon", starting_lng, starting_lat, <coordinates_2__2471_characters> ) = True
            or   in_polygon("polygon",     dest_lng,     dest_lat, <coordinates_2__2471_characters> ) = True ) 
          then 'Region 2'
          end as region
        , sum(<metrics>)
from source_table
where <conditions>
group by period, starting_lng, starting_lat, dest_lng, dest_lat

select    period
        , case 
          when ( in_polygon("polygon", starting_lng, starting_lat, <coordinates_3__3039_characters> ) = True
            or   in_polygon("polygon",     dest_lng,     dest_lat, <coordinates_3__3039_characters> ) = True )
          then 'Region 3'
          end as region
        , sum(<metrics>)
from source_table
where <conditions>
group by period, starting_lng, starting_lat, dest_lng, dest_lat

The 3 queries above work fine. The following one results empty:

select    period
        , case 
          when ( in_polygon("polygon", starting_lng, starting_lat, <coordinates_1__5739_characters> ) = True
            or   in_polygon("polygon",     dest_lng,     dest_lat, <coordinates_1__5739_characters> ) = True )
          then 'Region 1'
          when ( in_polygon("polygon", starting_lng, starting_lat, <coordinates_2__2471_characters> ) = True
            or   in_polygon("polygon",     dest_lng,     dest_lat, <coordinates_2__2471_characters> ) = True ) 
          then 'Region 2' 
          when ( in_polygon("polygon", starting_lng, starting_lat, <coordinates_3__3079_characters> ) = True
            or   in_polygon("polygon",     dest_lng,     dest_lat, <coordinates_3__3079_characters> ) = True )
          then 'Region 3'
          end as region
        , sum(<metrics>)
from source_table
where <conditions>
group by period, starting_lng, starting_lat, dest_lng, dest_lat

Observing that the strings of coordinates are a quite long was what led me to try separating the regions.

(2 x 5,739 + 2 x 2,417 + 2 x 3,079 ) = 22,578

---

What I would like your thoughts on is:

• is there a limit for the case when statement? if so, how many characters is the cap?
• what would be a more practical solution than splitting the query into three?

---

Answer 1

this should works:

select    period
        , case 
          when ( in_polygon("polygon", starting_lng, starting_lat, <coordinates_1__5739_characters> ) = True
            or   in_polygon("polygon",     dest_lng,     dest_lat, <coordinates_1__5739_characters> ) = True )
          then 'True' else 'False' end as region1,
          case  when ( in_polygon("polygon", starting_lng, starting_lat, <coordinates_2__2471_characters> ) = True
            or   in_polygon("polygon",     dest_lng,     dest_lat, <coordinates_2__2471_characters> ) = True ) 
          then 'True' else 'False' end as region2, 
          case  when ( in_polygon("polygon", starting_lng, starting_lat, <coordinates_3__3079_characters> ) = True
            or   in_polygon("polygon",     dest_lng,     dest_lat, <coordinates_3__3079_characters> ) = True )
          then 'True' else 'False' end as region3,    
        , sum(<metrics>)
from source_table
where <conditions>
group by period, starting_lng, starting_lat, dest_lng, dest_lat