March 27, 2006 at 6:41 pm
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
March 27, 2006 at 7:37 pm
The value in that column must be not null but empty string.
'' is not NULL.
Try this:
where len(ResourceEnterpriseRMV21ID) > 0
_____________
Code for TallyGenerator
March 27, 2006 at 7:59 pm
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
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply