June 24, 2010 at 3:46 am
Hi ! Guys
I have a problem here in a SP.
The SP is like this :
USE IRISDW_B4_FEEDRUN
IF OBJECT_ID('Proc_PerfectJourneyReport_Technical03') IS NOT NULL
DROP PROC Proc_PerfectJourneyReport_Technical03;
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Subhro Mukherjee
-- Create date: 15/06/10
-- Description: Proc_PerfectJourneyReport_Technical03
-- =============================================
CREATE PROC Proc_PerfectJourneyReport_Technical03
@timespan VARCHAR(50)
AS
SET NOCOUNT ON;
BEGIN
SELECT cp.Device_Type_Code As DeviceType,COUNT(c.Container_Key) AS Journeys,
CASE
WHEN c.Perfect_Journey_Tech_Exception_Flag IS NULL
OR c.Perfect_Journey_Tech_Exception_Flag = 0
THEN COUNT(c.Container_Key) ELSE 0
END AS PerfectJourney
FROM
dbo.Container c
INNER JOIN dbo.Container_Perfect_Journey_Exceptions cp
ON c.Container_Key = cp.Container_Key
GROUP BY cp.Device_Type_Code,c.Perfect_Journey_Tech_Exception_Flag,
CASE
WHEN @timespan = 'YTD'
THEN DATEPART(Year,getdate())
WHEN @timespan = 'Q'+CAST(DATEPART(quarter,getdate()) AS VARCHAR)+' '+CAST(DATEPART(YEAR,getdate()) AS VARCHAR)
THEN DATEPART(quarter,getdate())
WHEN @timespan = CAST(DATENAME(MONTH,getdate()) AS VARCHAR)+' '+CAST(DATEPART(YEAR,getdate()) AS VARCHAR)
THEN DATEPART(MONTH,getdate())
ELSE 'Lifetime'
END
END
The action of stored procedure is like that I will enter a timespan of last year or last quarter or last month etc. The stored Procedure will generate data related to that.
But when ever I try to Execute the SP it is throwing an error in the runtime :
ERROR : Each GROUP BY expression must contain at least one column that is not an outer reference.
I really don't understand what its means. Please give me idea how to implement the concept.
Thanks
Subhro
June 24, 2010 at 3:53 am
June 24, 2010 at 3:57 am
The following might cause your problem (in select part):
CASE
WHEN c.Perfect_Journey_Tech_Exception_Flag IS NULL
OR c.Perfect_Journey_Tech_Exception_Flag = 0
THEN COUNT(c.Container_Key) ELSE 0
END AS PerfectJourney
Try to change it to:
SUM(
CASE
WHEN c.Perfect_Journey_Tech_Exception_Flag IS NULL
OR c.Perfect_Journey_Tech_Exception_Flag = 0
THEN 1 ELSE 0
END) AS PerfectJourney
June 24, 2010 at 4:01 am
Still it is giving the same error .
Is there any problem in the group by clause ?
Subhro
June 24, 2010 at 4:09 am
Shintu (6/24/2010)
Still it is giving the same error .Is there any problem in the group by clause ?
Subhro
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 24, 2010 at 4:23 am
Hello Morris !
I think it is quite clear what I want to say, for your reference mail I am repeating my problem and requesting you to please go through the SP and give me proper answer if you have.
I have a problem here in a SP Below
The SP is like this :
USE IRISDW_B4_FEEDRUN
IF OBJECT_ID('Proc_PerfectJourneyReport_Technical03') IS NOT NULL
DROP PROC Proc_PerfectJourneyReport_Technical03;
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Subhro Mukherjee
-- Create date: 15/06/10
-- Description: Proc_PerfectJourneyReport_Technical03
-- =============================================
CREATE PROC Proc_PerfectJourneyReport_Technical03
@timespan VARCHAR(50)
AS
SET NOCOUNT ON;
BEGIN
SELECT cp.Device_Type_Code As DeviceType,COUNT(c.Container_Key) AS Journeys,
CASE
WHEN c.Perfect_Journey_Tech_Exception_Flag IS NULL
OR c.Perfect_Journey_Tech_Exception_Flag = 0
THEN COUNT(c.Container_Key) ELSE 0
END AS PerfectJourney
FROM
dbo.Container c
INNER JOIN dbo.Container_Perfect_Journey_Exceptions cp
ON c.Container_Key = cp.Container_Key
GROUP BY cp.Device_Type_Code,c.Perfect_Journey_Tech_Exception_Flag,
CASE
WHEN @timespan = 'YTD'
THEN DATEPART(Year,getdate())
WHEN @timespan = 'Q'+CAST(DATEPART(quarter,getdate()) AS VARCHAR)+' '+CAST(DATEPART(YEAR,getdate()) AS VARCHAR)
THEN DATEPART(quarter,getdate())
WHEN @timespan = CAST(DATENAME(MONTH,getdate()) AS VARCHAR)+' '+CAST(DATEPART(YEAR,getdate()) AS VARCHAR)
THEN DATEPART(MONTH,getdate())
ELSE 'Lifetime'
END
END
The action of stored procedure is like that I will enter a timespan of last year or last quarter or last month etc. The stored Procedure will generate data related to that.
But when ever I try to Execute the SP it is throwing an error in the runtime :
ERROR : Each GROUP BY expression must contain at least one column that is not an outer reference.
I really don't understand what its means. Please give me idea how to implement the concept.
Thanks
Subhro
June 24, 2010 at 4:33 am
It's still a little unclear what you are trying to do, but I think it's a filter.
So,
WHEN @timespan = 'YTD'
you want to return data within the selected range, year to date - is this correct? If so, then the conditional construct should appear in your WHERE clause, not in the GROUP BY, and you need to identify which date column to filter on.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 24, 2010 at 7:51 am
The error you are getting is due to grouping by a constant result value of
CASE
WHEN @timespan = 'YTD'
THEN DATEPART(Year,getdate())
WHEN @timespan = 'Q'+CAST(DATEPART(quarter,getdate()) AS VARCHAR)+' '+CAST(DATEPART(YEAR,getdate()) AS VARCHAR)
THEN DATEPART(quarter,getdate())
WHEN @timespan = CAST(DATENAME(MONTH,getdate()) AS VARCHAR)+' '+CAST(DATEPART(YEAR,getdate()) AS VARCHAR)
THEN DATEPART(MONTH,getdate())
ELSE 'Lifetime'
END
Why are you doing that? For every row returned by SELECT the result of your CASE WHEN statement will be exactly the same and will not affect anything. You don't even return it in your select part!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply