Is there a way to use the Calc functions in a Base query. I am attempting to calculate Wet Bulb, Dew Point and Enthalpy properties of air. I have Dry Bulb temp and Relative Humidity. The formulas use LN, EXP, ATAN. The formulas work in Calc, but fail miserably in Base. Here is an example of the Query for Dew Point:
SELECT “Time”, “DB_Temp”, “Rel_Humid”, ( 243.04 * ( LN( “Rel_Humid” / 100 ) + ( 17.625 * ( ( “DB_Temp” - 32 ) / 1.8 ) / ( 243.04 + ( ( “DB_Temp” - 32 ) / 1.8 ) ) ) ) / ( 17.625 - LN( “Rel_Humid” / 100 ) - ( 17.625 * ( ( “DB_Temp” - 32 ) / 1.8 ) / ( 243.04 + ( ( “DB_Temp” - 32 ) / 1.8 ) ) ) ) ) * 1.8 + 32 AS “Dew Point” FROM “Temp-RH” WHERE ( “Time” = {d ‘2024-06-01’ } OR “Time” < {d ‘2024-06-02’ } )
Just a note, that CoolProp library provides these calculations, and can be used in Calc using a Python-based extension.
-
Base and Calc are a team in many different ways.
[Tutorial] Using registered datasources in Calc
Workaround: Database reports with charts in LibreOffice Calc
Base, Calc, Python: INSERT, UPDATE, DELETE from spreadsheet
https://forum.openoffice.org/en/forum/viewtopic.php?p=96427#p96427 (interaction with databases through forms on sheets) -
The Base report builder provides most spreadsheet functions.
Base is not a database. Base is a tool to work with databases. Which database are we talking about? This is indicated in the status bar of your Base document.
In which ways do these functions fail?
dEWpOINT.odb (10.3 KB) (query and report using LibreOffice functions)
@BobDale,
you do not provide critical information such as the database you use.
how does it fail? error message? etc.
.
as mentioned by Wanderer all modern open source databases contain the functions LN, EXP, and ATAN.
.
Firebird 3.x is included with libreoffice, it has the required functions and the capacity to create user defined functions.
.
it’s important to understand that the result of an integer divided by an integer is always an integer which means that 10 / 3 = 3 and not 3.33, however 10.00 / 3 = 3.33 and 10.000 / 3 = 3.333 etc.
.
the attachment uses Firebird 3.0.7.
it shows both dew point and wet bulb.
I worked with celsius rather than fahrenheit because it enables cleaner sql.
I created the functions ‘Get_Dew_Point’ and ‘Get_Wet_Bulb’ which further simplifies the sql.
I am am not a fan of Firebird but it’s fairly modern and because it’s embedded is useful for demos etc.
I am hoping that other users may find this demo useful.
EDIT replaced attachment. original contained wrong link for dew point calculator.
DewPoint_fb.odb (5.0 KB)
1 Like
Simply no.
.
Base can for example connect to an MySQL/MariaDB-Server “somewhere on the internet”. It will get the result back, but neither LibreOffice nor single Functions of Calc are available there.
.
But most databases have their set of functions available to SQL-queries. You may use this instead in the query. As this sadly is often a little bit different in detail you have to check documentation of your database.
.
You may combine both worlds by dragging a query in a Calc-table, then continue calculations there. But the query itself can not use Calc.
Checking HSQLDB you may find the natural logarithm at LOG(d) instead of LN()
https://www.hsqldb.org/doc/1.8/guide/ch09.html#N1251E
.
but we have LN() and LOG() for MariaDB
.
while PostgreSQL also has LOG(base, value)
.
and Firebird shows the same as PostgreSQL on natural logarithm
https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref30/fblangref30-functions-math.html
Have a look at my report: https://ask.libreoffice.org/uploads/short-url/alXjcDgnz136JgkElxhbImPaJaC.odb
I think the report functions have something to do with Calc.
Function “DewPoint”
(243.04*(LN([Rel_Humid]/100)+(17.625*(([DB_Temp]-32)/1.8)/(243.04+(([DB_Temp]-32)/1.8))))/(17.625-LN([Rel_Humid]/100)-(17.625*(([DB_Temp]-32)/1.8)/(243.04+(([DB_Temp]-32)/1.8)))))*1.8+32The question was “in a Base query”. IMHO we can pre- and post-process, but the Database can not “use Calc” for calculations. If we are lucky, we have the same functions available.
.
I’ll check your example tomorrow.
Sure. However, a query is not the end product. In most cases, you need the query for an input form, report or external office document.
The query in my sample database can be used with input forms. The table fields are editable, the calculated dew point is read-only.
The report is made directly from the table. Open the report in edit mode, get the report navigator and open the properties dialog for the user-defined function “DewPoint”. The ellipsis button opens a formula wizard very much like the one in Calc with most of the Calc functions that are not specific to spreadsheets. In this context we use LN([FieldName]) wheras the query syntax with HSQL is LOG("FieldName").
Most of my own reports are Calc sheets with database ranges linked to some query. Any formulas directly adjacent to the import range grow and shrink with the updating record set.
Xcuse me, Villeroy,
Do believe you would agree that a group of queries may satisfy users for constant current update, especially if it’s a matter of MONEY!
I could post you an example if necessary…