CODEWITHSUNDEEP
google-bigquery
user3769827
user3769827

Reputation: 407

BigQuery Error: Encountered " "OVER" "OVER ""

I tried to execute this sql statement in BigQuery console but I got an error as below.

Anyone can suggest on this?

"Error: Encountered " "OVER" "OVER "" at line 5, column 99. Was expecting: "END" ..."

SELECT      *
FROM        (SELECT
                  *
                        , CASE 
                            WHEN initiator in ('01_User') THEN RANK() OVER (PARTITION BY item_id, log_date_desc, type ORDER BY log_date_desc DESC)
              ELSE RANK() OVER (PARTITION BY item_id, log_date_desc ORDER BY log_date_desc  DESC) 
              END AS ord
            FROM        temp.step1_itemlogall
            ) AS t
WHERE       ord = 1

Thanks.

Upvotes: 2

Views: 1309

Answers (2)

Paul Kenjora
Paul Kenjora

Reputation: 2004

Switch to STANDARD SQL in the UI or the API call, only the legacy SQL engine throws this error.

Upvotes: 0

Jordan Tigani
Jordan Tigani

Reputation: 26617

I think the issue is with putting analytic functions inside a CASE statement. You can rewrite this as an inner select that computes those two functions, then selects the right one in the outer query. For example:

SELECT *
FROM (
  SELECT    
    CASE WHEN initiator IN ('01_User') 
    THEN ord1
    ELSE ord2    
    END AS ord
  FROM (  
  SELECT *,
      RANK() OVER (PARTITION BY item_id, log_date_desc, type
                   ORDER BY log_date_desc DESC) as ord1,
      RANK() OVER (PARTITION BY item_id, log_date_desc 
                   ORDER BY log_date_desc  DESC) as ord2
    FROM temp.step1_itemlogall))
WHERE ord = 1

I think the real explanation for that error message is here, however: https://www.youtube.com/watch?v=2OBZf0QdKdE

Upvotes: 4

Related Questions