[Solved] Limiting a SQL Search

[Nocton]

I wish to do have an SQL statement (to go in a form) which limits the number of records shown to 1 to 10, 11 to 20 and so on. None of the LIMIT and TOP commands that I have used in other applications work, even though one post I found http://www.oooforum.org/forum/viewtopic.phtml?t=64460 seemed to suggest that they do work.

Does anyone have a solution?

Regards, Nocton

[probe1]

This WFM (OOo 3.2.1/openSuse, embedded HSQLDB, SQL direct enabled)

For first 10 records, saved as qry_Limit_step_1

Code: Select all

SELECT "Name" FROM "tab_kunden" ORDER BY "id" LIMIT 10

record 11-20 qry_Limit_step_2

Code: Select all

SELECT "Name" FROM "tab_kunden" ORDER BY "id" LIMIT 10 OFFSET 10

record 21-30 qry_Limit_step_3

Code: Select all

SELECT "Name" FROM "tab_kunden" ORDER BY "id" LIMIT 10 OFFSET 20 

The ORDER BY clause is essential


To switch the queries I use a control element (Pushbutton) and change the form command

Code: Select all

Sub frm_limit_step( oEvent )
oEvent.Source.Model.Parent.Command = "qry_Limit_step_2"
oEvent.Source.Model.Parent.reload()
End Sub

You can remember the query name in a global variable, or use a counter

Code: Select all

limit_step = limit_step + 1
oEvent.Source.Model.Parent.Command = "qry_Limit_step_" & limit_step

(both initialize on form open), or use a numeric field (spin button) to change the interval value...

Or use

Code: Select all

oEvent.Source.Model.Parent.CommandType = 3

and create the query SQL on the fly (putting the SQL in .COMMAND again)


Does this help?

[Nocton]

Thank you Winfried. However, I always get an error when I run the SQL command placing it in the Content section of my subform.

My syntax is:

Code: Select all

SELECT DISTINCT "EmailAddress" FROM "MemberDetails" AS "MemberDetails" WHERE "EmailAddress" IS NOT NULL ORDER BY "EmailAddress" ASC LIMIT 10

If I run it in Tools/SQL to execute it directly, it says it executes successfully. But it will not run in the form, nor can I save it in a query, because it always says there is a syntax error.

[probe1]

Which database you use (see statusbar of ODB)

I can save your query here... (HSQLDB).
Once saved you can use it for a form...

You checked/ activated the Run SQL command directly icon in the toolbar?

[Nocton]

Maybe the problem is because I am using 'file server' mode (see the Quick Fix http://www.oooforum.org/forum/viewtopic ... 523#396523.
As I could not wait for a resolution of this problem, which appears to depend on which version of SQL one is using, I have got round the problem by using Basic code to limit the number of records. But it would be nice to know how it can be done in the SQL that I am using.

[Sliderule]

Norton:

The problem you are describing is NOT an HSQL ( database engine ) issue, but rather, an OpenOffice Base Parser issue.

You see, the OpenOffice Base Parser will first check the SQL before sending it to the database engine. However, the OpenOffice Base parser does NOT recognise ( accept ) use of LIMIT or TOP in an SQL statement.

Therefore, if you write the Query, and, save it to Run Directly ( so the OpenOffice Base Parser is NOT first exeucted ) . . . it will work. The job of for the OpenOffice Base Parser, is to 'convert' dates, to ISO standard ( YYYY-MM-DD ) if entered in local format ( same for time values ), AND, to allow a Parameter Query, to allow a user to pass values to the SQL ( accomplished with a colon ), for example:

Where "MyField" = :Enter_Search_Text

Bottom line, the valid SQL will work, but, you have to implement it so it is passed directly to the database engine, rather than allowing the OpenOffice Base Parser to first check it.

I hope this helps, and, is as clear as mud. Please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[probe1]

[Tue Oct 04, 2011 11:16 pm]SQL direct enabled

[Wed Oct 05, 2011 12:56 am]You checked/ activated the Run SQL command directly icon in the toolbar?

That's what I wrote two times before

[Nocton]

Thank you sliderule for explaining the situation very clearly. I have tried it out and it works as you say. Winfried, you also said what to do, and I thought I had run the SQL directly. However, I had started with my SQL statement directly embedded in the form, without a saved query and clearly got confused with the 'Analyze SQL command' which does the same job I think.

So I've got there in the end!

Regards

Nocton

[DatFrame]

I know this post is very old, but I just wanted to post an alternative solution I found:

I wanted to limit the query results as well, however I wanted to use other base functions that required "Run SQL command directly" to be OFF (for example, you cannot use form filters when running SQL directly). I found a property of the row set called "MaxRows" that allows you to limit the number of rows returned without using SQL, see http://www.openoffice.org/api/docs/comm ... ml#MaxRows.

Here is a little code sample for how to limit the records returned by a Base form:

Code: Select all

Dim oForm as Object
Set oForm = thisComponent.DrawPage.Forms.getByName("MyForm")

REM The max rows property will limit the number of rows you can scroll to
oForm.setPropertyValue("MaxRows",30)

REM If you want all of these rows to be fetched at once, change the FetchSize as well:
oForm.setPropertyValue("FetchSize",30)

REM You will need to reload the form in order for these properties to take effect
oForm.reload()

Set oForm = Nothing

I did have some issues with the properties being set, as sometimes oForm.reload() did the job, and sometimes it seemed that the form did not recognize it's new MaxRows limitation.

Hope this helps anyone out there like me!