[Solved] I am missing some command..

[RikardSvenningsen]

Hi
I am using this link to find possible command:
http://wiki.services.openoffice.org/wik ... Procedures

I need some more command as:
if ... then .... else end.
and / or
Case statement, When .. do , and Repeat .. until.
Conversion value to string, string to value
And some simple examples to ilustrate how to use Marcro (basic) code to solve more complex problems in a query if possible

[RikardSvenningsen]

Usp..
And not to forget as I did...
Int(12.3) returning the Interger part.
And some more usefull command.

[DrewJensen]

Sorry - the person that was updating that page has just been a total slug and not added that section...yet.

You can find the original HSQLdb ( the Base embedded database engine ) documentation at http://hsqldb.org

One thing you will need to know is that the GUI part of the query designer will not recognize those constructs in your query - so to use them you switch the query designer into text mode and turn 'SQL Direct' on. Both are controlled by toolbar buttons.

HTH

[Sliderule]

RikardSvenningsen:

Just to 'add' to the answer that Drew gave above ( and Drew, thanks for all your efforts, they are really appreciated ) . . . the the 'internal' HSQL ( assuming you are using the embedded database with OpenOffice Base ) . . . the documentation for Built-in Functions and Stored Procedures is found at:

http://hsqldb.org/doc/guide/ch09.html#N1251E

For example, you asked about

if ... then .... else end

. . . the builtin HSQL syntax is:

CASEWHEN(exp,v1,v2)

if exp is true, v1 is returned, else v2)

I hope this helps.

Sliderule

[DrewJensen]

Just an aside also -

The HSQLdb team has stated that they will be releasing version 1.9 of the database engine in a mater of days ( not an exact date however ).

One of the points of interest will be a plSQL style command processor - so for really complex stuff one could use a stored procedure. Not to mention triggers that don't require java ( I hope ).

With any luck this will happen in time to get it into the 3.0 release of OO.o.

[RikardSvenningsen]

Thanks to all of you
To Drew:
I guess you mean some command and SQL syntax is accepted by the SQL GUI designer, and the text SQL editor (also selected from the menu) is used for the rest of the command.
Just to verify, the direct way, when run sql, you can se the table on top and the SQL command i the lower part of the window.
Should all the command from the HSQL base be used on the build in base in OpenOffice (ODB Base)?

To Sliderule:
Thanks, the link was the missing command, I guess that the also need to be enter in the text SQL editor and tested in direct mode?

To Drew, on the last comment.
The plSQL looks to be interesting (google "plsql") found on wiki seems to be very structured.

was it possible to use some Macro / Basic routine as function or procedure in a query?

And thanks again great..

[RikardSvenningsen]

To Drew and Sliderule
This is outstanding...
With the link from Sliderule and the SQL info from Drew about the differece between SQL Query in design mode and SQL in text mode, the preferd mode will be Text mode in that it takes all the command from the link.
Great, better than MS Access.....