Problem with Group by clause

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Problem with Group by clause

Problem with Group by clause

Rate Topic

Display Mode

Topic Options

Author

Message

Shintu

Posted Thursday, June 24, 2010 3:46 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 07, 2011 2:34 AM
Points: 35, Visits: 51

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

Post #942322

Eugene Elutin

Posted Thursday, June 24, 2010 3:53 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:59 AM
Points: 2,525, Visits: 4,324

[comment withdrawn]

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #942327

Eugene Elutin

Posted Thursday, June 24, 2010 3:57 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:59 AM
Points: 2,525, Visits: 4,324

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

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #942328

Shintu

Posted Thursday, June 24, 2010 4:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 07, 2011 2:34 AM
Points: 35, Visits: 51

Still it is giving the same error .

Is there any problem in the group by clause ?

Subhro

Post #942332

ChrisM@Work

Posted Thursday, June 24, 2010 4:09 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:21 AM
Points: 5,602, Visits: 10,950

Shintu (6/24/2010)

Still it is giving the same error .

Is there any problem in the group by clause ?

Subhro

Yes

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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
Exploring Recursive CTEs by Example Dwain Camps

Post #942337

Shintu

Posted Thursday, June 24, 2010 4:23 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 07, 2011 2:34 AM
Points: 35, Visits: 51

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

Post #942343

ChrisM@Work

Posted Thursday, June 24, 2010 4:33 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:21 AM
Points: 5,602, Visits: 10,950

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #942351

Eugene Elutin

Posted Thursday, June 24, 2010 7:51 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:59 AM
Points: 2,525, Visits: 4,324

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!

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #942453

« Prev Topic | Next Topic »

Permissions