Query Builder error ROW_NUMBER() OVER

Question

Hi,

I am having trouble with this input tool Query in Alteryx:

SELECT
billing_id
,_id
, ROW_NUMBER() OVER(PARTITION BY billing_id ORDER BY date) AS RN
FROM Test.invoice

If I paste this into the SQL editor, it runs fine, But If I try to switch to the visual query builder view, I get this error message:

Parsing Error

Invalid SELECT statement.

unexpected token "(" at line 4, position 20

I am using Alteryx version 2019.3.5.17947 and accessing Vertica

I know Query Builders is extremely fully about syntax, but I can't figure it out for this one. Any help would be appreciated.

Novakm · Answer

This is actually part of a much larger query someone sent me that normalizes and joins a bunch of tables. I would prefer not to have to recreate the whole thing in Alteryx.

mceleavey · Answer

Hi @Novakm ,

Is there any reason for you applying the ordering part of the statement in the query?

Why not simply select the fields in the select statement and stream the results into Alteryx? You can then change the name and order by whatever fields you need. No need to code that.

M.