I ran into an odd problem today with ExecuteSQL().  I was trying to run a query that would count things that were NOT set, i.e.:

ExecuteSQL (
    "SELECT COUNT ( ID_pk )
     FROM Updates
     WHERE ID_pk = ?  AND Replaced ≠ ? "
; ""  ; "" ; ParentID ; 1 )

Each parent could have a number of updates, but we only want one active at a time; so if the Update is marked as 'Replaced' then it is no longer active.

 

So my query above was returning a dreaded "?".  However, if I ran THIS query:

ExecuteSQL (
    "SELECT COUNT ( ID_pk )
     FROM Updates
     WHERE ID_pk = ?  AND Replaced = ? "
; ""  ; "" ; ParentID ; "" )

I got the correct result.  So it apparently doesn't want to consider a NULL value as a 'not equal to 1'?  And why didn't it just report 0?  Why the question mark?

 

I will probably define an auto-enter calc to set it to 0 in the first place, but it really seems like this query should work without that having to be defined as something non-empty.

 

Thanks,

J

Doh!!  Apparently it doesn't recognize the "≠" operator.  When I replaced it with "<>" it worked fine.

I don't think SQL recognizes the ≠ operator; try using <> instead. That would account for the question mark.

 

Re null values, they have their own operators: IS NULL and IS NOT NULL. See here for a better explanation:

http://www.w3schools.com/sql/sql_null_values.asp

http://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/

Thank you for the links.  The first produces a 404 error until I remove the colon (or is that a semi-colon?) from the end.

Thanks - fixed now.

Thanks for the links, Comment.  Very interesting behavior.  Good information to know about.