Problems with 'like statement' and NULL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problems with 'like statement' and NULL

Author

Topic

joblenis
Starting Member

29 Posts

Posted - 2007-03-29 : 17:46:44

I searched through the forums thinking this may have been brought up before, but I cannot find a post the directs to my problem.

I have a lookup table (lookup.os) which defines various operating systems. It has a "OSID" field which is just a primary key numbering, a "OSInstr" field which is used in a 'like statement' in a cursor used later on and "OSName" which is the full name of the OS.

ID| BROWSERNAME | INSTR
*********************************
1 | Windows XP | Windows+NT+5.1
2 | Windows 2003 | Windows+NT+5.2
3 | Windows Vists | Windows+NT+6.0
.....
19 | Unknown Operating System | <blank>

I have a cursor loop that goes through another table (tblIISLog_ALL_OS) and searches through the instr of the (lookup.os) table to add to a stats of a third table (statistics.os). It works fine going through the loop, except there is a problem with the last value in the (lookup.os) table. If the value does not match the rest, it will select value "19 - Unknown Operating System" which is correct, but if the value is null it will not be selected. I take it 'NULL' >< a blank like statement. Here is what I have now. I am open to suggestions of what I should do. I was thinking maybe removing the "19 - Unknown Operating System" from the (lookup.os) table and after the first cursor loop has run, run it again and everything remaining it will just put in as "19 - Unknown Operating System". What do you think?

DECLARE @TempID int,
@TempOSInStr varchar(50),
@TempOSName varchar(100),
@TempCount int
DECLARE loopc CURSOR FOR
SELECT [OSID],
[OSInStr],
[OSName]
FROM [IISLOG_REPORTS].[dbo].[lookup.os]

OPEN loopc

FETCH NEXT FROM loopc INTO @TempID,
@TempOSInStr,
@TempOSName

SET @TempID = 0
SET @TempCount = 0
WHILE @@fetch_status = 0
BEGIN

---------------------
SELECT [date], @TempOSName, count(1)
FROM [IISLOG_REPORTS].[dbo].[tblIISLog_ALL_OS]
WHERE '%' + [csUserAgent] + '%' like '%' + @TempOSInStr + '%'
GROUP BY [date]

INSERT INTO [IISLOG_REPORTS].[statistics].[os]
([TotalDate]
,[OSID]
,[OSTotal])
SELECT [date], @TempID, count(1)
FROM [IISLOG_REPORTS].[dbo].[tblIISLog_ALL_OS]
WHERE '%' + [csUserAgent] + '%' like '%' + @TempOSInStr + '%'
GROUP BY [date]

DELETE FROM [IISLOG_REPORTS].[dbo].[tblIISLog_ALL_OS]
WHERE '%' + [csUserAgent] + '%' like '%' + @TempOSInStr + '%'

----------------------
FETCH NEXT FROM loopc INTO @TempID,
@TempOSInStr,
@TempOSName
---------------------
END
CLOSE loopc
DEALLOCATE loopc

Thanks for the assistance. I hope I provided all the necessary information and explained it ok.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 17:52:44

Are these related?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81016

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 17:54:35

Try to post sample data for all relevant tables, and also post your expected output.

Here is an excellent example of how to do this!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81373

Peter Larsson
Helsingborg, Sweden

joblenis
Starting Member

29 Posts

Posted - 2007-03-30 : 10:47:11

quote:
Originally posted by Peso

Are these related?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81016

Peter Larsson
Helsingborg, Sweden

They are related, but not the same problem. I got the instr/cursor to work from the previous problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources