RAQL Syntax Question - "RANK() OVER (PARTITION BY..." Equivalent

,
1

Team,

I am a new to developing in MashZone NG 10.3 and am trying to learn the subtle differences in RAQL from standard TSQL or PL/SQL. I have the the “User & Developer Guide” for my version and it has a pretty extensive section on RAQL syntax, but very few code examples. The syntax charts are, for me anyway, difficult to fully understand, so I need some help. I can write complex TSQL in my sleep, and know PL/SQL fairly well. If anyone knows of any good resources for learning RAQL syntax, please share!

Now to my question: I’m trying to write a RAQL Query that does a RANK() that’s partitioned by an identifier. In TSQL, this would be simple:

RANK() OVER (PARTITION BY Field1 ORDER BY Field2 ASC, Field3 DESC) AS FieldName

However, in MashZone, it’s ok with

RANK() OVER (ORDER BY Field2 ASC, Field3 DESC)

but it doesn’t like the addition of the “PARTITION BY” clause. the documentation seems to indicate that you can RANK by partitions, but I cant quite make out how it’s supposed to be written. Anyone out there able to help?

Thanks!

John

2

Well, I can’t figure out how to delete my post, so I’ll answer it instead. I found that the “PARTITION BY…” syntax was fine… it was a “SUBSTRING” function I was using in the PARTITION that was the problem. My original statement was

RANK() OVER (PARTITION BY SUBSTRING(Field1, 1, 5) ORDER BY Field2)

which seemed to be broken because of the PARTITION. However, I found that in RAQL, the syntax for SUBSTRING is actually

SUBSTRING(Field1 FROM 1 FOR 5)

How weird! But it works fine now.

Hope this is helpful to someone!

Thanks,

John