Help with SData query, can't query for null

[CollinCampbell]

I have a property that is a true/false.  When I add "and Confidential eq true" I get results.  I have nulls in the database so I don't want to get "and Confidential eq false" results... I really want "and Confidential ne true " results but that query returns nothing.  I've included the details below.  Thanks for any input on this.

This returns the entire set of data

{

  "$descriptor": "Sage SalesLogix | history",

  "$url": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history?_includeContent=false&startIndex=1&count=25&where=ContactId eq 'C6UJ9A000IQH' &select=Id,LongNotes,Type,CompletedDate,UserName,AccountName,ContactName,Confidential,Description&orderby=CompletedDate desc&format=json&_t=1315993541190",

  "$totalResults": 5,

  "$startIndex": 1,

  "$itemsPerPage": 25,

  "$schema": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history/$schema",

  "$first": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history?_includeContent=false&startIndex=1&count=25&where=ContactId eq 'C6UJ9A000IQH' &select=Id,LongNotes,Type,CompletedDate,UserName,AccountName,ContactName,Confidential,Description&orderby=CompletedDate desc&format=json&_t=1315993541190",

  "$last": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history?_includeContent=false&startIndex=1&count=25&where=ContactId eq 'C6UJ9A000IQH' &select=Id,LongNotes,Type,CompletedDate,UserName,AccountName,ContactName,Confidential,Description&orderby=CompletedDate desc&format=json&_t=1315993541190",

  "$template": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history/$template",

  "$post": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history",

  "$service": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history/$service",

  "$resources": [

    {

      "$updated": "\/Date(1315979968000)\/",

      "$key": "H6UJ9A0017L6",

      "$url": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history('H6UJ9A0017L6')",

      "$lookup": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history",

      "$httpStatus": "OK",

      "$etag": "uiA9kHThcvA=",

      "$descriptor": "Note",

      "AccountName": "Daffy Duck",

      "CompletedDate": "\/Date(1315979940000)\/",

      "ContactName": "Doe, Jane",

      "Description": null,

      "LongNotes": "test",

      "Type": "atNote",

      "UserName": "Administrator, ",

      "Confidential": true

    },

    {

      "$updated": "\/Date(1294943665000)\/",

      "$key": "H6UJ9A001790",

      "$url": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history('H6UJ9A001790')",

      "$lookup": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history",

      "$httpStatus": "OK",

      "$etag": "54+Lqwu4LWA=",

      "$descriptor": "Note",

      "AccountName": "Daffy Duck",

      "CompletedDate": "\/Date(1294857216000)\/",

      "ContactName": "Doe, Jane",

      "Description": "Reminder to sign reports",

      "LongNotes": " ",

      "Type": "atNote",

      "UserName": "Bunny, Bugs",

      "Confidential": null

    },

    {

      "$updated": "\/Date(1292446060000)\/",

      "$key": "H6UJ9A0016CN",

      "$url": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history('H6UJ9A0016CN')",

      "$lookup": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history",

      "$httpStatus": "OK",

      "$etag": "sBRiepJliyc=",

      "$descriptor": "Note",

      "AccountName": "Daffy Duck",

      "CompletedDate": "\/Date(1292446034000)\/",

      "ContactName": "Doe, Jane",

      "Description": "E-Sign Follow Up Training",

      "LongNotes": " ",

      "Type": "atNote",

      "UserName": "Bunny, Bugs",

      "Confidential": null

    },

    {

      "$updated": "\/Date(1292006454000)\/",

      "$key": "H6UJ9A00168X",

      "$url": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history('H6UJ9A00168X')",

      "$lookup": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history",

      "$httpStatus": "OK",

      "$etag": "sFna4XBDfRw=",

      "$descriptor": "Note",

      "AccountName": "Daffy Duck",

      "CompletedDate": "\/Date(1292005888000)\/",

      "ContactName": "Doe, Jane",

      "Description": "Set up Additional Remote Acccess",

      "LongNotes": "Needed help setting up remote accesss on another computer.\r\n",

      "Type": "atNote",

      "UserName": "Fudd, Elmer",

      "Confidential": null

    },

    {

      "$updated": "\/Date(1291150516000)\/",

      "$key": "H6UJ9A001611",

      "$url": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history('H6UJ9A001611')",

      "$lookup": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history",

      "$httpStatus": "OK",

      "$etag": "6AyEDszsc0A=",

      "$descriptor": "Note",

      "AccountName": "Daffy Duck",

      "CompletedDate": "\/Date(1291149035000)\/",

      "ContactName": "Doe, Jane",

      "Description": "Setup Remote Access",

      "LongNotes": "Helped MD set up remote access on office computer and walked through on E-Sign reports\r\n",

      "Type": "atNote",

      "UserName": "Bunny, Bugs",

      "Confidential": null

    }

  ]

}

*****************************************

I add %20and%20Confidential%20eq%20true and I get what I’m expecting

{

  "$descriptor": "Sage SalesLogix | history",

  "$url": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history?_includeContent=false&startIndex=1&count=25&where=ContactId eq 'C6UJ9A000IQH' and Confidential eq true &select=Id,LongNotes,Type,CompletedDate,UserName,AccountName,ContactName,Confidential,Description&orderby=CompletedDate desc&format=json&_t=1315993541190",

  "$totalResults": 1,

  "$startIndex": 1,

  "$itemsPerPage": 25,

  "$schema": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history/$schema",

  "$first": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history?_includeContent=false&startIndex=1&count=25&where=ContactId eq 'C6UJ9A000IQH' and Confidential eq true &select=Id,LongNotes,Type,CompletedDate,UserName,AccountName,ContactName,Confidential,Description&orderby=CompletedDate desc&format=json&_t=1315993541190",

  "$last": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history?_includeContent=false&startIndex=1&count=25&where=ContactId eq 'C6UJ9A000IQH' and Confidential eq true &select=Id,LongNotes,Type,CompletedDate,UserName,AccountName,ContactName,Confidential,Description&orderby=CompletedDate desc&format=json&_t=1315993541190",

  "$template": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history/$template",

  "$post": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history",

  "$service": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history/$service",

  "$resources": [

    {

      "$updated": "\/Date(1315979968000)\/",

      "$key": "H6UJ9A0017L6",

      "$url": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history('H6UJ9A0017L6')",

      "$lookup": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history",

      "$httpStatus": "OK",

      "$etag": "uiA9kHThcvA=",

      "$descriptor": "Note",

      "AccountName": "Daffy Duck",

      "CompletedDate": "\/Date(1315979940000)\/",

      "ContactName": "Doe, Jane",

      "Description": null,

      "LongNotes": "test",

      "Type": "atNote",

      "UserName": "Administrator, ",

      "Confidential": true

    }

  ]

}

****************************************************

I change it to ne since I want the false and the nulls

{

  "$descriptor": "Sage SalesLogix | history",

  "$url": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history?_includeContent=false&startIndex=1&count=25&where=ContactId eq 'C6UJ9A000IQH' and Confidential ne true &select=Id,LongNotes,Type,CompletedDate,UserName,AccountName,ContactName,Confidential,Description&orderby=CompletedDate desc&format=json&_t=1315993541190",

  "$totalResults": 0,

  "$startIndex": 1,

  "$itemsPerPage": 25,

  "$schema": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history/$schema",

  "$first": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history?_includeContent=false&startIndex=1&count=25&where=ContactId eq 'C6UJ9A000IQH' and Confidential ne true &select=Id,LongNotes,Type,CompletedDate,UserName,AccountName,ContactName,Confidential,Description&orderby=CompletedDate desc&format=json&_t=1315993541190",

  "$last": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history?_includeContent=false&startIndex=1&count=25&where=ContactId eq 'C6UJ9A000IQH' and Confidential ne true &select=Id,LongNotes,Type,CompletedDate,UserName,AccountName,ContactName,Confidential,Description&orderby=CompletedDate desc&format=json&_t=1315993541190",

  "$template": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history/$template",

  "$post": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history",

  "$service": "http://localhost:3380/SlxClient/slxdata.ashx/slx/dynamic/-/history/$service",

  "$resources": []

[RJLedger]

Yep. The "null" issue has been a continuing problem w/SData as well as other client apps forever. It's not restricted to boolean items either (ex: Foreign Keys, etc.)

You have two choices - either program "defensively" and always assume you could get a NULL back or just make sure there are no NULLS in any/all critical fields in your db.

I wrote a short blog on this a while ago. Trashy Data bites/Bytes Back!  As a follow-up to it, I've been developing a set of SQL Update statements to repair/fix the db where these issues come to the surface. If anyone wants to contribute to it and benefit from the combined efforts of all.. just drop me an email w/any items that have been issues. We'll look at your contributions, incorporate them and send you a link where you can download a copy of all of it.

[CollinCampbell · Accepted solution]

where=Confidential ne 'true' OR Confidential eq null

So either handle cleanup of the data or add a specific case for null