Cache & SQL - Working with Empty/NULL Results

[Mack]

After executing a SQL query, I am provided with results that have an empty value in a column. What would be the best method for excluding NULL results when making the SQL query? While I understand there may be multiple ways to approach this, I want to ensure I'm utilizing the best method as the application I am developing will be a web-based CMS application. I would greatly appreciate any assistance.

My initial query looked like this

SELECT USERID, NAMEFIRST, NAMELAST, DATEBIRTH
FROM APP.USER

The following results were provided (pipe-delimited).

#|USERID|NAMEFIRST|NAMELAST|DATEBIRTH
1|023|THOMAS|CARTER|
2|ABA|ALEXANDER|ANDERSEN|54142
3|ADC|ASHLEY|CORRONADO|49519

The following is my attempt to exclude the NULL variables. Is this the most cost-effective query?

SELECT USERID, NAMEFIRST, NAMELAST, ISNULL(DATEBIRTH,'NULL')
FROM APP.USER
WHERE DATEBIRTH<>'NULL'

Thanks in advance for all of your help.

[Mack]

Lesson learned. Don't forget your basics due to someone else's advice.

It would have been better to use the following.

SELECT USERID, NAMEFIRST, NAMELAST, DATEBIRTH
FROM APP.

USER
WHERE DATEBIRTH IS NOT NULL

[Alex Grishkan]

Try

SELECT USERID, NAMEFIRST, NAMELAST, ISNULL(DATEBIRTH,'NULL')
FROM APP.

USER
WHERE DATEBIRTH IS NOT NULL

[Mack]

As info, I found that I did not need to use ISNULL(DATEBIRTH,'NULL'); however, this may only be applicable for my current version (2010.2) as I am sure there is a reason for why this functionality exists and may also be why my coworker advised the IS NOT NULL standard didn't work previously.

[jpm]

Mack,

Years ago I noticed that with legacy data that is projected into Cache tables there was some variation in how the Cache projection treated null versus blank values.  So, I took to using (DATEBIRTH IS NOT NULL AND DATEBIRTH <> '') to ensure that my results were always valued.  That may be where your coworker's advise came from.  I still have this habit to this day and I haven't tested with any recent version to determine if I need to keep doing it or not.

As to what ISNULL is for in general (if I understand your question), you can use it in your SELECT statement to retrieve different values based on whether or not a column is null.

-J.

On Monday, September 28, 2015 at 3:55:10 PM UTC-4, Mack wrote:

[Rosti]

Mac,

As JPM points out, I also had this issue and resolved it with checking for both NULL and '' as it appears that when a record is initially created, the value is NULL. But after a value is cleared by setting the value to '', it is no longer NULL and therefore requires a test for both NULL and '' if looking to select or exclude those items that have no value.

Rosti.

[Joel Solon]

How NULL works and why it is special is a pretty standard thing in relational databases. You can read all about it. There’s a difference between ‘' (the string of zero-length) and NULL (there is absolutely nothing there). When a row/object is initially created, any empty properties/columns are NULL. If a column value is cleared and you want it to be NULL again, set the column’s value to NULL. Setting it to ‘’ means something different and should rarely if ever be done. On the object side it’s a little simpler: if an object property with a value is set to “”, the corresponding column's value will again be NULL.

So if you are consistent, you should be able to use only this to exclude rows: WHERE <column> is NULL

--
--
Caché, Ensemble, DeepSee

---
You received this message because you are subscribed to the Google Groups "Caché, Ensemble, DeepSee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to intersystems-publi...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[jpm]

For tables/persistent classes that are created within Cache that is absolutely the truth.  NULL is NULL and unvalued is unvalued and they are distinct.  The specific scenario I encountered (again, this was many version ago) was for data stored in globals carried forward from MSM/DSM implementations that was then used as the storage for a Cache class. 

To unsubscribe from this group and stop receiving emails from it, send an email to intersystems-public-cache+unsub...@googlegroups.com.

[Brendan Bannon]

If you want to look at the global then “” is NULL and $c(0) is the empty string.

 

Seems backwards but if you think about how the values would get into the global it makes sense.  To set a value to the empty string it must be specifically set by the user in either SQL or Objects.  With  a given value it is easy to convert it so we convert the empty string to $c(0).

 

If you don’t reference a property / field then it has a NULL value.  Not providing a value for a list element or a piece of a string results in “” for a value in the global so that is NULL.

 

 

If you have a mix of using global sets with some Objects and SQL then things can be confusing because most COS application do not consider NULL and empty string to be 2 different things.  Odds are the code for not providing a value and providing “” will do the same thing and the global will end up with “”

 

Brendan

To unsubscribe from this group and stop receiving emails from it, send an email to intersystems-publi...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

 

--

--
Caché, Ensemble, DeepSee

---
You received this message because you are subscribed to the Google Groups "Caché, Ensemble, DeepSee" group.

To unsubscribe from this group and stop receiving emails from it, send an email to intersystems-publi...@googlegroups.com.

[David Van De Griek]

Mack,

For your query, you can just do:

SELECT USERID, NAMEFIRST, NAMELAST, DATEBIRTH
FROM APP.USER

WHERE DATEBIRTH IS NOT NULL

No values of DATEBIRTH can be ‘’ since ‘’ is not a valid %Date value.

Here is an example:

SAMPLES>>update sample.person set dob='' where ID=1
1. update sample.person set dob='' where ID=1

[SQLCODE: <-105>:<Field validation failed in UPDATE>]
[%msg: <Field 'Sample.Person.DOB' (value <EMPTY STRING>) failed validation>]
0 Rows Affected

‘’ is the SQL empty string.  This is a known value, and is not the same as NULL.

Typically you do not want to interchange ‘’ for NULL when “clearing” values for a field.

Many datatypes that are not strings will not even accept ‘’ as a valid value.

If you want to clear the value and make it null/unknown, use:

  update app.”user” (datebirth) values (null) where userid = ?

If you want to store an explicit empty string value for a string field, update it to ‘'

  update app.”user” (myStringField) values ('') where userid = ?

It is probably pretty rare that you want to actually store an empty string value (‘’) in your database.

-dave

On Oct 6, 2015, at 4:59 PM, Rosti <ros...@gmail.com> wrote: