[Solved] Different behavior of IS NULL with LO and OO

[charlie.it]

Hi everyone,
In case the parameter ':A' is not inserted, this code,

Code: Select all

SELECT * FROM "Mytable" WHERE ( "Age" = :A OR :A IS NULL )

• in LO it shows all the records
• in OO it shows one record empty

Why?
With what can I replace IS NULL in OO?
Thank you.

[chrisb]

Hi,
you are using a parameter query.
parameters are handled by the application i.e OpenOffice, LibreOffice & NOT the database.

if the user does not enter a value then OpenOffice returns an empty string while LibreOffice returns null.

LibreOffice:

Code: Select all

SELECT * FROM "Mytable" WHERE "Age" = :A OR :A is null

OpenOffice:

Code: Select all

SELECT * FROM "Mytable" WHERE "Age" = :A OR :A = ''

[charlie.it]

Hi,
in LibreOffice, if the user does not enter a value,

Code: Select all

SELECT * FROM "Mytable" WHERE "Age" = :A OR :A is null

shows all records, it is that i need also in Open Office, because

Code: Select all

SELECT * FROM "Mytable" WHERE "Age" = :A OR :A is null
SELECT * FROM "Mytable" WHERE "Age" = :A OR :A =''

show an empty record if the user does not enter a value.

[chrisb]

hello charlie you are correct.

WITH OPEN OFFICE:
when field type is string & the user does not enter a value then an empty string is returned & this works.

Code: Select all

SELECT * FROM "Mytable" WHERE "Age" = :A OR :A = ''

when field type is integer & the user does not enter a value then zero is returned & this works.

Code: Select all

SELECT * FROM "Mytable" WHERE "Age" = :A OR :A = 0

[charlie.it]

I'm looking for a way to view all the records when the user doesn't enter the parameter.

[chrisb]

how is the field age defined? i guess it's an integer.
did you try?

Code: Select all

SELECT * FROM "Mytable" WHERE "Age" = :A OR :A = 0

what was the result?

[charlie.it]

Thank you, I forgot that Age is numeric
It works, thank you !

[charlie.it]

I am sorry but, what can I do for Date fields?

Code: Select all

=''

it seem doesn't work. Error: "java.lang.IllegalArgumentException"

[chrisb]

Hi didn't realise that you added an issue, just noticed that the [Solved] tag had gone missing.

i had to test this because i found this date issue a little tricky.
when i used

Code: Select all

WHERE "Date" = :A

and did not enter a value then an error was thrown 'java.lang.IllegalArgument.Exception'.
a workaround is

Code: Select all

SELECT * FROM "Mytable" WHERE to_char("MyDateField", 'dd MM YY') = :DD_MM_YY or :DD_MM_YY = ''

input is in the form of 'DD MM YY' without the quotes.

the upgrade carried out by the LibreOffice developers which casts an empty parameter as NULL is a major improvement.

[charlie.it]

I added a new question because I had forgotten that in the real database I have to look for the records in which a date is between a minimum and a maximum typed. If one or both of the dates are not entered, I have to see all the records in the table.
In Libre Office this code works:

Code: Select all

SELECT * FROM "Mytable" WHERE ("Day" >= :From OR :From IS NULL ) AND "Day" <= :To OR :To IS NULL)

In Open Office that code doesn't work like also

Code: Select all

SELECT * FROM "Mytable" WHERE ("Day" >= :From OR :From ='' ) AND "Day" <= :To OR :To ='')

They show error: "java.lang.IllegalArgumentException"

[chrisb]

it can be done in openoffice using parameters but is hardly worth the trouble.
if you must use openoffice then it's best to use a form in conjunction with a filter table.
maybe someone more knowledgeable than i can offer a better solution.
assuming that "Day" is a date field:

Code: Select all

select * from "Mytable" 
where
	(
		to_char( "Day", 'YY MM dd' ) = :DateFrom_YY_MM_DD or :DateFrom_YY_MM_DD = '' 
		or
		to_char( "Day", 'YY MM dd' ) = :DateTo_YY_MM_DD or :DateTo_YY_MM_DD = '' 
	) 
		or
		to_char( "Day", 'YY MM dd' ) between :DateFrom_YY_MM_DD and :DateTo_YY_MM_DD
order by "Day"

[kifaR]

In OpenOffice I use this solution:

Code: Select all

WHERE ( CAST( "Some_Field" AS CHAR ) = :x OR IFNULL( :x, '' ) = '' )

this also works in LibreOffice

for dates between certain values:

Code: Select all

WHERE ( CAST( "data" AS CHAR )between :DateFrom and :DateTo OR IFNULL(:DateFrom, '' ) = ''  OR IFNULL(:DateTo, '' ) = '' )

Remember to enter the date parameters (:DateFrom and : DateTo) in the format 'YYYY-MM-DD'

[Villeroy]

Why does everybody use strings for dates? This makes everything more complicated.

Code: Select all

WHERE ("DateField" >= :FromDate OR :FromDate IS NULL) 
AND ("DateField" >= :UntilDate OR :UntilDate IS NULL) 

The 2 parameters can be taken from a filter form's date control or manually entered as ISO date or as a localized date.

[charlie.it]

@chrisb

thanks for your help but your code seems doesn't work well after some tests .

for dates between certain values:

Code: Select all

WHERE ( CAST( "data" AS CHAR )between :DateFrom and :DateTo OR IFNULL(:DateFrom, '' ) = ''  OR IFNULL(:DateTo, '' ) = '' )

Remember to enter the date parameters (:DateFrom and : DateTo) in the format 'YYYY-MM-DD'

This is the solution that I looking for, thank you. It works well with my tests .

[charlie.it]

@Villeroy

I already used that code in LO, it works well, but not in OO. (A friend of mine asked me to use OO).