[Solved] I am missing some command..

Creating tables and queries

[Solved] I am missing some command..

Postby RikardSvenningsen » Tue Jan 15, 2008 2:27 pm

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
Last edited by RikardSvenningsen on Wed Jan 16, 2008 4:11 pm, edited 1 time in total.
Best regards
Rikard Svenningsen
Denmark
RikardSvenningsen
 
Posts: 16
Joined: Mon Jan 14, 2008 3:17 pm

Re: I am missing some command..

Postby RikardSvenningsen » Tue Jan 15, 2008 2:36 pm

Usp..
And not to forget as I did...
Int(12.3) returning the Interger part.
And some more usefull command.
Best regards
Rikard Svenningsen
Denmark
RikardSvenningsen
 
Posts: 16
Joined: Mon Jan 14, 2008 3:17 pm

Re: I am missing some command..

Postby DrewJensen » Tue Jan 15, 2008 2:48 pm

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
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: I am missing some command..

Postby Sliderule » Tue Jan 15, 2008 5:06 pm

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:

HSQL documentation wrote:CASEWHEN(exp,v1,v2)

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


I hope this helps.

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1167
Joined: Thu Nov 29, 2007 9:46 am

Re: I am missing some command..

Postby DrewJensen » Tue Jan 15, 2008 5:13 pm

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.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: I am missing some command..

Postby RikardSvenningsen » Tue Jan 15, 2008 9:35 pm

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..
Best regards
Rikard Svenningsen
Denmark
RikardSvenningsen
 
Posts: 16
Joined: Mon Jan 14, 2008 3:17 pm

[SOLVED] Re: I am missing some command..

Postby RikardSvenningsen » Tue Jan 15, 2008 10:37 pm

To Drew and Sliderule
This is outstanding... :roll:
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.....
:D
Best regards
Rikard Svenningsen
Denmark
RikardSvenningsen
 
Posts: 16
Joined: Mon Jan 14, 2008 3:17 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 6 guests