Hi Team,
D10.4.1, current EDB, Win 10, 32bit App.
I have a table with a VARCHAR field called MSN.
It currently contains all sorts of correct data, including NULL where no data has been entered.
If I apply a Filter on the table in edbmgr of   Trim(BOTH ' ' from MSN) <> 'N/A'
'N/A' is a valid entry but I am seeking to exclude records with MSN = 'N/A' from visibility.
That filter is working fine, sorta, my problem is that it is also excluding records with NULL in them, which I still want visible.

Ummmm.  Why is it so?  Is this a bug or have I not got something set correctly?

I also tried the following filter  MSN not like '%N/A%'   but get the same result.

Regards & TIA,
Ian

Ian


Its part of the NULL conspiracy

Change your filter to

Trim(BOTH ' ' from MSN) <> 'N/A' AND MSN IS NOT NULL

You have to explicitly test for nulls. No longer are they the same as an emptystring

The other thing you can do is CAST MSN - without testing

Trim(BOTH ' ' from CAST(MSN AS VARCHAR(3)) <> 'N/A'


Roy Lambert

Hi Roy,

I actually want the records with MSN = NULL to appear.

Ian

Ian Branch wrote:

> Hi Roy,
>
> I actually want the records with MSN = NULL to appear.
>
> Ian

Hi Ian,

what about

(Trim(BOTH ' ' from MSN) <> 'N/A' or MSN IS NULL)

--
--
Yusuf Zorlu | MicrotronX

Ian


As Yusuf says, or thinking about it the CAST should do that since '' <> 'N/A'

Roy Lambert

Yusef - Thank you.  That works.
Roy - Trim(BOTH ' ' from CAST(MSN AS VARCHAR(3))) <> 'N/A' - Does NOT show the records with NULL in the MSN.

Ian

Examples from the US Department of Agriculture Food database. You can have filters such as:

1. Shrt_Desc is null

2. Shrt_Desc is null or shrt_desc LIKE 'Cheese%'

3. Shrt_Desc is null or (shrt_desc LIKE '%blue%' and shrt_desc LIKE 'cheese%')

All work as expected.

<<If I apply a Filter on the table in edbmgr of   Trim(BOTH ' ' from MSN) <> 'N/A'
'N/A' is a valid entry but I am seeking to exclude records with MSN = 'N/A' from visibility.
That filter is working fine, sorta, my problem is that it is also excluding records with NULL in them, which I still want visible.>>

<<Ummmm.  Why is it so? >>

This is because SQL has three-valued logical results (TRUE, FALSE, UNKNOWN) instead of the more familiar two-valued logical results (TRUE, FALSE). Normally, we expected all the cheeses if we ask for Cheese is 'Blue' and cheese is not 'Blue' but not so with SQL. If you do not have the description of the cheese then you cannot expect to see all the rows for the table as a result of Cheese is 'blue' and cheese is not 'blue'.

I suggest that you get rid of the spaces in the 'N/A' columns which makes the data cleaner.

Richard

Ian


I did say untested

I have now tested and CASTing a NULL as a VARCHAR returns a NULL. I'm off to have a cold shower before I start ranting.

Roy Lambert

Roy,

That tells me that EDB is working well
CAST adds a type but doesn't turn an unknown (NULL) value into a known value.
It only adds that the value is of type VARCHAR, but still unknown.

--
Fernando Dias
[Team Elevate]

Fernando

>That tells me that EDB is working well
>CAST adds a type but doesn't turn an unknown (NULL) value into a known value.
>It only adds that the value is of type VARCHAR, but still unknown.

Well is a relative term.I think you mean "in accordance with the standard" which is subtly different

Roy