How to get rid of this error mismatched input 'line' expecting <EOF>(line 1, pos 17) in spark-sql

user_14645503 0
Viewed 1664
select case 
when line = 'KEY' 
  then case 
when length(pop)>0 and pop not rlike '^[0-9]+@.*' 
  then '' 
else case 
  when pop rlike '^[0-9]+@.*' 
    then regexp_extract(pop, '^[^@]+', 0) end 
  else '' end as pop from input

The above code throughs the error "mismatched input 'line' expecting (line 1, pos 17)"

can anyone help me with this!

apache-spark-sql apache-spark
2 Answers

Sometimes it becomes tricky when writing nested "case" statements, use a editor

like notepad++ (Language -> sql) and check if the case/end are balanced.

enter image description here

And when you select "end" there are only 2.

enter image description here

Note that "else" is optional.

From the picture it is clear that an "end" statement is missing. Try to align each case/end statement on the same vertical ruler using tabs/spaces. Now you can identify the issue easily.

Check this.

enter image description here

user_68670480

You have 3 CASE clause but only 2 END statements, you missed one more END keyword. And one CASE clause can have only one ELSE clause.

Below modified query should work for you.

SELECT
      CASE
             WHEN line = 'KEY' THEN
                    CASE
                           WHEN Length(pop)>0
                           AND    pop NOT rlike '^[0-9]+@.*' THEN ''
                           ELSE
                                  CASE
                                         WHEN pop rlike '^[0-9]+@.*' THEN regexp_extract(pop, '^[^@]+', 0)
                                         ELSE ''
                                  END
                    END
              ELSE ''
       END as pop
             FROM   input
user_87733090
Related