SQL problem after splitting database

[Chris-H]

I am using Libreoffice base 6.3.6.2 (x64) on Windows 10 with a newly splitted HSQLDB database. I have setup a database with tables, queries and forms in order to write invoices and keep control of a stock situation.
I followed the excellent tutorial to split HSQLDB databse and copied all my tables, queries etc… using the JDBC driver to connect. Now I noticed that some SQL I had used within the invoice form does not work anymore and I cannot see why that is.
It basically is a formula to extend the final price after calculating a discount, it goes as following:

( “ITEMS”.“PRICE” - ABS( COALESCE ( “SOLD”.“DISC_CURRENCY”, 0 ) ) ) * “SOLD”.“QTY” * ( 1 - ABS( COALESCE ( “SOLD”.“DISC_PERCENT”, 0 ) ) )

Does anybody see any reason why this wouldn’t work anymore? Could this be due to the ABS not being accepted?
Thanking you in advance for any idea

EDIT: same goes for some macro (from form to print).

[keme]

Could you detail on the “not working” part?

• Are you sure that the SQL is at fault? Could it be that some reference in your form points to the old (now nonexistent?) data source?
• Have you tried to run the query by itself to look at it in tabular form?
• Do you get an error message?
• Do you get results but they are wrong?
• Do you get an empty result?

[Chris-H]

thanks much for your comment @keme. Yes I have tryed the query and there is no error message, just the results are wrong. it does not subestract the given percentage or discount. It seems to get the correct answers if I remove the ABS( parts

[Ratslinger]

Hello,

When moving from one version of a database to another there is always the potential that either standards were implemented or errors fixed. It appears you went from HSQLDB embedded v1.8 to split 2.x

So it seems your calculation is being affected by the data used and may need adjustments - especially when dealing with decimal data. Try this SQL in each version:

Select 34 - ABS(COALESCE (23.42, 0)) From "YOUR_TABLE"

In v1.8 the result is 10.58 but in v2.x the result is 11. The calculation is based upon an Integer in v2.x.

Now try:

Select 34 * 1.00 - ABS(COALESCE (23.42, 0)) From "YOUR_TABLE"

Now both versions will result in 10.58

Review your situation and adjust the formula accordingly.

[Chris-H]

@ratslinger… that is it, absolutely! thank you very much for sharing your knowledge!