Howto format a SQL table result ?

[arfgh]

Hello guys !

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

Code: Select all

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

[Villeroy]

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.

[arfgh]

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.

[Villeroy]

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.

[arfgh]

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

[Villeroy]

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).

[arfgh]

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...

[DACM]

...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.
  
  • See: http://openoffice.blogs.com/openoffice/ ... alcul.html
  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:

  • Code: Select all

    SELECT CAST( CAST( "PRICE" * 2 AS DECIMAL ( 18 , 2 ) ) AS VARCHAR ( 20 ) ) AS "TOTAL" FROM "ITEMS"

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

  • Code: Select all

    SELECT '$' || CAST( CAST( "PRICE" * 2 AS DECIMAL ( 18 , 2 ) ) AS VARCHAR ( 20 ) ) AS "TOTAL" FROM "ITEMS"

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:
viewtopic.php?p=298025#p298025
http://www.oooforum.org/forum/viewtopic ... 643#348643
...

[arfgh]

ok DACM, that is what i was finding !!

[arfgh]

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.

[Villeroy]

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.

[arfgh]

yes villeroy, i use AOO 4.0.1 and it says that is HSQL Engine