Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 10 total |
Problem with Trim() in a Filter.. |
Fri, Sep 18 2020 2:23 AM | Permanent Link |
Ian Branch | 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 |
Fri, Sep 18 2020 3:24 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Sep 18 2020 5:34 AM | Permanent Link |
Ian Branch | Hi Roy,
I actually want the records with MSN = NULL to appear. Ian |
Fri, Sep 18 2020 6:00 AM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | 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 |
Fri, Sep 18 2020 6:14 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ian
As Yusuf says, or thinking about it the CAST should do that since '' <> 'N/A' Roy Lambert |
Fri, Sep 18 2020 11:15 AM | Permanent Link |
Ian Branch | 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. |
Fri, Sep 18 2020 7:44 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | 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 |
Sat, Sep 19 2020 5:53 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sat, Sep 19 2020 6:05 AM | Permanent Link |
Fernando Dias Team Elevate | 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] |
Sat, Sep 19 2020 8:50 AM | Permanent Link |
Roy Lambert NLH Associates 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 |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |