Result is 0 !!!

[arfgh]

Ok

i have this query where the result shouln't be 0, but it is !

Code: Select all

100 - ( SELECT SUM( "A" - "B" ) FROM "IN" WHERE "ORG" = 'Blabla')

If we do just the select alone we will obtain '0'. So by the way we would expect 100 - 0 = 100. But no, result is 0 !!!
I need an explanation to understand the reason of that unexpected value.

thx in advance

[MTP]

Is this a snippet from a larger query? There might be some type conversion going on that's not obvious from the snippet.

Also, just to check, what are the field types of A and B?

[arfgh]

both are currency, with two decimals.

[Villeroy]

May be any of these?
100-SUM(COALESCE("A",0) - COALESCE("B",0))
100 -SUM("A") + SUM("B")
100 -SUM(COALESCE("A",0)) + SUM(COALESCE("B",0))

[MTP]

The query you posted gives me a syntax error and will not run. This:

Code: Select all

SELECT 100 - SUM("A" - "B" ) FROM "IN"

gives the expected result.

Are you using a different backend than the default HSQLDB? What query are you actually running (since the posted one won't compile)?

[arfgh]

the backend is jut base with lastest AOO.
Like i said, A and B are currency fields with 2 decimals.
In this case there are no results in the query, but the SUM(A - B) give as result 0.
So then why that happen ? 100 - 0 = 0 how is it possible.

[arfgh]

MTP just what i said:

Code: Select all

select "Name", "ORG",  100 - ( SELECT SUM( "A" - "B" ) FROM "IN" WHERE "ORG" = 'blabbla') from "Names"

There you have it complete. But it is the embedded DB, last AOO version.

Villeroy, none of the solutions you gave solved the problem.

The sub-query with the SUM result is 0. Because there are no entries in "IN". I have tried the sub-query alone, and yes, the result is 0. But then i need some explanation about why 100 - 0 = 0

thx in advance

[MTP]

there are no entries in "IN"

There is your explanation. SUM of a NULL value is NULL.

100 - NULL = NULL

If your table has at least one entry and A or B values (or both) have the possibility of being NULL you need to use COALESCE as Villeroy posted for you earlier. If the table might be empty you need to use COALESCE to wrap the full SUM function.

[arfgh]

i figured that MTP, but if you use the sub-query ONLY, the result is 0. So 100 - 0 != null

[UnklDonald418]

if you use the sub-query ONLY, the result is 0.

No the result is Null, but what's confusing you is that HSQL displays Null as 0.
if as MTP suggested you change the query to

Code: Select all

 SELECT 100 - COALESCE(SUM("A" - "B" ),0) FROM "IN"

the result is 100 because the COALESCE statement substitutes the value 0 for the NULL result of the SUM
Likewise, if you set A = 0 and B = 0 in table “IN” then the sub-query result is 0 and the full query returns 100.

[arfgh]

i must insist. The result of the subquery is 0 and not null. I understand what all of you are pointing. But i see the 0. If i see the 0 as result... 100 - 0 = ?

[chrisb]

228 posts & still struggling with the basics!!

Code: Select all

Select "Name", "ORG",
100 - ( SELECT SUM( "A" - "B" ) FROM "IN" WHERE "ORG" = 'blabbla') COL1,
coalese(cast(100 - ( SELECT SUM( "A" - "B" ) FROM "IN" WHERE "ORG" = 'blabbla') as varchar(10)) COL2, 'IF YOUR READING THIS THEN THE VALUE SHOWN IN COL1 AS ZERO IS ACTUALLY NULL')
from "Names"

[UnklDonald418]

The ASCII value for the control character Null is 0
You will continue to struggle while you “insist” on misinterpreting the result.

[arfgh]

ok UnklDonald418, your answer did the point. All saying that is null that is null when i see 0. So if the way it works is 0 to represent null, then all is ok. Just the explanation i needed. Because i never figured that the way to represent null can be 0.

Anyways and in my opinion, if the backend show 0, that is the result that should be taken. Otherwise, it should show null in order to dont mess us. Am I wrong with that ?

[UnklDonald418]

It's one of those trade-offs.
The control character Null generally represents a lack of data in a table field and it does not display on a computer screen or printer. However, displaying nothing as a query result wouldn't be very helpful so the decision was to represent Null in a query result with a displayable 0 (the ASCII character 48). When you perform the calculation 1-1 the result also is not displayable but according to mathematical convention it is represented as 0. So now there are two distinct 0's.
SQL designers understood that there could be confusion so they included functions like COALESCE, IFNULL and ISNULL to help distinguish between the two. They also included a NULLIF function to generate a Null, because there are situations where displaying “nothing” can be useful.

[arfgh]

so coalesce, ifnull, isnull, nullif. Are all valid commands for embedded hsql on AOO last version ?

[UnklDonald418]

COALESCE has already been demonstrated to work in an embedded database.
If you replace COALESCE with IFNULL it too will return a value of 100
ISNULL is not implemented in the embedded version and will return a error message, but it does work in a split database using HSQL 2.3.4
I have used NULLIF in INSERT and UPDATE statements in embedded databases, but never tried it in a query.
Out of curiosity I added a record to the IN table and set A = 0 and B = 1 and then ran

Code: Select all

SELECT 100 -  COALESCE(SUM( "A" - "B" ),0) FROM "IN"

the result was 101 as would be expected. Next I ran

Code: Select all

SELECT 100 - COALESCE ( SUM( "A" - NULLIF ( "B", '1' ) ), 0 ) FROM "IN"

and the result was 100, confirming that NULLIF also works in a query.

[Villeroy]

http://hsqldb.org/doc/guide/builtinfunc ... _functions and you have a local copy of the documentation if you downloaded the full package

I don't get the difference between coalesce and ifnull.

[Sliderule]

http://hsqldb.org/doc/guide/builtinfunc ... _functions and you have a local copy of the documentation if you downloaded the full package

I don't get the difference between coalesce and ifnull.

Villeroy:

According to the HSQL 2.3.4 documentation . . . and . . . remember with HSQL 1.8 documentation, it is noted that user should use COALESCE instead:

IFNULL

ISNULL

IFNULL | ISNULL ( <value expr 1>, <value expr 2> )

Returns <value expr 1> if it is not null, otherwise returns <value expr 2>. The type of the return value is
the type of <value expr 1>. Almost equivalent to SQL Standard COALESCE(<value expr 1>, <value
expr 2>) function, but without type modification. (JDBC)

Therefore, it is recommended to use the SQL standard COALESCE.

Sliderule