NULL Data

Question

NULL Data

David Greene-305730

Old Hand

Points: 390
More actions
February 5, 2007 at 12:10 pm

#172019

Can anyone tell me why this Query will not return "ONLY" NULL data?

SELECT masterregis.lastname, masterregis.ssn, masterregis.companyname, masterregis.statecode, masterregis.subcompanyname, masterregis.[reason desc], [status table].statusdescr, masterregis.[term date], masterregis.dateofclaim, masterregis.finalstatus, [disney hearing dates].hearingdate, [disney hearing dates].hearingdecision
FROM masterregis, [disney hearing dates], [status table]
WHERE masterregis.claimstatus = [status table].claimstatusid and [status table].claimstatus is not NULL
Thanks for your help in advance...

David

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

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

Russel Loski SSCertifiable Points: 7059 More actions · Answer 1

"Can anyone tell me why this Query will not return "ONLY" NULL data?"

What makes you think that this query might return only null data? The only mention of null in the query states that a column should not be null.

You have three tables in you from clause, but only filter on two of them. The way I read this query as written:

You want all of the masterregis records that have a matching status table. Specifically the claimstatus in the status table must have a value. Also, you want to repeat each of these rows for all of the records in disney hearing date.

What is you want to see. Perhaps a small sample data: raw data followed by what you want to see in the results of your query.

Russel Loski, MCSE Business Intelligence, Data Platform

David Greene-305730 Old Hand Points: 390 More actions · Answer 2

Gee Whiz... well, I guess I'm in the right part of the forum... Newbies! HAH!! And I thought I was doing something great.

Anyway, I want to see all records from MasterRegis where the masterregis.claimstatus is not null.

Since writing this note to you, I did see one problem... the last part of the where should be "masterregis.claimstatus is not NULL" Is this correct?

Do you happen to know where I can get a good book on how to write queries for just about any situation I might come upon?

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

If you are in 2000 and below the best T-SQL programming books are by Ken Henderson, try the link below for his guidelines about writing stored procs. I have also used 2005 since beta one I would not recommend any programming books because most leave out the structural differences between 2000 and 2005. Hope this helps.

http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=4&rl=1

Kind regards,
Gift Peddie

Russel Loski SSCertifiable Points: 7059 More actions · Answer 4

I'm still not clear what you are trying to do and what you mean by null values. The change you provide actually doesn't change the query at all, since the condition "masterregis.claimstatus = [status table].claimstatusid" already covers the change you provide.

At this stage, I'm getting very mixed signals. I'm not sure what you want your query to do without sample source data and what you expect to see.

Russel Loski, MCSE Business Intelligence, Data Platform

David Greene-305730 Old Hand Points: 390 More actions · Answer 5

OK, I want to pull all the data from each of the fields mentioned in the SELECT statement from the tables mentioned in the FROM statement but only the records that do not have a NULL value in the masterregis.claimstatus fields of the masterregis table.

I hope I explained this better this time.

David

Farrell Keough SSCoach Points: 17414 More actions · Answer 6

I am a bit surprised at these reactions to you David. This is the Newbies forum, hence it is implied that you are not aware of requirments. Try this as well: Help us help you

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

Russel Loski SSCertifiable Points: 7059 More actions · Answer 7

When you run your original query do any of the records have a null masterregis.claimstatus? Also, is there are record in [status table] where claimstatusid has a value of '' (empty string)?

Russel Loski, MCSE Business Intelligence, Data Platform

David Greene-305730 Old Hand Points: 390 More actions · Answer 8

Yes, literally 1000's of them have a NULL value in the claimstatus field.

And NO, none of the fields in claimstatusid have (empty string) in a field.

Thanks,

David

Russel Loski SSCertifiable Points: 7059 More actions · Answer 9

I'm going to assume that you are using Query Analyzer.

In the Tools menu, select the Options menu. Click on the Connection Properties tab. About half way down there is a check box labeled "Set Ansi Nulls" I suspect that it is cleared. If it is, try setting the box.

Alternatively, you can run the following before your query to see if it works:

set Ansi_nulls on

Russel Loski, MCSE Business Intelligence, Data Platform

David Greene-305730 Old Hand Points: 390 More actions · Answer 10

I just read the "Help Us Help You" page, so I'm going to generate some samples of the data and put it someplace where you can get it...

Thanks for your efforts so far.

David

laker_42 SSCarpal Tunnel Points: 4785 More actions · Answer 11

David,

Try this & see if it gets what you want:

SELECT

masterregis.lastname,

masterregis.ssn,

masterregis.companyname,

masterregis.statecode,

masterregis.subcompanyname,

masterregis.[reason desc],

[status table].statusdescr,

masterregis.[term date],

masterregis.dateofclaim,

masterregis.finalstatus,

[disney hearing dates].hearingdate,

[disney hearing dates].hearingdecision

FROM masterregis

INNER JOIN [disney hearing dates]

ON --enter you join criteria here for this table

LEFT OUTER JOIN [status table]

ON masterregis.claimstatus = [status table].claimstatusid

Where [status table].claimstatus is not NULL

John

David Greene-305730 Old Hand Points: 390 More actions · Answer 12

Thanks for all your help guys, my bodd helped me figure it out. I did not want to go to my boss about it, but I finally had to break down and go to him because the deadline was drawing too close.

I now need to develop a new interface for a query I wrote a couple of weeks ago. Where should I go to ask questions about integrating a VB user interface with a query? Basically the query looks for data in about 56 different tables and either returns about 4 fields or returns a message saying person not found.

Thanks again,

David

Gift Peddie SSC Guru Points: 73570 More actions · Answer 13

Hi David,

It's me again most of the code you will need in Web interface using VB.NET is by Scott, try the link below for his blog print out the articles on the side bar and you are almost there. Post again if you still need help. Hope this helps.

http://www.scottonwriting.net/sowBlog/

Kind regards,
Gift Peddie

David Greene-305730 Old Hand Points: 390 More actions · Answer 14

Actually I only want to run this little app on the Local Area Network.