Cast statement problem - SQL Server Forums

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

Cast statement problem

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-22 : 11:26:21

My case statement isn't working. It is giving me this:


fo     reg     doc      clms    cos      filedate       ddsrcpt      title    title
019	c	S0L	129698	129698	05/20/2011	07/13/2011	T2	T2
278	c	S0L	129698	129698	06/02/2011	07/13/2011	T16	T16

I want to get this:


fo     reg     doc      clms    cos      filedate       ddsrcpt      title   
019	c	S0L	129698	129698	05/20/2011	07/13/2011	concurr

What am I missing?

This is the stored procedure I'm using:


select fo, reg, doc, clms, cos, filedate,ddsrcpt, title,
case when min(filedate) <> max(filedate) and fo <> fo then 'concurr' else min(title) end as title
from pendtest
group by fo, reg, doc, clms, cos, filedate, ddsrcpt, title

Here's all the table info:



CREATE TABLE [dbo].[Pendtest](
	[fo] [varchar](3) NOT NULL,
	[reg] [varchar](3) NULL,
	[doc] [varchar](4) NOT NULL,
	[clms] [char](6) NOT NULL,
	[cos] [char](6) NOT NULL,
	[FileDate] [datetime] NULL,
	[DDSRcpt] [datetime] NULL,
	[Title] [varchar](3) NOT NULL,
 CONSTRAINT [PK_Pendtest] PRIMARY KEY CLUSTERED 
(
	[fo] ASC,
	[clms] ASC,
	[cos] ASC,
	[Title] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


insert into pendtest
select '019', 'c', 'S0L', '129698', '129698', '05/20/2011', '07/13/2011', 'T2' union all
select '278', 'c', 'S0L', '129698', '129698', '06/02/2011', '07/13/2011', 'T16'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-22 : 11:53:29

take filedate and title out of the group by

No, you're never too old to Yak'n'Roll if you're too young to die.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-22 : 11:54:47

I did and got this error:

Column 'pendtest.FileDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-22 : 12:00:42

yes, you have to use min(filedate) or max(filedate) or both in your select list.

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-22 : 12:02:31

Not that it makes sense, but I have no errors. What version are you using



CREATE TABLE [dbo].[#Pendtest](
	[fo] [varchar](3) NOT NULL,
	[reg] [varchar](3) NULL,
	[doc] [varchar](4) NOT NULL,
	[clms] [char](6) NOT NULL,
	[cos] [char](6) NOT NULL,
	[FileDate] [datetime] NULL,
	[DDSRcpt] [datetime] NULL,
	[Title] [varchar](3) NOT NULL,
 CONSTRAINT [PK_Pendtest] PRIMARY KEY CLUSTERED 
(
	[fo] ASC,
	[clms] ASC,
	[cos] ASC,
	[Title] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

  SELECT  fo
		, reg
		, doc
		, clms
		, cos
		, filedate
		,ddsrcpt
		, title
		, CASE  WHEN MIN(filedate) <> MAX(filedate) AND fo <> fo 
				THEN 'concurr' 
				ELSE min(title) END AS title
    FROM #pendtest
GROUP BY fo, reg, doc, clms, cos, filedate, ddsrcpt, title
GO

DROP TABLE #Pendtest
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-22 : 12:25:50

All columns in group by can't be of different values. They will lead to different rows in the result set.
Please try to understand what a group by is doing.

No, you're never too old to Yak'n'Roll if you're too young to die.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-22 : 13:22:41

using 2005

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-22 : 13:28:52

WebFred thanks I understand now why you said to remove filedate and title. I added this and still get the same results:


select fo, reg, doc, clms, cos,min(filedate), ddsrcpt,
case when min(filedate) <> max(filedate) and fo <> fo then 'concurr' else min(title) end as title
from pendtest
group by fo, reg, doc, clms, cos,  ddsrcpt

Don't know why it wouldn't change.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-22 : 15:27:37

that means your actual data is different from what you posted

I think there're rows existing with varying values for ddsrcpt

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-23 : 21:18:12

Weird situation. Some how the data was wrong that I received. Thanks for all of your help.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-23 : 21:18:23

Weird situation. Some how the data was wrong that I received. Thanks for all of your help.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-23 : 21:18:44

Weird situation. Some how the data was wrong that I received. Thanks for all of your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-23 : 23:11:19

post all different scenarios and explain how you want output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources