[Solved] Retaining SQL layout when coding

[Matt123]

Is there a way of retaining or displaying your code layout in Base?

Code: Select all

eg:
SELECT 
   xxxxx,
   xxxxx
WHEN
   xxxxx
FROM
   xxxxx

Rather than constantly viewing the monolithic block.

Cheers

[Sliderule]

Yes.

If the SQL Query ( Select statement ) does NOT go through the Base Parser . . . it will retain the 'format' of the SQL including comments ( starting with two dashes ) and line fields.

Put another way, if you send the SQL directly to the database engine, Base will keep it exactly as you have written it.

There is ONE thing you loose by NOT invoking the Base Parser. That is, if you want your Query to PROMPT the user for input / substitution ( sometimes called, a Parameter Query ) . . . you MUST allow the Base Parser to be used.

Now, just to be alittle clearer, if you do NOT want Base to first Parse the SQL, EITHER :

1. From the Toolbar, press the Run SQL command directly icon ( word SQL and green check mark )
2. From the Menu: Edit -> Run SQL command directly

When you save your SQL with EITHER of the two options above, it will come back to you exactly as written, including comments ( starting with two dashes ( -- ) if desired.

For example, a Query that is run directly, could be saved and displayed like this, WITH COMMENTS :

Code: Select all

-- Query to return items based on the value of "MyField"
-- Just wanted to add some comments to show how they might look
SELECT
   "MyTable1"."MyField1" as "Sample",
   "MyTable2"."MyField6" as "Have a nice day"

FROM "MyTable1",
     "MyTable2"

WHERE "MyTable1"."MyField" = "MyTable2"."MyField"   -- Another comment for the fun of it

I hope this helps, 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.

[Matt123]

Hmm, it keeps the formating ok. But... I'm getting "Data load error" which when I go into more detail, tells me "Table not found in statement."

I'm picking thats because this example is a query calling on other queries...

& yep, when I try it on a query using only tables, it works fine.

Thoughts? Do I need to learn to use VIEW (recall seeing it mentioned somewhere?)

Cheers

[Sliderule]

You said / asked:

Do I need to learn to use VIEW (recall seeing it mentioned somewhere?)

Yes.

A Query is stored in the OpenOffice Base file, just as Reports and Forms are a part of the OpenOffice Base file.

However, Tables and Views are a part of the Database Driver ( HSQL in your case ). Since HSQL does not know about the OpenOffice Base part, you can accomplish the 'task' by creating a View from the Query(ies) of your choice.

Sliderule

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

[Matt123]

Ok, something new to learn. Later...

Meantime the majority of my code will retain its formating

Thanks for the help.

Cheers!

[Ridgeland]

Thanks Matt123 and Sliderule.

[Ridgeland]

Ouch!
I just ran in circles and finally discovered that when your query uses another query rather than a table then
"Run SQL Command Directly"
gives cryptic error messages that don't make any sense to me.
But turn off the "Run SQL Command Directly" and save the query and voila it works.
Why make it so hard to have your sql statement be formatted in a human friendly way??!!

[Sliderule]

Put another way, if you send the SQL directly to the database engine, Base will keep it exactly as you have written it.

I just ran in circles and finally discovered that when your query uses another query rather than a table then
"Run SQL Command Directly"
gives cryptic error messages that don't make any sense to me.
But turn off the "Run SQL Command Directly" and save the query and voila it works.
Why make it so hard to have your sql statement be formatted in a human friendly way??!!

Exactly. OpenOffice / LibreOffice Base is the database front-end, NOT the database back-end ( such as, HSQL, MySql, Access, H2, SQLite, PostGreSQL, etc ). Tables and Views are stored ( as part of ) the database driver, that is, the database back-end. That way, you can use it, ( your data, tables, views etc ) outside of OpenOffice / LibreOffice Base when it is configured correctly.

However, Queries are stored as a part of your *.odb file, that is, the database front-end, not as a part of the the database back-end.

Therefore, when you use, Run SQL Command Directly, what is sent to the database back-end, since, your stored Query is NOT a part of the database back-end, it cannot run.

Solution: Use ANY of the following, alternatives, if you wish to keep formatting ( line-breaks / comments etc ):

1. Save the Query you want included as a View. That way, since a View is a part of the database back-end, you can use it in your new query, and, send it to the database back-end directly.
2. Include the entire SQL for the OTHER query, surrounded by parentheses, as a part of the From clause, in the NEW Query. That way, the database back-end will have the needed Select statements you want.

I hope this helps, please be sure to let me / us know.

Sliderule

[Ridgeland]

Excellent!
Thanks Sliderule
The () works for me. I don't care for creating a view by going back into the GUI click and drag setup.
The () approach simplifies the list of queries since now there is one query and before there were two. I did not know about the comments -- until reading this topic. Now I have a sql formatted to be easier to read and understand 12 months from now. And I have comment lines that explain what the query does and why.

[Sliderule]

The () works for me. I don't care for creating a view by going back into the GUI click and drag setup.

Just as an FYI ( For Your Information ) and, for others reading this forum post.

Creating a View within your OpenOffice / LibreOffice Base file ( *.odb ) requires NO click and drag setup. Just follow these instructions once, and, your newly created View name will show up under the Tables, and, may be used just like a table when writing Queries .

1. Start your OpenOffice / LibreOffice Base file ( *.odb )
2. On the left, under Databases, click on the Queries icon
3. Under the list of your Queries, bottom right side of display, right-click on the Query you want to make the View from
4. From the Pop-up menu, choose: Create as View
5. From the Pop-Up dialog, assign a unique name of your choice for the new View and Press the OK button. My standard, is to end any View name with _VIEW , but, YMMV ( Your Database/Mileage May Vary )

Sliderule

[Ridgeland]

Thanks again!
I tried a couple of times and did not have "Create as view" in the pop-up menu when I right click on a query name. But with the query highlighted from the pull down menu Edit->Create as View worked. After doing that now I find "Create as view" when I right click on a query. User error? Glitch? doesn't matter.

Now I have the answer to a question I would have created a new thread for.
How to format the output of the sql select statement?
Create a view.
FORMAT(field,format) doesn't work - gives "access is denied" error. ABS(field) works, both get green font color in sql edit. With the view it's easy to right click on the field name and format the field. When a query is displayed you can format the column but the next time the query is run the format is lost. Fields inherit the format from the table on simple SELECT * queries but the format for a calculated field seems undefined or a system default.