Syntax of using case in grouping sets

Question

The Hive query snippet in question is as below:

group by
  case
    when inte.subId is not null then 'int'
    else 'ext'
  end,
  taskType,
  result
grouping sets(
  (
    case
      when inte.subId is not null then 'int'
      else 'ext'
    end,  -- line 36
    taskType,
    result
  ),  -- line 39
  (
    taskType,
    result
  )
)

The log suggests some syntax error at line 36 and 39:

FAILED: ParseException line 36:7 missing ) at ',' near ')'
line 39:3 missing EOF at ',' near ')'

Any idea? If you need more info from me, feel free to comment.

@GordonLinoff: Are you suggesting that `case` cannot be used within `grouping sets`, but only column names? — Lingxi, May 25 '17 at 02:15
. . I don't know. But if you need to get something done, then defining the column in a subquery solves the problem quickly. — Gordon Linoff, May 25 '17 at 02:16
@GordonLinoff: Agreed. I will give it a try and let you know the result :) — Lingxi, May 25 '17 at 02:18
@GordonLinoff: Problem solved. Would you mind posting an answer so I can vote up and accept? If not, I will post one and close the thread myself. Credit goes to you anyway :) — Lingxi, May 25 '17 at 02:43

score 0 · Accepted Answer · answered May 25 '17 at 03:03

OK, here is the solution suggested by @GordonLinoff in the comment. Basically, the idea is to use a sub-query where a new column Category is selected as

case 
  when inte.subId is not null then 'int'
  else 'ext'
end as Category

Then, in the outer main query, the group part is simply

group by Category, Type, Result
grouping sets(
    (Category, Type, Result),
    (Type, Result)
)

As to why the syntax error in the question, we don't know for sure. Maybe, case cannot be used within grouping sets.

Syntax of using case in grouping sets

1 Answers1