WHERE clause - exclude null values

Question

Please can someone tell me how to write a WHERE clause in the feature reader that excludes records that do not contain a value.

I have thousands of records and some of them have a Global ID - I want to keep these records.
I also have records that contain NULL or no value - I want to exclude these values.

I can perform this function in a Tester, but a colleague informed me having this clause built in to the feature reader instead will be much quicker. Perhaps someone can confirm this.

Thanks, Frank

icon

Best answer by chriswilson 24 August 2022, 03:21

View original

chriswilson · Accepted Answer

If you're talking SQL (or geodatabase SDE or file geodatabase readers) then try:

WHERE your_column <> ' ' AND your_column IS NOT NULL

Seemingly you don't need the AND/onwards part but if you want to be explicit then you can include it.

https://stackoverflow.com/questions/8660203/how-to-check-for-is-not-null-and-is-not-empty-string-in-sql-server.

Your colleague is correct that using this will be quicker as it will avoid reading records you don't need into memory.

frank_herbert · Answer

If you're talking SQL (or geodatabase SDE or file geodatabase readers) then try:

WHERE your_column <> ' ' AND your_column IS NOT NULL

Seemingly you don't need the AND/onwards part but if you want to be explicit then you can include it.

https://stackoverflow.com/questions/8660203/how-to-check-for-is-not-null-and-is-not-empty-string-in-sql-server.

Your colleague is correct that using this will be quicker as it will avoid reading records you don't need into memory.

Thanks Chris. This has done the trick, much appreciated.

WHERE clause - exclude null values

2 replies

Reply

Community Stats

Reply

Community Stats

Sign up

An FME Account is required to contribute

Login to the community

An FME Account is required to contribute

Scanning file for viruses.

This file cannot be downloaded