how to exclude null/blank/emply columns from the result set?
-
I am trying to narrow the result set by excluding rows that don't contain populated values.
But my select with the is not null test does not work.
SELECT ENT_ResourceUniqueID, WPROJ_ID, ResourceEnterpriseRMV21ID
FROM dbo.MSP_VIEW_PROJ_RES_ENT where ResourceEnterpriseRMV21ID is not null
returns
Col1 Col2 Col3
261 2413 2
261 3
261 2428 4
In the above result set, I want to exclude row 2 where Col3 is not populated. Col3 is defined as ntext in the database. Col3 is ResourceEnterpriseRMV21ID where I tested for is not null.
Through serendipity, I got rid of the null/empty valued rows with this select.
SELECT ENT_ResourceUniqueID, WPROJ_ID, ResourceEnterpriseRMV21ID
FROM dbo.MSP_VIEW_PROJ_RES_ENT where substring(ResourceEnterpriseRMV21ID,1,1) > 0
I still don't understand what test the clause -- substring(ResourceEnterpriseRMV21ID,1,1) > 0 -- is really doing??????
Thanks
-
The value in that column must be not null but empty string.
'' is not NULL.
Try this:
where len(ResourceEnterpriseRMV21ID) > 0
_____________
Code for TallyGenerator -
I still don't understand what test the clause -- substring(ResourceEnterpriseRMV21ID,1,1) > 0 -- is really doing?????? If a value from a column is NULL, it cannot be greater than 0 (or anything else, NULLs are eliminated by such comparisons). Also, a blank or empty cell has an equivelent numeric value of 0.
So you formula get's rid of NULL's, all blanks, empty strings, and convertable values <= 0.
You don't need the substring either...
WHERE ResourceEnterpriseRMV21ID > 0
Now, if you don't want to eliminate any values, this will also work...
WHERE ResourceEnterpriseRMV21ID > ''
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply