resultset is single blank!

Question

Post reply

resultset is single blank!

CooLDBA

Hall of Fame

Points: 3301
More actions
February 11, 2005 at 10:55 am

#92238

Is anyone of you experience that your resultset is blank but in your where condition you have NOT NULL and <> ''.
I tried sp_dbcmptlevel
The current compatibility level is 80.
any ideas? or solutions to remove them?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Maybe it's just a carriage return. Can you post the whole query so we can check it out?

CooLDBA Hall of Fame Points: 3301 More actions · Answer 2

select * from dbo.SAME_NAME_DOB_GENDER

where name is not NULL and name <> ''

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

What are you expecting the results to be ? When you say "blank", do you mean an empty result set (no rows returned) or there are blanks in the returned rows ?

What do diagnostic record counts show ?

ie:

select count(*) As CountNull from dbo.SAME_NAME_DOB_GENDER

where name is NULL

select count(*) As CountBlank from dbo.SAME_NAME_DOB_GENDER

where name = ''

select count(*) As CountAll from dbo.SAME_NAME_DOB_GENDER

CooLDBA Hall of Fame Points: 3301 More actions · Answer 4

there are blanks in the returned rows!

What do diagnostic record counts show

ie:

select count(*) As CountNull from dbo.SAME_NAME_DOB_GENDER

where name is NULL

0 row

select count(*) As CountBlank from dbo.SAME_NAME_DOB_GENDER

where name = ''

43 rows

select count(*) As CountAll from dbo.SAME_NAME_DOB_GENDER

6904 rows

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

If I understand you correctly, you are saying something like, row one has values, row two has values, row three is blank, row four through 20 has values.... etc?

Or, are you getting one row returned that is blank?

I wasn't born stupid - I had to study.

CooLDBA Hall of Fame Points: 3301 More actions · Answer 6

e.g. Result below Country Zip
	1
	1
	1
	2
	2
	2
	2
	2
	1
	1
	1
	1
	2
	2
	1
	1
	2
	2
	2
	2
	1
	1
	1
	2
	2
	2
	2
	2
	2
	1
	1
	1
	2
	2
	2
?	1
?	1
?	1
?	1
?	1
?	2
?	2
?	2
?	2
?	2
	1
	1
	2
	2
	2
	2
	2
	2
american	1
AMERICAN	1
AMERICAN	1

PW-201837 SSC-Insane Points: 20805 More actions · Answer 7

I suggest at this point you take a step back and concisely describe exactly what the problem is.

If there are blank column values in the resultset, that would imply the table contains empty strings in those columns. Is that a problem ? You tell us ...

Farrell Keough SSCoach Points: 17414 More actions · Answer 8

I agree with PW. For instance, your original code was:

select * from dbo.SAME_NAME_DOB_GENDER

where name is not NULL and name <> ''

Where is Name in your output?

I wasn't born stupid - I had to study.

CooLDBA Hall of Fame Points: 3301 More actions · Answer 9

yes, it is a problem that the resultset is having blank column values! I want to remove those blank values in my resultset.

Any ideas how to remove them?

i used NOT NULL and <> '', didn't work!

CooLDBA Hall of Fame Points: 3301 More actions · Answer 10

my result there is just an example.

you can assume that the name same with country

name = country

PW-201837 SSC-Insane Points: 20805 More actions · Answer 11

How do you know it's an empty string in the resultset, and not 1 or more spaces ?

'' <> ' ' <> ' '

Try trimming Conutry, so that empty strings, or strings of 1 or more spaces get eliminated:

Where country Is Not Null And Rtrim(Country) <> ''

CooLDBA Hall of Fame Points: 3301 More actions · Answer 12

CooLDBA

Hall of Fame

Points: 3301

February 11, 2005 at 3:56 pm

#541913

I tried Rtrim, still no luck!

Farrell Keough SSCoach Points: 17414 More actions · Answer 13

Maybe try SELECT LEN( Name), Name dbo.SAME_NAME_DOB_GENDER and see it some other character is actually in that field....

I wasn't born stupid - I had to study.

CooLDBA Hall of Fame Points: 3301 More actions · Answer 14

I tried that...

some len is 2, some len is 4, etc...

any solutions?