SQL Query works in SQL Developer, but not always in MII

[Former Member]

Hi all,

I encountered a strange behaviour with a query in MII 12.0.2. Maybe someone has a guess what happens.

I have created a SQL query which runs against Oracle 10g tables. I have tested the query using SQL Developer, and it throws a couple of lines, depending on the contents of the where clause.

Next I have copied the query to a MII SQL Query (FixedQuery). However, the output is empty most of the time, without showing any errors. After some testing I got the impression that older data are not displayed, but there is no time or date setting in MII.

As the SQL Developer always returns rows, I am unsure where to search for the error.

Regards

Michael

[jcgood25 · Accepted solution]

Michael,

I would imagine that you have sub-select statements in your FixedQuery, all which will fall subject to the RowCount property of the query template (SQL defaults to 100), which is issued through the driver and typically honored by the database when returning the data from your request.

Most of the native database query tools allow you to make unbound query requests with no limit on rows, which would probably account for the difference between SQL Developer and the query template.

For SQLServer it's ROWCOUNT: http://msdn.microsoft.com/en-us/library/ms188774.aspx

For Oracle it's ROWNUM: http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

So the answer would be not to make the query template row count some rediculous number, but more appropriately refine the way that the database request is issued.

Regards,

Jeremy

[Former Member · Accepted solution]

Jeremy,

thanks for your response. The rownum article was very interesting!

However, this morning I found the reason for the different display in SQl Developer and MII. Yesterday I have changed some data in the queried tables with update queries issued in SQL Developer, but forgot the final Commit. So the changes were visible in Developer, but not in MII...

So everything works fine. Sorry for the confusion.

Regards

Michael