Searching for NULL value with SearchEngine?

Hi, I'm trying to use a SearchEngine against a datefield in an extratable. I want to find all rows where the datefield is NULL.

I've tried a few different variety of operators in my .addCriteria, but I'm unable to get it to search for NULL values.

Here is a simple example that doesn't work;

SearchEngine se;
se.addField("y_object.id");
se.addCriteria("y_object.x_startdate", "OperatorIs", "", "OperatorAnd", 0);

How can I get it to build a SQL query that uses "x_startdate IS NULL" ?

RE: Searching for NULL value with SearchEngine?

Hi,

try this

 

String null;

se.addCriteria("y_table.x_field", "OperatorIs", null, "OperatorAnd", 0)

Von: Hans Oluf Waaler 10. Jul 2012

RE: Searching for NULL value with SearchEngine?

Thanks, that worked :)

Von: Frode Lillerud 10. Jul 2012

RE: Searching for NULL value with SearchEngine?

Hello, 

This does not seem to work in SuperOffice 8.0....

Von: Ummair Tahir 9. Apr 2018

RE: Searching for NULL value with SearchEngine?

Sure it does.

 

That give me three rows back, as expected in my demobase.

Von: Frode Lillerud 9. Apr 2018

RE: Searching for NULL value with SearchEngine?

I got it to work in somewhat same way aswell :)

Von: Ummair Tahir 9. Apr 2018

RE: Searching for NULL value with SearchEngine?

8.5 R4 on premise:

I can't seem to get it working?

Von: Dennis Aagaard Mortensgaard 10. Sep 2019

RE: Searching for NULL value with SearchEngine?

Did you define the variable first?

String null;
Von: Frode Lillerud 10. Sep 2019

RE: Searching for NULL value with SearchEngine?

Oh i didn't notice that - wouldn't this make the same result:

se.addCriteria('person.DeletedDate', 'OperatorIs', '');

?

I mean String null; would just be a empty String variable named null right?

like String null = '';

Von: Dennis Aagaard Mortensgaard 10. Sep 2019

RE: Searching for NULL value with SearchEngine?

To me it seems like these two are different: 

//Here the variable null is not initiated and doesent have a value i.e null in the db.
String null; 

//While here it has an empty value.
String null = ""; 

 

Its just like the SQL query : 

select * from tablename where variable='' 

//vs

select * from tablename where variable = NULL
Von: Ummair Tahir 10. Sep 2019

RE: Searching for NULL value with SearchEngine?

Ummair, you are absolutely correct. There is a difference of a null string and an empty string. We don't have a global null value available for you, but as pointed out, a string without a defined value is null.

Sverre

Von: Sverre Hjelm 11. Sep 2019