Removing blanks from my looped queries

Last Post 10 Apr 2002 11:46 AM by Rich_bailey. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Rich_bailey
New Member
New Member

--
10 Apr 2002 11:46 AM

I have the following query, which uses a cursor to create seperated record sets for a report. However, I have run into a problem in that it is pulling up blanks for some users since they do not fit into the criteria but are part of the cursor.

Can anyone tell me how to have the cursor/query not display anything if the 'Name' does not have any results based on the rest of the query, but is still part of the cursor list.

I have Included an example of the data the query displays below the query. Not the 0 results blank to which I am refering.


DECLARE @name varchar(32)
DECLARE name_cursor CURSOR FOR SELECT DISTINCT clientusername FROM usernames
ORDER BY clientusername
OPEN name_cursor
FETCH NEXT FROM name_Cursor INTO @name
WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) BEGIN
SELECT TOP 5 s.clientusername AS Name, count(*) as Hits,
S.desthost AS DestinationSite FROM Sitelog S WHERE S.clientusername = @name
and s.desthost in (select desthost from desthost)
GROUP BY S.clientusername,S.desthost ORDER BY Hits desc
END FETCH NEXT FROM name_Cursor INTO @name END
CLOSE name_cursor DEALLOCATE name_cursor


Name Hits DestinationSite
-------------------------------- ----------- --------------------
Domain\BOB 30 www.google.com
Domain\BOB 28 www.sarc.com
Domain\BOB 18 www.officeparts.com
Domain\BOB 7 www.symantec.com
Domain\BOB 7 www.zthing.com

(5 row(s) affected)

Name Hits DestinationSite
-------------------------------- ----------- --------------------
(0 row(s) affected)


Name Hits DestinationSite
-------------------------------- ----------- ---------------------------
Domain\SARAH 542 www.paypal.com
Domain\SARAH 117 www.ibm.com
Domain\SARAH 79 www.flipdog.com
Domain\SARAH 43 www.commission-junction.com
Domain\SARAH 36 www.mybeautycenter.com
(5 row(s) affected)


Acceptable Use Policy
---