Hi,
I have a database with simple query. It has 3 columns: Seller_Id; Doc_Id (invoice number); Product_Id
Database is MS SQL 2008 R2. I connect to this database using jtds.jdbc driver.
I found some bug in Base Report. I create report using SQL command.
If I use “Direct SQL” then Base doesn’t sort results.
If I use “Normal mode” (I don’t know what is name for it) then everything is ok.
But I need to use subquery with TOP command and I’m not able to do this in normal mode.
After many hours struggling with this I found some solutions but I’m not very happy with them.
If I use normal mode I need to use complicated solution with SELECT and MAX to retrive TOP row. It is not elegant nor effective.
If I use “Direct SQL” I need to sort results, because Base doesn’t do it. I can’t use ORDER BY because MS SQL doesn’t allow this in inner select. And probably base treat this whole SQL query as subquery. To workaround this I need to use SELECT TOP 99999999 (some big number) and ORDER BY and the end. It not elegant as well.
Should I report a bug or I made some mistakes?
For example if I want to sort and group by seller:
query:
Seller_Id; Doc_Id (invoice number); Product_Id
1;1;1;
1;1;2;
2;2;1;
1;3;3;
1;4;4;
And the result in base report is:
Group: Seler 1
1;1;1;
1;1;2;
Group: Seler 2
2;2;1;
Group: Seler 1
1;3;3;
1;4;4;
If I try to sort it in query (using “order by” ) in “Direct mode” I get error.
“The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.”
It is because I want to order in inner select and this is prohibited.