Dear Community,
I'm impressed by the powerful database feed and the way how to execute queries. But I'm having issues with the RANK() OVER (PARTITION BY) statement, which does not work in the database feed query field.
The error message:
The following error occurred when running SQL query "SELECT STKGRP, AMT_LCY Rank()OVER(Partition by STKGRP Order by AMT_LCY DESC) as ‘Ranking’ FROM dbo_SALEINVOICE" on database "Access": [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'AMT_LCY Rank()OVER(Partition by STKGRP Order by AMT_LCY DESC)'.
The underlying database is MS Access which again uses linked ODBC tables to gather the data from SQL Server 2005.
I haven't had any bigger problems with this setup so far, but this problem is really tough.
Here's a brief introduction into the RANK() OVER statement:
http://riteshshah.wordpress.com/2009/03/03/use-of-rank-and-partition-by-clause-in-sql-server-2005/
Thank you very much!
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 :)
Mon, 05/23/2022 - 13:20
In reply to Dear Community, by Markobe0824
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è
