|
Subject:
|
Warning: Null value is eliminated by an aggregate
|
|
Posted By:
|
hyder_master
|
Post Date:
|
4/17/2008 6:54:42 AM
|
I m using this SP in SQL 2005 and Getting this error plz help me
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[systek_salesRpt]
@username varchar(50) = NULL,
@RoleID int = NULL,
@ItemTitle varchar(50) = NULL,
@jumpid int
AS
BEGIN
CREATE TABLE #saletemp
(
SubscriptionID int,
Noofsales int,
subscriberID int ,
OwnerID int,
Rolename varchar(50),
Type varchar(50),
Itemid int,
Totalprice float,
Commission float,
RoleID int,
ItemTitle varchar(50),
CreatedDate DateTime,
Username varchar(50)
)
INSERT INTO #saletemp(SubscriptionID,Noofsales,subscriberID,OwnerID,Rolename,Type,Itemid,Totalprice,Commission,RoleID,ItemTitle,CreatedDate,UserName)
SELECT B.SubscriptionID,
COUNT(B.SubscriptionID) AS noofsales,
A.SubscriberID,A.UserID AS OwnerID,
C.RoleName,B.Type,
ISNULL(D.VideoID,'') AS ItemID,
ISNULL(SUM(B.price),0) AS TotalPrice,
ISNULL(SUM(A.Commission),0) AS Commission,A.RoleID,
D.VideoTitle as ItemTitle,B.CreatedDate,H.userName
FROM
(SELECT * FROM Systek_UserCommissions WHERE Systek_UserCommissions.Module = 'videos')A
LEFT OUTER JOIN
(SELECT * FROM Systek_Subscription WHERE Type='videos')B
ON A.SubscriberID = B.UserID
LEFT OUTER JOIN
(SELECT * FROM SIC_Roles )C
ON C.RoleID = A.RoleID
INNER JOIN
(SELECT * FROM systek_videos)D
ON D.VideoID = B.ProductID OR D.VideoID = A.itemid
LEFT OUTER JOIN
(SELECT * FROM systek_Series)E
ON E.SeriesId = D.SeriesId OR E.SeriesId = B.ProductID OR E.SeriesId = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM SIC_SmartThinker_Group)F
ON F.GroupID = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM Systek_VideoProducer)G
ON A.UserID = G.userID
LEFT OUTER JOIN
(SELECT * FROM sic_users)H
ON H.UserID = G.UserID OR B.UserID = H.UserID
GROUP BY
B.SubscriptionID,A.SubscriberID,A.UserID,C.RoleName,B.Type,D.VideoTitle,
E.SeriesId,F.GroupID,D.VideoID,A.RoleID,B.CreatedDate,H.UserName
UNION ALL
------------------------------------------------------------------series-------------------------------------------------------------------------
SELECT B.SubscriptionID,
COUNT(B.SubscriptionID) AS noofsales,
A.SubscriberID,A.UserID AS OwnerID,
C.RoleName,B.Type,
ISNULL(E.SeriesId,'') AS ItemID,
ISNULL(SUM(B.price),0) AS TotalPrice,
ISNULL(SUM(A.Commission),0) AS Commission,A.RoleID,
E.seriesTitle as ItemTitle,B.CreatedDate,H.userName
FROM
(SELECT * FROM Systek_UserCommissions WHERE Systek_UserCommissions.Module = 'series')A
LEFT OUTER JOIN
(SELECT * FROM Systek_Subscription WHERE Type='series')B
ON A.SubscriberID = B.UserID
LEFT OUTER JOIN
(SELECT * FROM SIC_Roles )C
ON C.RoleID = A.RoleID
LEFT OUTER JOIN
(SELECT * FROM systek_videos)D
ON D.VideoID = B.ProductID OR D.VideoID = A.itemid
LEFT OUTER JOIN
(SELECT * FROM systek_Series)E
ON E.SeriesId = D.SeriesId OR E.SeriesId = B.ProductID OR E.SeriesId = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM SIC_SmartThinker_Group)F
ON F.GroupID = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM Systek_VideoProducer)G
ON A.UserID = G.userID
LEFT OUTER JOIN
(SELECT * FROM sic_users)H
ON H.UserID = G.UserID OR B.UserID = H.UserID
GROUP BY
B.SubscriptionID,A.SubscriberID,A.UserID,C.RoleName,B.Type,D.VideoTitle,
E.SeriesId,F.GroupID,D.VideoID,A.RoleID,E.seriesTitle,B.CreatedDate,H.userName
UNION ALL
-----------------------------------------------------------group id----------------------------------------------------------------------------
SELECT B.SubscriptionID,
COUNT(B.SubscriptionID) AS noofsales,
A.SubscriberID,A.UserID AS OwnerID,
C.RoleName,B.Type,
ISNULL(F.GroupID,'') AS ItemID,
ISNULL(SUM(B.price),0) AS TotalPrice,
ISNULL(SUM(A.Commission),0) AS Commission,A.RoleID,
F.GroupName AS ItemTitle,B.CreatedDate,H.userName
FROM
(SELECT * FROM Systek_UserCommissions WHERE Systek_UserCommissions.Module = 'Groups')A
LEFT OUTER JOIN
(SELECT * FROM Systek_Subscription WHERE Type='Groups')B
ON A.SubscriberID = B.UserID
LEFT OUTER JOIN
(SELECT * FROM SIC_Roles )C
ON C.RoleID = A.RoleID
LEFT OUTER JOIN
(SELECT * FROM systek_videos)D
ON D.VideoID = B.ProductID OR D.VideoID = A.itemid
LEFT OUTER JOIN
(SELECT * FROM systek_Series)E
ON E.SeriesId = D.SeriesId OR E.SeriesId = B.ProductID OR E.SeriesId = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM SIC_SmartThinker_Group)F
ON F.GroupID = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM Systek_VideoProducer)G
ON A.UserID = G.userID
LEFT OUTER JOIN
(SELECT * FROM sic_users)H
ON H.UserID = G.UserID OR B.UserID = H.UserID
GROUP BY
B.SubscriptionID,A.SubscriberID,A.UserID,C.RoleName,B.Type,D.VideoTitle,
E.SeriesId,F.GroupID,D.VideoID,C.RoleID,A.RoleID,F.GroupName,B.CreatedDate,H.username
----------------------------------------------------removing duplicate subscrptionid---------------------------------------------------------
DECLARE @Count int
DECLARE @SubsCriptionID AS int
DECLARE vendor_cursor CURSOR
FOR
SELECT SubscriptionID,Count(SubscriptionID) - 1 FROM #saletemp Where RoleName <> 'smartIcast' GROUP BY SubscriptionID HAVING Count(subscriptionid) > 1
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor INTO @SubsCriptionID,@Count
WHILE @@FETCH_STATUS = 0
BEGIN
SET ROWCOUNT @Count
DELETE FROM #saletemp WHERE #saletemp.SubscriptionID = @SubsCriptionID
SET ROWCOUNT 0
FETCH NEXT FROM vendor_cursor INTO @SubsCriptionID,@Count
END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor
----------------------------------------------------------------------------------------------------------------------------------------------
IF @jumpid = 0
BEGIN
SELECT subscriptionid,noofsales,subscriberid,ownerid,Rolename,Type,itemID,
'$' + (CAST(ROUND(TotalPrice,2) AS Varchar)) AS TotalPrice,
'$' + (CAST(ROUND(Commission,2) AS Varchar)) AS Commission,
RoleID,ItemTitle,CreatedDate,UserName
FROM #saletemp
END
IF @jumpid = 1
BEGIN
SELECT subscriptionid,noofsales,subscriberid,ownerid,Rolename,Type,itemID,
'$' + (CAST(ROUND(TotalPrice,2) AS Varchar)) AS TotalPrice,
'$' + (CAST(ROUND(Commission,2) AS Varchar)) AS Commission,
RoleID,ItemTitle,CreatedDate,UserName
FROM #saletemp
Where
RoleID = @RoleID
OR
ItemTitle LIKE '%'+ @ItemTitle + '%'
OR
username LIKE '%' + @username + '%'
END
DROP TABLE #saletemp
END
hyder_master
hyder_master
|
|
Reply By:
|
Jeff Moden
|
Reply Date:
|
4/17/2008 8:06:04 AM
|
It's not an error... it's an "advisory" that's saying "HEY! I ran into some NULLs here! Are you sure that's right because 3rd normal form isn't supposed to have NULLs". Provided your code and data is doing exactly what you want, you can suppress the message using SET ANSI_WARNINGS OFF... might be a better idea for you to troubleshoot your data and code to make sure it's correct.
--Jeff Moden
|
|
