DateItem LESS_OR_EQUAL operator

[david_s]

Hi,

when I have DateItem or RelativeDateItem and set LESS_OR_EQUAL/LESS_THAN operator on them what is generated is:

SELECT COUNT(*) FROM supplyItem WHERE ((supplyItem.nextShipment <= TO_DATE('2011-04-15 00:00:00','YYYY-MM-DD HH24:MI:SS') OR supplyItem.nextShipment IS NULL))

how can I easily get rid of OR part of the where clause ie. I don't want NULLs to be shown as smaller than some date.
2.5.2011-04-12-EVAL

[Isomorphic]

What's your expected NULL treatment - greater than all other dates?

[david_s]

My expected treatment is exactly the same like in SQL: NULLs are special cases and are neither bigger nor smaller than number(date) in comparison, so they are not appearing in result.

[Isomorphic]

That's widely considered an oddity of SQL which people work around in various way (including what we're using). To explicitly exclude nulls, there's an IS_NULL operator you can use.

[david_s]

Ok, I see it other way (how NULL(missing value) can be less(greater) than something?), but it is the chosen way of handling the problem in the library, so I reluctantly accept it ;-)
That brings me back to original question, how I can set it up on the field when I want to achieve above described behavior? Should I create operator with LESS_THEN and then on the server check all the LESS operators and remove half of the condition on the server resp. create a new criteria which includes only half of the original condition? Or add another criteria but then condition would be

SELECT COUNT(*) FROM supplyItem WHERE ((supplyItem.nextShipment <= TO_DATE('2011-04-15 00:00:00','YYYY-MM-DD HH24:MI:SS') OR supplyItem.nextShipment IS NULL)) AND supplyItem.nextShipment IS NOT NULL

Is there some better easier and cleaner way?
Maybe that IS_NULL operator you mentioned, but I cannot see the way how IS_NULL operator is going to help me to exclude NULLS from result once they are there, maybe I misunderstood operators: is there some way how to combine them?

[Isomorphic]

Just to clarify, the UI problem created by SQL's null handling is that users often do not realize that they need to eliminate criteria entirely to see null-valued records.

Use the IS_NULL operator if you want to eliminate nulls. All operator types support combining via the Boolean operators.

[david_s]

I am sorry, I still not understand it. I have this code:

op1 = new FloatItem();
op1.setOperator(OperatorId.LESS_THAN);

How can I set IS_NULL or NOT_NULL on it to exclude nulls? Can you please post the code?

One way how to do it, which I know is to change criteria in operationBinding, but I don't like this solution as I have some compound component and when using it, it would require to change manually each datasource in which it would appear(and if the change is forgotten, old behavior is back) Thats why I am looking for some solution which would be inside of that compound component(its CanvasItem with innerForm as Canvas and few existing items) and once in place would require no further coding.

[Isomorphic]

It's not really clear how your code is organized, but you want to use DataSource.combineCriteria() or similar criteria manipulation approaches to add the IS_NULL Criterion to the Criteria you are currently producing.

[Blama]

Hi david,

I think that Isomorphic meant (correct me if I'm wrong) NOT_NULL (http://www.smartclient.com/smartgwte....html#NOT_NULL) all the time when writing IS_NULL.

Best regards,
Blama