Grouped Data: type mismatch

Dean Flinter shared this problem 14 months ago
Defect Logged

Hi,

I am trying to create some data groups in report builder. The data I am trying to group is from a freehand SQL calculated field which is the result of a datediff function on two timestamps in the dataset (in minutes). The result is a numeric type but I want to group into things like "1-2 hours", "2-4 hours" etc

I can create the groups just fine but the query generated fails as the opening and end clauses in the CASE WHEN statement are the freehand SQL function itself, which returns a numeric value while the rest are the group labels, which are text.

It seems that Snowflake does not like this arrangement as it returns an error saying that the group labels are not a numeric type. Testing on DBFiddle suggests that Postgres behaves the same however MySQL seems to not mind

Is there anyway to alter the opening and end clauses or otherwise change how the generated SQL behaves? I tried putting the statement as it's own freehand SQL field, which allows the query to run but the sorting of the data thereafter is based on the text values and not the underlying data

Below is a snippet of the statement being generated, for reference

SELECT DISTINCT
   CASE
      WHEN 1 = 0 THEN datediff(minute, timestamp_a, timestamp_b)
      WHEN datediff(minute, timestamp_a, timestamp_b) <= 10 THEN '10 Minutes'
      WHEN datediff(minute, timestamp_a, timestamp_b) BETWEEN 10 AND 30 THEN '10-30 Minutes'
      WHEN datediff(minute, timestamp_a, timestamp_b) BETWEEN 30 AND 60 THEN '30-60 Minutes'
      WHEN datediff(minute, timestamp_a, timestamp_b) BETWEEN 60 AND 120 THEN '1-2 Hours'
      WHEN datediff(minute, timestamp_a, timestamp_b) BETWEEN 120 AND 360 THEN '2-6 Hours'
      WHEN datediff(minute, timestamp_a, timestamp_b) BETWEEN 360 AND 1440 THEN '6-24 Hours'
      WHEN datediff(minute, timestamp_a, timestamp_b) BETWEEN 1440 AND 2880 THEN '1-2 Days'
      WHEN datediff(minute, timestamp_a, timestamp_b) BETWEEN 2880 AND 8640 THEN '2-6 Days'
      WHEN datediff(minute, timestamp_a, timestamp_b) BETWEEN 8640 AND 20160 THEN '1-2 Weeks'
      WHEN datediff(minute, timestamp_a, timestamp_b) BETWEEN 20160 AND 40320 THEN '2-4 Weeks'
      WHEN datediff(minute, timestamp_a, timestamp_b) > 40320 THEN '4 Weeks+'
      ELSE datediff(minute, timestamp_a, timestamp_b)
   END AS C1,

Comments (3)

photo
1

Hi Dean,

Thanks for reaching out. Unfortunately, this is a known defect in Snowflake. I've gone ahead and added you to the client list of those being affected by this in the existing logged Defect. There is no workaround listed in the ticket, I'm afraid.

Any potential updates regarding this will be posted here. The task is a bit old now, so I've explicitly requested an update as well.

Regards,

Mike

photo
1

Thanks Mike

I've found a bit of workaround myself

I adjusted the above CASE statement so that it returns all text values and added the whole bit as a freehand SQL calculated field (as I did before). Then I created another calculated field which returns just the results of the function itself. I then grouped this data in the exact same way but for the labels I put in numbers rather than text

The funny thing is that the SQL statement generated has those numbers as text i.e. '1' but Snowflake is happy to take them. In order to sort properly, anything after 9 is set as 99, 999 etc and Yellowfin then sorts in the order I want

It's a little messy but it does the job. Ideally we'd want the native functionality to work, especially if we wanted a lot more groups so thanks for adding us as an affected client. If it helps with the priority, as I mentioned it appears the same is true for Postgres and I would suggest it might be the same for Redshift as that is a fork of Postgres (although heavily modified). I don't have access to Redshift anymore so I can't confirm


Thanks

Dean

photo
1

Hi Dean,

Glad to hear you found a workaround! I've added this workaround to the task, but still explicitly stated that we get this working as expected considering the workaround is a bit messy, and is still ultimately just that: a workaround. I agree it likely to occur in Redshift as well and I've made note to test that in the course of looking at this as well, so thanks for the additional feedback!

I'll keep you posted with any progress made here.

Regards,

Mike