Can't cast the result of LAG or LEAD into an Integer

Vote count:

0

I have been trying to do a calculation based on the result of either the LAG or LEAD functions.

Encapsulating the function in the INTEGER() casting function seems to cause an issue with the OVER function within and throws the following error:

Unrecognized Analytic Function: INT64 cannot be used with an OVER() clause

The following is the base code that works just fine, but when I add a function, it produces an error:

LEAD(hits.hitNumber, 1) OVER (PARTITION BY fullvisitorID, visitid, visitnumber ORDER BY hits.hitNumber DESC) as nextHit

The code that I was using to produce this error is as follows:

INTEGER(LEAD(hits.hitNumber, 1)) OVER (PARTITION BY fullvisitorID, visitid ORDER BY hits.hitNumber DESC) as nextHit

The following doesn't seem to work either:

INTEGER(LEAD(hits.hitNumber, 1) OVER (PARTITION BY fullvisitorID, visitid ORDER BY hits.hitNumber DESC))as nextHit

Encountered " "OVER" "OVER "" at line 8, column 36. Was expecting: ")"

Do I really need to make this a sub-query to make this work or is there a different solution?

asked 1 min ago

hoggkm

1

Can't cast the result of LAG or LEAD into an Integer