Howto format a SQL table result ?

Discuss the database features

Howto format a SQL table result ?

Postby arfgh » Fri Nov 08, 2013 1:41 pm

Hello guys !

Exist a way to format the table result when we execute an sql query ?
Example imagine the follow:

Code: Select all   Expand viewCollapse view
select "client", "money" from "clients" where "client" = 'OpenOffice'


and we want that money table result it is formated as 'money' showing two decimals.
By default OpenOffice show only one decimal value in that table result.


thx in advance
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: Howto format a SQL table result ?

Postby Villeroy » Fri Nov 08, 2013 1:59 pm

The database stores raw data only and OpenOffice can show your data in formatted and layouted elements on forms (input) and reports (output).
Everything depends on the field type where you store the raw data. Based on the raw data hen you may apply any format you want.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26540
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Howto format a SQL table result ?

Postby arfgh » Fri Nov 08, 2013 2:04 pm

well, i am using the 'Query' section into Base. I know that i can format manually these table results, but i wanted to know if exist a way to perform it from the SQL command to obtain the data properly formated at once.
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: Howto format a SQL table result ?

Postby Villeroy » Fri Nov 08, 2013 2:33 pm

In all software development and database development, data and formatting are 2 separate layers.
Base works with 2 different software products at the same time:

1. The underlying database you don't mention (HSQL?). It stores strictly typed data in fields. All the time you never tell us anything about your field types.

2. The office suite which provides
2a. simple data grids to visualize raw data of simple types (numbers, strings, date, times, booleans) so the database developer gets some visual feed back (29.1 and 29.100 are the same value actually).
2b. office documents with formattable tables and form controls for the aleged end user of your database application. Here you provide specialized input controls for different sub-types of numbers (currency, percent, etc.) and you can format any output dumped into reporting tables, mail merge fields, spreadsheet cells, charts etc.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26540
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Howto format a SQL table result ?

Postby arfgh » Fri Nov 08, 2013 4:16 pm

1 - Openoffice 4.01
2 - Openoffice 4.01
2a - I have said 'money' so it is numbers
2b - I want to know if is possible to send formated data from sql command to the view port results
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: Howto format a SQL table result ?

Postby Villeroy » Fri Nov 08, 2013 4:25 pm

The type of database connected to your Base document is indicated in the status bar of that document. Base can generate dBase and HSQL databases and it can connect to dozends of other types of databases.
HSQL is a separate, 3rd-party database engine. An old version of it is shipped with OpenOffice. Base is not a database at all. It is just a tool to work with databases. The office version makes no difference since there is almost no develpment on the Base component. The relational database (HSQL or any other) is a separate software, controlled through the Base component.
The availlable field types depend on the used type of database. "Numbers" can be floating point, double-precision floating point, fixed decmial types and different sizes of integer numbers. The true field type of a HSQL database is shown in the edit view of a table (right-click>Edit).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26540
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Howto format a SQL table result ?

Postby arfgh » Fri Nov 08, 2013 10:21 pm

Villeroy, i dont need that kind of deep answer because all that i want to know is if i can set the output format 'from the sql command'. I just know that i can change the format in the output viewport, but unfortunatelly i have to do it every time that i execute the query because it shows always only one decimal value when i need two because it is about money.

Do you understand me ?

PD: i am not english...
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: Howto format a SQL table result ?

Postby DACM » Mon Nov 11, 2013 2:44 am

arfgh wrote:...change the format in the output...it shows always only one decimal value when i need two because it is about money.

Here's my own findings subject to correction, expansion, or simplification...

Table Data View formatting:

    You can add the full spectrum of data-type formatting to the Table Data View in Base by right-clicking the column header > Column Format...

    This table-level formatting will apply (pass-through) to the same field(s) in a query result set.


    However, this formatting does not apply to calculated fields created by the query (at least not permanently).

Query 'calculated field' formatting:
    You can format a calculated field using CAST AS VARCHAR in the final step:

    Plus you can add the currency symbol or other text using CONCATENATION:


Form field formatting:
    You can use a Formatted Field on a Form to control the data-type formatting plus alignment (left/center/right justified), color, size, font, and other attributes.

See also:
https://forum.openoffice.org/en/forum/v ... 25#p298025
http://www.oooforum.org/forum/viewtopic ... 643#348643
...
Last edited by DACM on Mon Mar 10, 2014 9:59 am, edited 1 time in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Howto format a SQL table result ?

Postby arfgh » Tue Nov 12, 2013 11:18 am

ok DACM, that is what i was finding !!
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: Howto format a SQL table result ?

Postby arfgh » Mon Dec 16, 2013 7:48 pm

i am trying to use..


cast ( blabla as currency)

and

cast ( blabla as money)

no lucky, i obtained an error and i dont know why.
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: Howto format a SQL table result ?

Postby Villeroy » Mon Dec 16, 2013 7:59 pm

You still refuse to tell us anything about the database you are using. Does it really support a currency data type?
HSQL does not have any such type.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26540
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Howto format a SQL table result ?

Postby arfgh » Mon Dec 16, 2013 8:11 pm

yes villeroy, i use AOO 4.0.1 and it says that is HSQL Engine
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 1 guest