RANK() OVER (PARTITION BY...) SQL Statement not supported in database feed?

Are you sure that MS Access supports the

RANK() OVER

function? I could find neither keyword in the list of reserved keywords for Access' SQL dialect (it does not even exist for e.g. SQL Server 2000, if I'm informed correctly).

Alternatively, it could be a construct that is not supported by the JDBC-ODBC-Bridge.

Have you tried to execute that statement directly via a dedicated JDBC driver on a SQL Server 2005 database?

As we mentioned before, we do not recommend to use the JDBC-ODBC-Bridge in productive environments, and hence cannot give real support for those cases, especially as this seems to me to be a database-specific MS Access issue. I'm happy to have another look into it if you prove me wrong :-)

Yes you are right, MS Access does not support this statement, but since I'm using the SQL server 2005 as the underlying data source, it should work, shouldn't it?

In general, how does the database feed work regarding SQL languages? In my case, does the database feed take MS Access as a reference for the statements or MS SQL Server? I'm wondering how this works in ARIS MashZone.

Maybe I do not understand your ODBC architecture correctly.

The linked tables in MS Access may lead to a SQL Server 2005 database, but the SQL query is passed to a MS Access database and is processed there, and not on the SQL Server 2005 database, or am I misunderstanding something? The error message you provided prompts me to think so.

In general, the SQL statement is passed on to the database "as-is". This means you can use database-specific statements if you are connected to a matching database - e.g. you can use the "SUBSTRING ...  USING OCTETS" function when connecting to a DB2 database, but this is not supported by e.g. Oracle which has a different syntax and different keywords for that feature.

Finally, I just tried to simulate your statement - according to the error message. It seems there is a syntax error in the statement:

SELECT STKGRP, AMT_LCY Rank()OVER(Partition by STKGRP Order by AMT_LCY DESC) 
as ‘Ranking’ FROM dbo_SALEINVOICE

should probably read (note the additional comma)

SELECT STKGRP, AMT_LCY, Rank() OVER(Partition by STKGRP Order by AMT_LCY DESC) 
as ‘Ranking’ FROM dbo_SALEINVOICE

Dear Helmut,

You got it, the SQL Query is entirely processed in MS Access. As a result I can't use the Rank() Over (Partition By....) statement.

Anyway, I could figure out a comparable solution. I'm simply using a subquery that calculates the top 5 amount. Still, this subquery takes a looong time to execute due to the massive amount of data I'm dealing with.

But it's working, and that's the most important thing :)

Thanks your reply :)

Dear Community,

is there any beginner level tutorial how to and where we can write SQL queries in ARIS Architect?

Thank you very much!

Dear Marko,

the discussion above was about ARIS MashZone where SQL statements were used to fetch data. ARIS Architect is different. It's meant to model/design processes. If offers also reporting capabilities, but based on Java script, not SQL.

I also recommend that open up a new discussion since your topic and the rest of this rather old discussion don't relate to each other.

Cheers
Runè 

Oh I see. Thanks Rune!