Grouping record based on a condtion

TechnologyTypeSize

XYZA200

XYZ1A200

XYZ2A300

XYZ3A300

ABC1X238

ABC2X238

PQRB320

MNOC330

I have written a query on a table whose output will look like the above. I need to know if i should store this in a record set or create a temp table to get the following fuctionality.

Now I need to concatenate the Technology based on Type and size.

As you can see in Type A we have two sizes 200 and 300.

We need to group the Technology of type A with same size together.

So the output of the procedure should be

XYZ + XYZ1

XYZ2+ XYZ3

ABC1 + ABC2etc.

We need to concatenate the Technology string with the next technology if they have the same type and size.

Can somebody please help or send any sample code.

Any help is greatly appreciated

Thanks

Swapna

CTE solution for SQL Server 2005:

With MyCTE(Size, Type, col1, col2, myNum) AS

(

SELECT a.Size, a.Type, CONVERT(varchar(50), MIN(RTRIM(a.Technology))) as col1, CONVERT(varchar(50),RTRIM((a.Technology))) as col2, 1 as myNum

FROM techTable AS a GROUP BY a.Size, a.Type, CONVERT(varchar(50),RTRIM(a.Technology))

UNION ALL

SELECT b.Size, b.Type, CONVERT(varchar(50), RTRIM(b.Technology)) as col1, CONVERT(varchar(50), (c.col2 + '+' + RTRIM(b.Technology))) as col2, c.myNum+1 as myNum

FROM techTable AS b INNER JOIN MyCTE c ON b.Size=c.Size AND b.Type= c.Type

WHERE b.Technology>c.col1

)

SELECT a.col2 As Technology_combined, a.Size, a.Type FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Size, a1.Type FROM MyCTE a1

GROUP BY a1.Size, a1.Type) b on b.Size=a.Size AND b.Type= a.Type AND a.myNum= b.myNumMax

|||

you I am new to stored procedures...and working with the databse...so could you please explain the above code...I could not get much from it...Will the loop through the sample table I mentioned and return a set of concatenated Technology values....Please get back.

Thanks for your reply

Swapna

|||

and more over the data in the table is just an example...we are in no way concerned with the data in Technology Column...all we need to do is group the technology column data which have the same Type and Size

TechnologyTypeSize

XYZA200

ABCA200

ABC1A300

XYZ3A300

MNO1X238

ABC2X238

PQRB320

MNOC330

so the output should be XYZ+ABC

ABC1+XYZ3

MNO1+ABC2.... I hope I am clear now.

Please reply...Can we use cursors to do this...can someone explain how to use cursors for the above functionality

Thanks

|||

Hello:

The "techTable" would be the name of your table which holds your data.

The CTE code I posted will work in a recursive fasion.

If you are using SQL Server 2005, you can give the code a try run (remember to change the "techTable" to your table name).

|||

--CREATE TABLE MyTable(Technology VARCHAR(MAX), Type char(10), Size int)

--Enter the values suggested

--Run the following code

DECLARE @.Type CHAR(1)

DECLARE @.Size INT

DECLARE @.MyNewString CHAR(11)

DECLARE @.MyNewString2 VARCHAR(MAX)

SET @.MyNewString2 = ''

--Replace MyTable with your tablename

--Replace Technology, Type, Size with your field names

CREATE TABLE #Temp(MyNewString VARCHAR(MAX))

DECLARE c1 CURSOR FOR

SELECT mt.Type, mt.Size

FROM MyTable mt

OPEN c1

FETCH NEXT FROM c1

INTO @.Type, @.Size

WHILE @.@.FETCH_STATUS = 0

BEGIN

DECLARE c2 CURSOR FOR

SELECT Technology from MyTable Where size = @.Size and type = @.Type

OPEN c2

FETCH NEXT FROM c2

INTO @.MyNewString

WHILE @.@.FETCH_STATUS = 0

BEGIN

SET @.MyNewString2 = LTRIM(RTRIM(@.MyNewString2)) + LTRIM(RTRIM(@.MyNewString))

FETCH NEXT FROM c2

INTO @.MyNewString

END

CLOSE c2

DEALLOCATE c2

INSERT INTO #Temp(MyNewString) VALUES(@.MyNewString2)

SET @.MyNewString2 = ''

FETCH NEXT FROM c1

INTO @.Type, @.Size

END

CLOSE c1

DEALLOCATE c1

SELECT * from #Temp

GROUP BY MyNewString

DROP TABLE #temp

|||

limno wrote:

CTE solution for SQL Server 2005:

With MyCTE(Size, Type, col1, col2, myNum) AS

(

SELECT a.Size, a.Type, CONVERT(varchar(50), MIN(RTRIM(a.Technology))) as col1, CONVERT(varchar(50),RTRIM((a.Technology))) as col2, 1 as myNum

FROM techTable AS a GROUP BY a.Size, a.Type, CONVERT(varchar(50),RTRIM(a.Technology))

UNION ALL

SELECT b.Size, b.Type, CONVERT(varchar(50), RTRIM(b.Technology)) as col1, CONVERT(varchar(50), (c.col2 + '+' + RTRIM(b.Technology))) as col2, c.myNum+1 as myNum

FROM techTable AS b INNER JOIN MyCTE c ON b.Size=c.Size AND b.Type= c.Type

WHERE b.Technology>c.col1

)

SELECT a.col2 As Technology_combined, a.Size, a.Type FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Size, a1.Type FROM MyCTE a1

GROUP BY a1.Size, a1.Type) b on b.Size=a.Size AND b.Type= a.Type AND a.myNum= b.myNumMax

This code is equivalent to my nested cursor approach and works, but I agree is a tad bit confusing...but nice work all the same.|||

You don't need to use cursors to get the results. Using cursors is often inefficient and consumes more resources than necessary. Very few problems require cursor based solutions and if you don't know how to use cursors that is actually good. :-) You can learn the basics of SQL to begin with than cursors.

If you are using SQL Server 2005 you can use below approach which will be faster than CTE and slightly simpler.

select t2.Type

, t2.Size

, max(case t2.seq when 1 then t1.Technology end)

+ max(case t2.seq when 2 then '+' + t2.Technology else '' end) as Technology

from (

select t1.Type, t1.Technology, t1.Size

, ROW_NUMBER() OVER(partition by t1.Type, t1.Size order by t1.Technology) as seq

from tbl as t1

) as t2

group by t2.Type, t2.Size;

You can use similar logic in older versions of SQL Server also since they don't have the ROW_NUMBER() function.

Below working query uses pubs authors table and you can do the same based on your table schema.

select a2.city, a2.state
, max(case a2.seq when 1 then a2.au_id else '' end)
+ max(case a2.seq when 2 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 3 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 4 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 5 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 6 then ', ' + a2.au_id else '' end) as au_ids
from (
select a1.city, a1.state, a1.au_id, row_number() over(partition by a1.city, a1.state order by a1.au_id) as seq
from authors as a1
) as a2
group by a2.city, a2.state
order by a2.state, a2.city;

|||

Umachandar Jayachandran - MS wrote:

You don't need to use cursors to get the results. Using cursors is often inefficient and consumes more resources than necessary. Very few problems require cursor based solutions and if you don't know how to use cursors that is actually good. :-) You can learn the basics of SQL to begin with than cursors.

If you are using SQL Server 2005 you can use below approach which will be faster than CTE and slightly simpler.

select t2.Type

, t2.Size

, min(case t2.seq when 1 then t1.Technology end)

+ min(case t2.seq when 2 then '+' + t2.Technology else '' end) as Technology

from (

select t1.Type, t1.Technology, t1.Size

, ROW_NUMBER() OVER(partition by t1.Type, t1.Size order by t1.Technology) as seq

from tbl as t1

) as t2

group by t2.Type, t2.Size;

You can use similar logic in older versions of SQL Server also since they don't have the ROW_NUMBER() function.

Not knowing cursors is a good thing? Can we go a step further with your logic and say not knowing SQL is a good thing? Use ADO?

...and could you post some working code. I'm interested in this approach but getting errors.

Thanks,

Adamus

|||

Umachandar Jayachandran - MS wrote:

You don't need to use cursors to get the results. Using cursors is often inefficient and consumes more resources than necessary. Very few problems require cursor based solutions and if you don't know how to use cursors that is actually good. :-) You can learn the basics of SQL to begin with than cursors.

If you are using SQL Server 2005 you can use below approach which will be faster than CTE and slightly simpler.

select t2.Type

, t2.Size

, min(case t2.seq when 1 then t1.Technology end)

+ min(case t2.seq when 2 then '+' + t2.Technology else '' end) as Technology

from (

select t1.Type, t1.Technology, t1.Size

, ROW_NUMBER() OVER(partition by t1.Type, t1.Size order by t1.Technology) as seq

from tbl as t1

) as t2

group by t2.Type, t2.Size;

You can use similar logic in older versions of SQL Server also since they don't have the ROW_NUMBER() function.

I unmarked this as the answer because the poster requested a cursor approach.|||

Not using procedural logic when dealing with SQL is a good thing. Yes, you can use ADO/client-side code to do this but it will be very slow and inefficient. If you have a table that contains say millions of rows you will be moving those rows from client to server for each user and performing the logic on the client side. Moreover, you have to implement lot of specific logic on the client side whereas the SQL language has built-in functionality / primitives to solve complex problems easily.

Anyway, here is a query that uses pubs authors table:

select a2.city, a2.state
, max(case a2.seq when 1 then a2.au_id else '' end)
+ max(case a2.seq when 2 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 3 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 4 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 5 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 6 then ', ' + a2.au_id else '' end) as au_ids
from (
select a1.city, a1.state, a1.au_id, row_number() over(partition by a1.city, a1.state order by a1.au_id) as seq
from authors as a1
) as a2
group by a2.city, a2.state
order by a2.state, a2.city;

The query produces a comma-separated list of author ids for each state and city combination similar to the problem.

Grouping by hour, day, month, etc

I have tables which record data entered by six users.
I would like to creat a query which will return the number of entries
created by each user. The UserId is recorded for each record along with a
date stamp.
I would like to be able to group these results by hour, day, etc.Please post DDL, sample data, and sample output...
http://www.aspfaq.com/etiquette.asp?id=5006
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I have tables which record data entered by six users.
> I would like to creat a query which will return the number of entries
> created by each user. The UserId is recorded for each record along with a
> date stamp.
> I would like to be able to group these results by hour, day, etc.
>|||SELECT Count(EntryKey), UserID, DatePart(hh,DateTimeStamp) as TheHour,
DatePart(dd,DateTimeStamp) as TheDay, DatePart(mm,DateTimeStamp) as
TheMonth, (yy, DateTimeStamp) as TheYear
FROM TheEntryTable
--WHERE UserID = 1
GROUP BY UserID, DatePart(hh,DateTimeStamp), DatePart(dd,DateTimeStamp),
DatePart(mm,DateTimeStamp), (yy, DateTimeStamp)
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I have tables which record data entered by six users.
> I would like to creat a query which will return the number of entries
> created by each user. The UserId is recorded for each record along with a
> date stamp.
> I would like to be able to group these results by hour, day, etc.
>|||CREATE TABLE [ImagePointers] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[TrackablesId] [int] NULL CONSTRAINT [DF__Temporary__Track__22751F6C]
DEFAULT (0),
[TrackablesRecordVersion] [smallint] NULL CONSTRAINT
[DF__Temporary__Track__236943A5] DEFAULT (0),
[ScanDirectoriesId] [int] NULL CONSTRAINT [DF__Temporary__ScanD__245D67DE]
DEFAULT (0),
[ScanBatchesId] [int] NULL CONSTRAINT [DF__Temporary__ScanB__25518C17]
DEFAULT (0),
[ScanSequence] [int] NULL CONSTRAINT [DF__Temporary__ScanS__2645B050]
DEFAULT (0),
[FileName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ScanDateTime] [datetime] NULL ,
[PageNumber] [int] NULL CONSTRAINT [DF__Temporary__PageN__2739D489] DEFAULT
(0),
[CRC] [int] NULL CONSTRAINT [DF__TemporaryUp__CRC__282DF8C2] DEFAULT (0),
[Orientation] [smallint] NULL CONSTRAINT [DF__Temporary__Orien__29221CFB]
DEFAULT (0),
[Skew] [float] NULL CONSTRAINT [DF__TemporaryU__Skew__2A164134] DEFAULT
(0),
[Front] [bit] NOT NULL CONSTRAINT [DF__Temporary__Front__2B0A656D] DEFAULT
(0),
[ImageHeight] [smallint] NULL CONSTRAINT [DF__Temporary__Image__2BFE89A6]
DEFAULT (0),
[ImageWidth] [smallint] NULL CONSTRAINT [DF__Temporary__Image__2CF2ADDF]
DEFAULT (0),
[ImageSize] [int] NULL CONSTRAINT [DF__Temporary__Image__2DE6D218] DEFAULT
(0),
[BarCodeCount] [smallint] NULL CONSTRAINT [DF__Temporary__BarCo__2EDAF651]
DEFAULT (0),
[BarCodes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrgDirectoriesId] [int] NULL ,
[OrgFileName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[upsize_ts] [timestamp] NULL ,
[PageCount] [int] NULL ,
[OrgFullPath] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AddedToFTS] [tinyint] NULL CONSTRAINT [DF__ImagePoin__Added__44EA3301]
DEFAULT (0),
[AddedToOCR] [tinyint] NULL CONSTRAINT [DF__ImagePoin__Added__47C69FAC]
DEFAULT (0),
CONSTRAINT [ImagePointers_PK] PRIMARY KEY NONCLUSTERED
(
[Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [ScanBatches] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[BatchStartDateTime] [datetime] NULL ,
[PageCount] [int] NULL CONSTRAINT [DF__Temporary__PageC__45544755] DEFAULT
(0),
[DocumentCount] [int] NULL CONSTRAINT [DF__Temporary__Docum__46486B8E]
DEFAULT (0),
[BelowDeleteSizeCount] [smallint] NULL CONSTRAINT
[DF__Temporary__Below__473C8FC7] DEFAULT (0),
[RescannedCount] [int] NULL CONSTRAINT [DF__Temporary__Resca__4830B400]
DEFAULT (0),
[AutoIndexedCount] [int] NULL CONSTRAINT [DF__Temporary__AutoI__4924D839]
DEFAULT (0),
[LastScanSequence] [int] NULL CONSTRAINT [DF__Temporary__LastS__4A18FC72]
DEFAULT (0),
[ScanRulesIdUsed] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[UserName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [ScanBatches_PK] PRIMARY KEY NONCLUSTERED
(
[Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
Select ScanBatches.UserName,
ImagePointers.Scandatetime
from ImagePointers, scanbatches
where ImagePointers.ScanBatchesId = ScanBatches.Id
and filename like 'Y%' and Scandatetime > '2005-01-23' and Scandatetime <
'2005-01-25'
and UserName like 't%'
Order by ImagePointers.Scandatetime
tjones 2005-01-24 08:48:19.000
tjones 2005-01-24 08:50:35.000
tjones 2005-01-24 08:50:47.000
tjones 2005-01-24 08:50:56.000
tjones 2005-01-24 08:51:02.000
tjones 2005-01-24 08:51:04.000
tjones 2005-01-24 08:51:28.000
tjones 2005-01-24 08:51:35.000
Of course, what I would like to produce is the number of records produced by
any user for any unit of time like records per hour by each user. There are
currently six users.
Thanks
Rich
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ucX0yOpAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Please post DDL, sample data, and sample output...
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Richard Lawson" <nospam@.nospam.com> wrote in message
> news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
a
>|||"Richard Lawson" <nospam@.nospam.com> wrote in message
news:eXWzSzpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Of course, what I would like to produce is the number of records produced
by
> any user for any unit of time like records per hour by each user. There
are
> currently six users.
For a per-hour report, you could do something similar to David Buchanan's
solution:
Select ScanBatches.UserName,
CONVERT(CHAR(14), ImagePointers.Scandatetime, 120) + '00',
COUNT(*) AS Total
from ImagePointers, scanbatches
where ImagePointers.ScanBatchesId = ScanBatches.Id
and filename like 'Y%' and Scandatetime > '2005-01-23' and Scandatetime <
'2005-01-25'
and UserName like 't%'
GROUP BY ScanBatches.UserName,
CONVERT(CHAR(14), ImagePointers.Scandatetime, 120) + '00'
Order by ImagePointers.Scandatetime
You can change the CONVERT to get different granularities.
... That will show you only hours that actually have data. To see hours
that didn't have data, you should implement a calendar table of some sort.
Here's some basic reading on the topic:
http://www.aspfaq.com/show.asp?id=2519
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Group Total..

I'm working on a Financial Report which contains a column "XYZ" , its value
is calculated from a formula by passing the row's record id and commission
rate. (the formula is inside a custom dll). The values are correctly
computed. Now, the footer should display the total of all the rows in the
group.
for instance:
"Unit" "BrandName" "XYZ Total" "Comments"
Sodas
Pepsi $361,000 gfyeefyefffee
Coca Cola $475,250 djfdfjdfddddd
RCola $28,757 re8reruejreerr
fdfsfnfsfssf
_________________________________________
Total: $ 865,007
Each of the "XYZ Total" in the above example, uses an expression as = FindTotal(recID!value, comm_rate!value)
In this case, how do I get the total in the footer? How to recursively add
the FindTotal expression when it contains the row's unique record id?
Thanks
P.S. The above data is a sample data. The actual report contains 3 different
levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
Transaction TitleI recently came across a new software, that I think you might want to look into.
www.simx.com/simx/home_report%20manager.htm
Works with SQL Server, and I was able to do reporting much like what you are describing.
"newmem" <"" wrote:
> I'm working on a Financial Report which contains a column "XYZ" , its value
> is calculated from a formula by passing the row's record id and commission
> rate. (the formula is inside a custom dll). The values are correctly
> computed. Now, the footer should display the total of all the rows in the
> group.
> for instance:
> "Unit" "BrandName" "XYZ Total" "Comments"
> Sodas
> Pepsi $361,000 gfyeefyefffee
> Coca Cola $475,250 djfdfjdfddddd
> RCola $28,757 re8reruejreerr
> fdfsfnfsfssf
> _________________________________________
> Total: $ 865,007
> Each of the "XYZ Total" in the above example, uses an expression as => FindTotal(recID!value, comm_rate!value)
> In this case, how do I get the total in the footer? How to recursively add
> the FindTotal expression when it contains the row's unique record id?
> Thanks
> P.S. The above data is a sample data. The actual report contains 3 different
> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> Transaction Title
>
>|||While I appreciate your eagerness to help, I think that most people would
prefer that you refrain from advertising other products in a forum dedicated
to SQL Server Reporting Services. If you start a SIMX newsgroup, I promise
not to post there. :)
That being said, you should be able to define a custom field that does the
calculation and then referce the custom field in a sum in the group footer.
Presumably, you only need to add values from the inner group as the outer
group is just summary. If you want it to do parent / child hierarcy
aggregates, you need to use the recursive keyword.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
>I recently came across a new software, that I think you might want to look
>into.
> www.simx.com/simx/home_report%20manager.htm
> Works with SQL Server, and I was able to do reporting much like what you
> are describing.
> "newmem" <"" wrote:
>> I'm working on a Financial Report which contains a column "XYZ" , its
>> value
>> is calculated from a formula by passing the row's record id and
>> commission
>> rate. (the formula is inside a custom dll). The values are correctly
>> computed. Now, the footer should display the total of all the rows in the
>> group.
>> for instance:
>> "Unit" "BrandName" "XYZ Total" "Comments"
>> Sodas
>> Pepsi $361,000 gfyeefyefffee
>> Coca Cola $475,250 djfdfjdfddddd
>> RCola $28,757 re8reruejreerr
>> fdfsfnfsfssf
>> _________________________________________
>> Total: $ 865,007
>> Each of the "XYZ Total" in the above example, uses an expression as =>> FindTotal(recID!value, comm_rate!value)
>> In this case, how do I get the total in the footer? How to recursively
>> add
>> the FindTotal expression when it contains the row's unique record id?
>> Thanks
>> P.S. The above data is a sample data. The actual report contains 3
>> different
>> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
>> Transaction Title
>>|||Thanks Brian.
Can you give me an example of using a custom field and using the Recusrive
keyword? If there is a sample in BOL, then pls provide any reference/links
(I wasn't able to locate any help on this topic)
appreciate it.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
> While I appreciate your eagerness to help, I think that most people would
> prefer that you refrain from advertising other products in a forum
dedicated
> to SQL Server Reporting Services. If you start a SIMX newsgroup, I promise
> not to post there. :)
> That being said, you should be able to define a custom field that does the
> calculation and then referce the custom field in a sum in the group
footer.
> Presumably, you only need to add values from the inner group as the outer
> group is just summary. If you want it to do parent / child hierarcy
> aggregates, you need to use the recursive keyword.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
> >I recently came across a new software, that I think you might want to
look
> >into.
> >
> > www.simx.com/simx/home_report%20manager.htm
> >
> > Works with SQL Server, and I was able to do reporting much like what you
> > are describing.
> >
> > "newmem" <"" wrote:
> >
> >> I'm working on a Financial Report which contains a column "XYZ" , its
> >> value
> >> is calculated from a formula by passing the row's record id and
> >> commission
> >> rate. (the formula is inside a custom dll). The values are correctly
> >> computed. Now, the footer should display the total of all the rows in
the
> >> group.
> >> for instance:
> >>
> >> "Unit" "BrandName" "XYZ Total" "Comments"
> >> Sodas
> >> Pepsi $361,000 gfyeefyefffee
> >> Coca Cola $475,250 djfdfjdfddddd
> >> RCola $28,757 re8reruejreerr
> >>
> >> fdfsfnfsfssf
> >> _________________________________________
> >> Total: $ 865,007
> >>
> >> Each of the "XYZ Total" in the above example, uses an expression as => >> FindTotal(recID!value, comm_rate!value)
> >> In this case, how do I get the total in the footer? How to recursively
> >> add
> >> the FindTotal expression when it contains the row's unique record id?
> >>
> >> Thanks
> >>
> >> P.S. The above data is a sample data. The actual report contains 3
> >> different
> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> >> Transaction Title
> >>
> >>
> >>
>

Group Min Record

Hello Guyz,

A small problem here, I have the below table and I need to group and display the record that has the minimum value in the table (this table is derived from a query that permutates some records to give me this result).

F1 F2 F3
QQQ C 2
QQQ B 1
QQQ A 3

expected result:
QQQ B 1

my result:
when I group by F1, First(F2) and MIN(F3):
QQQ C 1

when I group by F1, MIN(F2) and MIN(F3):
QQQ A 1

when I group by F1, F2 and MIN(F3):
QQQ C 2
QQQ B 1
QQQ A 3

Any help would be very much appreciated..

CyherusYou don't GROUP BY the aggregate for one thing. What you need is something like this in this particular example:

SELECT
t1.F1,
t1.F2,
t1.F3
FROM
table t1
INNER JOIN (
SELECT MIN(F3) AS F3 FROM table) t2 ON t1.F3 = t2.F3

This is assuming you're using SQL Server, which you probably aren't.

Group data by time slot

I would like to prepare a SQL such that I can perform query on a table
and count the number of record per time slot. The time slot is 2
seconds each, but the beginning time of each time slot is not fix, it
is determined by the create_date of the record, and this is the
example:
Raw Data:
seq cat create_date
-- -- --
151 A 2006-01-25 14:14:20.827
152 B 2006-01-25 14:14:20.983
161 A 2006-01-25 14:14:22.390
162 B 2006-01-25 14:14:22.543
171 A 2006-01-25 14:14:23.997
172 B 2006-01-25 14:14:24.153
181 A 2006-01-25 14:14:25.560
182 B 2006-01-25 14:14:25.717
191 A 2006-01-25 14:14:27.123
192 B 2006-01-25 14:14:27.280
Output:
seq cat create_date count
-- -- -- --
151 A 2006-01-25 14:14:20.827 2 -- contain 151, 161
171 A 2006-01-25 14:14:23.997 2 -- contain 171, 181
191 A 2006-01-25 14:14:27.123 1 -- contain 191
152 B 2006-01-25 14:14:20.983 2 -- contain 152, 162
172 B 2006-01-25 14:14:24.153 2 -- contain 172, 182
192 B 2006-01-25 14:14:27.280 1 -- contain 192
Any idea on how the SQL should write?
I just think of a SQL to get the intermediate grouping on which time
slot the record belongs to, but no idea on how to eliminate the
duplicated entry which is already included in another time slot....
The unfinished SQL:
SELECT m.cat, m.create_date AS 'gp_create_date', m.seq, n.seq,
n.create_date, n.random_string
FROM bbs m
LEFT OUTER JOIN bbs n ON n.cat = m.cat
AND n.create_date > m.create_date AND
datediff(ss, m.create_date, n.create_date) < 2
ORDER BY m.create_dateTry this
CREATE TABLE #Temp(seq int, cat CHAR(1), cdate datetime)
INSERT INTO #Temp
SELECT
151, 'A', '2006-01-25 14:14:20.827' UNION ALL
SELECT 152, 'B', '2006-01-25 14:14:20.983' UNION ALL
SELECT 161, 'A', '2006-01-25 14:14:22.390' UNION ALL
SELECT 162, 'B', '2006-01-25 14:14:22.543' UNION ALL
SELECT 171, 'A', '2006-01-25 14:14:23.997' UNION ALL
SELECT 172, 'B', '2006-01-25 14:14:24.153' UNION ALL
SELECT 181, 'A', '2006-01-25 14:14:25.560' UNION ALL
SELECT 182, 'B', '2006-01-25 14:14:25.717' UNION ALL
SELECT 191, 'A', '2006-01-25 14:14:27.123' UNION ALL
SELECT 192, 'B', '2006-01-25 14:14:27.280'
SELECT MIN(seq) as seq, cat, min(cdate) as mindate,max(cdate) as maxdate,
count(*) as cnt
FROM #Temp
GROUP BY cat, YEAR(cdate), Month(cdate), day(cdate),
DATEPART(hh,cdate),DATEPART(mi,cdate),
CASE WHEN DATEPART(s,cdate) %2 = 0
THEN DATEPART(s,cdate)
ELSE DATEPART(s,cdate) - 1
END
DROP tABLE #Temp
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"John Shum" <eurostar@.gmail.com> wrote in message
news:1138177022.986492.77030@.o13g2000cwo.googlegroups.com...
>I would like to prepare a SQL such that I can perform query on a table
> and count the number of record per time slot. The time slot is 2
> seconds each, but the beginning time of each time slot is not fix, it
> is determined by the create_date of the record, and this is the
> example:
> Raw Data:
> seq cat create_date
> -- -- --
> 151 A 2006-01-25 14:14:20.827
> 152 B 2006-01-25 14:14:20.983
> 161 A 2006-01-25 14:14:22.390
> 162 B 2006-01-25 14:14:22.543
> 171 A 2006-01-25 14:14:23.997
> 172 B 2006-01-25 14:14:24.153
> 181 A 2006-01-25 14:14:25.560
> 182 B 2006-01-25 14:14:25.717
> 191 A 2006-01-25 14:14:27.123
> 192 B 2006-01-25 14:14:27.280
> Output:
> seq cat create_date count
> -- -- -- --
> 151 A 2006-01-25 14:14:20.827 2 -- contain 151, 161
> 171 A 2006-01-25 14:14:23.997 2 -- contain 171, 181
> 191 A 2006-01-25 14:14:27.123 1 -- contain 191
> 152 B 2006-01-25 14:14:20.983 2 -- contain 152, 162
> 172 B 2006-01-25 14:14:24.153 2 -- contain 172, 182
> 192 B 2006-01-25 14:14:27.280 1 -- contain 192
> Any idea on how the SQL should write?
> I just think of a SQL to get the intermediate grouping on which time
> slot the record belongs to, but no idea on how to eliminate the
> duplicated entry which is already included in another time slot....
> The unfinished SQL:
> SELECT m.cat, m.create_date AS 'gp_create_date', m.seq, n.seq,
> n.create_date, n.random_string
> FROM bbs m
> LEFT OUTER JOIN bbs n ON n.cat = m.cat
> AND n.create_date > m.create_date AND
> datediff(ss, m.create_date, n.create_date) < 2
> ORDER BY m.create_date
>

GROUP BY/ HAVING CLAUSE problem

I'm trying to set up my adhoc query to return just one single record, which is aliased as 'foreign' in my sql statement (which is just the total amount of foreign overseas orders for just one day. All Sale_Type_Ids over 2 [integer datatype] are foreign orders):

SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) AS foreign
FROM Orders INNER JOIN
Processing ON Orders.ID = Processing.Order_ID
WHERE (Processing.Orderdate = '20050915') AND (Processing.status = 1)
GROUP BY CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END
HAVING (SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) >= 0)

..but my resultset is returning two records. If I remove the HAVING clause, it will return three records, with one being blank.
?
.netsports

In caculations COUNT (* ) is the only aggregate function in SQL Server that caculates NULL values, so your results will be different if you use COUNT (* ) if any of you columns allow NULLs. Try the link below for more about SQL Server NULLs. Hope this helps.
http://www.akadia.com/services/dealing_with_null_values.html|||i am using four table(ForumMain,ForumThreads,ReplyToThread,Authentication) in my forum.I have 4 asp.net pages in this forum. On the very first page, I am showing the Main category of forums.i.e all forums,last thread posted,total threads so far and the total number of replies to each thead and of course the name of the user who generated or added last thread.
To do this, i am using count function to count the total replies to each thread,RepliesToThread table is doing that(not counting total threads yet),Forum Category field from the ForumMain table,ThreadName from the ForumThreads table and the username from the Authentication table.
I am using Group By clause as well but every time a new thread is added from AddThread.aspx page, the name of the main category which the new thread is added into, is repeated on the main page.
i.e. if I add a new thread in main category DATABASE, and this main category has already one thread, the main page show me like
DATABASE already existing category.
date:25/09/2005
DATABASE new category
date:26/09/2005
rather than it should show me
DATABASE new category
date:26/09/2005
What should I do to avoid this repetition?
Thanks in advance.|||Try the link below and see if GROUP BY with CUBE or ROLLUP operator will help with you problem and some restrictions apply. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp|||Caddre, this linke you providehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp is not providing help to solve my problem.
I am looking forward to more helpful replies from you or anybody else.|||Hi,
You're summingSale_Type_Id values from Orders table. I thinks this is not what you want to get. You can Count theSale_Type_Id values to have the number of orders.

SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) AS [foreign]
And if your records in Orders table have Sale_Type_Id values greaterthan 2, for each distinct value of Sale_Type_Id you'll get a differentrow.
Because you group your records due to Sale_Type_Id's. Note that if itis 2 or less. You group them as nulls. And remove only the null groupby using the Having clause.
So you still have groups having Sale_Type_Id's greater than 2
I hope it is helpfull
Eralper
http://www.eralper.com

Group by, creating a headache

I am using SQL server 2000.
While using query analyzer I am facing problem.
If a query has group by clause and if that query is not fetching any record (i.e. query is returning nothing), then in this situation, I want zero to be displayed where datatype of field is integer and "-" if datatype of field is varchar.

Please give me solution as soon as possible, a kind request.

Facing problem for the below mentioned query:-

select IsD.ItemCode,
case
when sum(IsD.IssuedQty) is null then 0
else sum(IsD.IssuedQty)
end as IssuedToday
from Inv_IssueMaster IsM, Inv_IssueDetail IsD
where IsM.IssueNo=IsD.IssueNo
group by IsD.ItemCode

In the above mentioned query, datatype of IssuedQty is int and for ItemCode it's varchar.use the CASE operator for these columns where you need conditional values.

Hope this helps.|||Please give me solution as soon as possible, a kind request.please show your query|||I am using SQL server 2000.
While using query analyzer, I am facing problem.
If a query has group by clause and if that query is not fetching any record (i.e. query is returning nothing), then in this situation, I want zero to be displayed where datatype of field is integer and "-" if datatype of field is varchar.

Please give me solution as soon as possible.

Facing problem for the below mentioned query:-

select IsD.ItemCode,
case
when sum(IsD.IssuedQty) is null then 0
else sum(IsD.IssuedQty)
end as IssuedToday
from Inv_IssueMaster IsM, Inv_IssueDetail IsD
where IsM.IssueNo=IsD.IssueNo
group by IsD.ItemCode

In the above mentioned query, datatype of IssuedQty is int and for ItemCode it's varchar.|||I want zero to be displayed where datatype of field is integer and "-" if datatype of field is varchar.

I'm having trouble understanding your objective.

You have 2 columns from 2 tables, the 2nd is 0 to many relationship: first Varchar, 2nd Int
If the input is:

A 10, 20, 25
B null
C 15

You want results of

A 55
- 0
C 15

Is that correct? Why are you saying you want to check datatype

Note: Are you aware of the "ISNULL" function? It's a poor-man's DECODE (Oracle's powerhouse fuction).

I'm wondering if your question about datatype was just a confusion and this is all you want (I took the liberty of adding an outer join in case you have no detail records, you may still want to show a zero).

SELECT IsD.ItemCode,
sum(isnull(IsD.IssuedQty,0)) as IssuedToday
FROM Inv_IssueMaster IsM
LEFT OUTER JOIN Inv_IssueDetail IsD
ON IsM.IssueNo=IsD.IssueNo
GROUP BY IsD.ItemCode

This would return:
A 55
B 0
C 15

If you really want the "-" to be output in the first column, then this would work:

SELECT
CASE WHEN IssuedToday = 0 then '-'
ELSE SubQry.ItemCode end as ItemCode,
IssuedToday
FROM
(
SELECT IsD.ItemCode,
sum(isnull(IsD.IssuedQty,0)) as IssuedToday
FROM Inv_IssueMaster IsM
LEFT OUTER JOIN Inv_IssueDetail IsD
ON IsM.IssueNo=IsD.IssueNo
GROUP BY IsD.ItemCode
) SubQry

This will return:
A 55
- 0
C 15|||select IsD.ItemCode
, coalesce(sum(IsD.IssuedQty),0) as IssuedToday
from Inv_IssueMaster IsM
inner
join Inv_IssueDetail IsD
on IsD.IssueNo = IsM.IssueNo
group
by IsD.ItemCodethe only way that this query "is not fetching any record (i.e. query is returning nothing)" is when there are no rows in the Inv_IssueMaster table, which doesn't seem likely|||select IsD.ItemCode
, coalesce(sum(IsD.IssuedQty),0) as IssuedToday
from Inv_IssueMaster IsM
inner
join Inv_IssueDetail IsD
on IsD.IssueNo = IsM.IssueNo
group
by IsD.ItemCodethe only way that this query "is not fetching any record (i.e. query is returning nothing)" is when there are no rows in the Inv_IssueMaster table, which doesn't seem likely
Interesting use of COALESCE instead of ISNULL. Somehow I sense that I'm unaware of some subtlity. Partell.:shocked: (eagerly awaiting lesson)

As for no records in the Master - that would normally be true in a typical table structure, but it is possible to set up such a relationship. It would be a strange and badly normalized design, but SQL would allow it.

For example; let's say a school has the guy's SSN for student's that earn money from them and instead of linking the payment file into the student-ID, they have to link it to the SSN because the payment file doesn't have a "Student ID" column. They should create an intermediate XREF table, but they could also get lazy and just add the SSN to the STUDENT table. So this report would show "-" in the SSN column when there isn't one.

Somehow; given the OP's inconclusive wording of the requirement I'm wondering if that's really what s/he ment though. I think his problem is solved and s/he may or may not return to clarify.|||Interesting use of COALESCE instead of ISNULL. Somehow I sense that I'm unaware of some subtlity. Partell.:shocked: (eagerly awaiting lesson)interesting? how about standard sql ;)

coalesce is standard, isnull isn't

perhaps too subtle...|||interesting? how about standard sql ;)

coalesce is standard, isnull isn't

perhaps too subtle...
Thanks for the response.

ISNULL is just a trimmer version (ie: smaller Object) of COALESCE so is likely a little faster. However it's propietary so not portable.

So; sounds like a combination of habit, your need for portability, and maybe a general distain for propietary deviations from ANSI. Not some performance or reliability (within SQL Server) trick.

I'll have to rethink some of those things if I ever write something that has to be portable. For now, in a SQL Server only shop, I'll opt for using the trimmer version, considering it gets used so many times.

I see your point and it's a good one. I'm sure I'll continue using ISNULL, and when using Oracle I'll use DECODE, but I'll better appreciate why a Sr. SQL Consultant might do otherwise.

(Quite honestly; I had forgotten that ISNULL wasn't standard. The danger of being a single shop guy - need to get out more. Ergo my sudden appearence on this forum. :) )|||COALESCE blows the socks off ISNULL when there are more than two terms in the list ;)

likely faster? you cannot say that without extensive benchmarking, can you

and what's a "smaller Object" -- is that some kind of object-oriented thingy? in which context would you need to measure this object size?

i wasn't aware that the compiled execution plan would actually be bigger for one function in a query as compared to another|||COALESCE blows the socks off ISNULL when there are more than two terms in the list ;)

likely faster? you cannot say that without extensive benchmarking, can you

and what's a "smaller Object" -- is that some kind of object-oriented thingy? in which context would you need to measure this object size?

i wasn't aware that the compiled execution plan would actually be bigger for one function in a query as compared to another
Yes, object oriented thingy. General overhead thing. Use the minimum.

Extensive benchmarking - I'd rather just take an educated guess and say "probably".

Why would MS make it if not to optimize things a little? If they just did it for readability, and they actually made it slower, well ... that's just very unlikely. If I said "probably blow's it's socks off performance wise", now that would be an irresponsible statement. I'd stand by my original statmenet and say that common sense (and many years of programming experience) suffice for a "probably".

"more than two terms in the list", well, that would be a different capability, more in line what that extra code is ment to handle. I think "blow socks off" is a misnomer. It's more binary than that, like SELECT vs. SET, since ISNULL does not accept multiple terms.

Anyway; I didn't mean to start a shouting match. I acknowledge that you're probably far senior to myself in such matters and was ernestly looking for an insight. Your point is well taken and I believe it's a valid one.

Cheers :beer:|||Why would MS make it if not to optimize things a little? oh! oh! i know this one! to be compatible with sql standards, maybe?

but thanks for the followup, and rest assured, i wasn't shouting

:cool:|||oh! oh! i know this one! to be compatible with sql standards, maybe?

but thanks for the followup, and rest assured, i wasn't shouting

:cool:
See, now I'm curious about the overhead.

Indeed, why would MS deviate from ANSI standard on this?

My general project management experience tells me someone made a hit-list of "how to optimize, how to simplify". However; was it part of a marketing conspiracy to prevent SQL Server shops from migrating out? haha.

If the former, then the gain would have to be substantial to justify it - or at least very easy to implement with a modest gain.

Guess someone with an IN to the MS development team, or a very in depth book on SQL Server "improvements" would have to answer that one. Or like you say, develop a benchmark. Honestly; I'm not that curious.|||Another deviation from ANSI SQL is the GROUP BY ALL (see BOL). Works OK if all you need to deal with is SQL Server.|||"more than two terms in the list", well, that would be a different capability, more in line what that extra code is ment to handle. I think "blow socks off" is a misnomer.i should have been a bit more explicit

COALESCE is easier to write, to understand, and to maintain ( = "blows the socks off") than a series of nested ISNULLs, when what you need to do is select the first non-null value in a series of values|||Which also means, thaty you would have to use ISNULL many time to mimic the COALESCE function, so performance point goes out the window

Also, I stay as ANSI as possible to because I work on many different database platforms...it's tough enough changing gears as it is

Man I hate DB2 OS/390 a lot these days|||ooohhhh sigh.....

always stick to the ANSI-92 as close as possible. My hands are not clean in this matter. I have wrtten some proprietary junk.

screw having to port something to oracle or mysql or whatever. ask people what they think about moving to MS SQL 2005 that have a bunch of *= and =* in their code instead of nice and proper OUTER JOIN statements.|||I absolutely love COALESCE!!! My shop here (and pretty much all new SQL coders I have seen in the past 4 years) take to ISNULL as if it were a heated jockstrap on a backpacking trip to Eagle Lake (Sierras) in early March!!!

I just love COALESCE. If I wasn't married, I would marry COALESCE. I have cleaned up much stacked, squeezed, haywired, and Elmer's Glued ISNULL nests with a single COALESCE that, in it's own sublime beauty, brings tears to the eyes of the SQL youngsters.

Put simply, I feel strongly that COALESCE is cleverer than ISNULL. I think COALESCE could whip ISNULL's butt in a towel-snapping fight, and in most recognized games of chance available in the world today.

I had no idea that ISNULL is not ANSII standard though, so I am thrilled to have read this thread. It's really about time that COALESCE got the press it deserves.

If I have not mentioned it, it is, in fact, probably my favorite verb in MS SQL.|||Guess someone with an IN to the MS development team, or a very in depth book on SQL Server "improvements" would have to answer that one.

Friend of friend was a dev on the sql server team <wink>. Didn't code up the ISNULL feature though, so can't speak with authority on its origins :)

Indeed, why would MS deviate from ANSI standard on this?

I am pretty sure that ISNULL did not come about because of some MS conspiracy to try to get people to move away from existing ANSI standards.

More likely is that ISNULL was the pet feature of some long lost PM. Fair bet that he/she wasn't even aware of the existence of COALESCE when they came up with it. :)|||I don't know of any benchmarks showing one is faster than the other. The difference would be less than negligible anyway.

Why did MS create ISNULL()? I don't know that they did. It was probably a part of SYBASE before Microsoft got a hold of it. Also suspicious that ISNULL() in MSSQL operates completely differently than ISNULL() in MSAccess, making it even more unlikely that it was Microsoft's idea.

If you have a choice between two methods and one is ANSI while the other is not, use the ANSI standard.|||Love finding such passion. Tall dude, careful what you wish for (marriage wise), lol.

Oracle has "nvl", SQL Server "isnull".

I'm opting for using stuff as appropriate. COALESCE just seems more like a parser of a string of potentially null values than a simple replacment converter. Like a conditional branch statement that has a conversion method as a side benifit. Just my impression.

Using it with a single arguement just seemed odd to me, but then, I've only used Microsoft and Oracle platforms.

Nesting ISNULL does seem messy. Wattya wanna bet (ok, nothing over 50 cents) that COALESCE actually uses ISNULL internally. Would certianly make sense, programming wise. Why repeat all that coding?

Anyway, hat's off to the "ANSI-92 Only" camp. I sure can't keep up with it.

Shame when we get spoiled with some Propietary feature. Main one I sorely miss is the PL SQL support for a "Cursor Loop". All the cursor's fields get pseudo created with a scope only within the loop, no extra definition required, and more importantly all the definitions and naming inhereted at compile time and therefore self-maintaining if someone comes along and changes a column definition. Yeah; what a time and code saver! I'll deviate any day for that one. I'm just an ignorant "only solve what's in front of me" guy so I have no idea if that's in the ANSI standard or not - or if SQL Server 2005 has something similar.|||SQL Server supports cursors?

I'm not the best one to answer that one...as the Great Sage has been saying for incalcuable millennia, "Nature Abhors A Cursor" (or something to that effect, anyway).

I have managed to use only two in the past 4 years...and both of those to demonstrate why they should NOT be used ;)

yeah, yeah, yeah...sometimes they are necessary (or it's not worth it to do it correctly for a one-off cursor solution), but I like to pretend they don't exist. Similar to the methodology I use to address the existence of the devil himself.|||COALESCE just seems more like a parser of a string of potentially null values than a simple replacment converter.
Nesting ISNULL does seem messy. Wattya wanna bet (ok, nothing over 50 cents) that COALESCE actually uses ISNULL internally. Would certianly make sense, programming wise. Why repeat all that coding?

Anyway, hat's off to the "ANSI-92 Only" camp. I sure can't keep up with it.

I wouldn't muddy ISNULL() the function with the concept of something being null. COALESCE and ISNULL are funtionally identical at the conceptual level for evaluating the first argument. COALESCE is not a parser by any stretch of the imagination. COALESCE just has infinantly more flexibility with a very small perceived performance hit.

Assume you have:

ISNULL(myScalarVal, myScalarResult)
and
COALESCE(myScalarVal, myScalarResult)

logically speaking, both functions must perform the exact same scalar comparison for null against myScalarVal. The difference is COALESCE will now have to perform the same scalar comparison against myScalarResult to determine if it is also null. This could be viewed as a minor performance hit, although scalar comparisons are about as trivial of an action as I can imagine.

Now assume we have:

ISNULL(myScalarVal, ISNULL(mySecondScalar, ISNULL(myThirdScalar, myScalarResult)))
and
COALESCE(myScalarVal, mySecondScalar, myThirdScalar, myScalarResult)

Now we have to execute three seperate statements, returning the result of each nested statement to its parent statement. There is inherint overhead in having multiple levels of recursion waiting on a return value. COALESCE by comparison does this intrinsically, not much caring how many values it's been given. It simply plods along looking for the first non-null value and throws away anything that doesn't satisfy this criteria as opposed to passing the failed results back to a calling function.

So all in all, I don't think there's much of a difference between the two for testing and substituting a single null value. However there are great benefits in performance, readibility and flexibility in using COALESCE for multi-element comparisons, so why not use COALESCE out of hand all the time?|||SQL Server supports cursors?

...

I have managed to use only two in the past 4 years...and both of those to demonstrate why they should NOT be used ;)
They're sure slow hua? Here I'm arguing over trivia and start talking about using something about 10 times slower. sigh. And 10 times clunkier to program.

Usually it's a conversion program or integrity check - but in production ... there's always some kind of alternative. Sometimes however, what the heck's the difference between .1 second and 2 seconds for a once a month process.

... That said, why not use COALESCE out of hand all the time?
Well, I'm sold. Quite honestly (I admit :eek: ), the answer is "harder to type and spell".|||Well, I'm sold. Quite honestly (I admit :eek: ), the answer is "harder to type and spell".Ahhh...but there is an advantage to that as well. Noob developers looking over shoulder think it is some mystical database arcana. Abra-cadabrac-coalesce...it helps keep up the reputation.|||My Main problem is still unanswered.
Problem statement:
If a select query without group by clause, returns null under column headers, if select query doesnt fetch any record.
If a select query with group by clause, not returns null under column headers, if select query doesnt fetch any record.
I want select query should return zero for int datatype and - for varchar datatype, if select query doesnt fetch any record.|||sorry, umeshm_patil, i don't think that's possible|||Indeed, why would MS deviate from ANSI standard on this?I [b]like[b] the easy questions... When Sybase created the IsNull function, Coalesce was still about seven years from coming into existance as a proposed addition to the standard. Microsoft inherited the SQL Server product with IsNull already well established.

Actually, starting in SQL 7.0 the IsNull function is now implemented internally as a call to the same routine that serves Coalesce. There might be minute difference in the Transact-SQL parsing time (only because Coalesce has more letters than IsNull), but that difference isn't material to the function execution. If there are more than two values (implying nested calls to IsNull), then a single call to Coalesce will win hands down.

-PatP|||My Main problem is still unanswered.
Problem statement:
If a select query without group by clause, returns null under column headers, if select query doesnt fetch any record.
If a select query with group by clause, not returns null under column headers, if select query doesnt fetch any record.
I want select query should return zero for int datatype and - for varchar datatype, if select query doesnt fetch any record.What you seem to be requesting is a formatter for "non values" in your result set. These are by definition application specific, they are not truly issues for the server to manage for you. They really ought to be handled in your application code to avoid pushing application presentation issues into the realm of your SQL Server.

-PatP|||I don't know of any benchmarks showing one is faster than the other. I know of three! One I have bookmarked (good old Adam) with links to the other two:
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/30/5311.aspx
The difference would be less than negligible anyway.Bingo!

Sorry for another <ot> post umeshm_patil. FWIW - I agree with Pat. Well done Pat!|||excellent link, pootle flump|||Nice!

A 4th test, (http://jerrytech.blogspot.com/2006/05/sql-2k-performance-isnull-vs-coalesce.html) linked by someone in the comment section of pootle flump's link, showed very surprising results.

The time for a nested ISNULL was only MARGINALLY longer than a single ISNULL.

Example:
9280ms : Set @.x = IsNull(Jerry, Nixon)
9296ms : Set @.x = IsNull(Jerry, IsNull(Nixon, Value))

9500ms : Set @.x = Coalesce(Jerry, Nixon)
9563ms : Set @.x = Coalesce(Jerry, Nixon, Value)

This test showed ISNULL to be slightly faster.

Hardly an arguement for using ISNULL given it's propietary nature, but it is an arguement for not rolling one's eyes quite so loudly when some programmer nests ISNULLs.|||btw, while we are on the topic of coalesce, C# has a coalesce operator now. I just learned about it following some of the perf testing links pootle_flump provided: http://weblogs.sqlteam.com/mladenp/archive/2006/03/27/9425.aspx

with it you can do this:

string foo = bar ?? "default value";

very neat! I love the operator because when I first saw it I thought "what??"|||<cough>

drop table testproducts
create table testproducts
(col1 int,
col2 varchar(10))

insert into testproducts
select 1, 'red'
union
select 2, 'yellow'
union
select 3, 'blue'

create table testorders
(col1 int,
col2 int)

insert into testorders
select 1, 10
union
select 2, 30
union
select 1, 50
union
select 2, 1
union
select 1, 5
-- Hey! No blue orders??

select sum(o.col2), count(*), p.col2
from testproducts p left outer join
testorders o on p.col1 = o.col1
group by all p.col2

Yeah, yeah. Group by all is non-ANSI, but it gets the OP through the day.|||<cough>something wrong with your throat?

guess what results you get for this query --select sum(o.col2), count(*), p.col2
from testproducts p left outer join
testorders o on p.col1 = o.col1
group by p.col2
so your point was... ?|||Mistaken, apparently.....|||Not to stray off topic, but anyone ever run COALESCE with NULL as the final value?

Interesting...|||Not to stray off topic, but anyone ever run COALESCE with NULL as the final value? i haven't

i don't think it's ever necessary, is it ;)|||i haven't

i don't think it's ever necessary, is it ;)
Of course not. It made for an interesting academic exercise in poking around behind how coalesce works though...|||Not to stray off topicI think off topic is the new on topic.
Of course not. It made for an interesting academic exercise in poking around behind how coalesce works though...Presumably this is where you got a lot of your conclusions from ealier? Do you have any code to hand or do we have to jig up our own? :)|||My earlier conclusions were nothing more than logical conjecture as a developer. It wasn't until all those contradictory perf studies were posted that I really wanted to start monkeying with this stuff.

Anyways, try these:

SELECT CASE WHEN 1=1 THEN NULL END

SELECT CASE WHEN 1=1 THEN NULLIF(1,1) END

SELECT COALESCE (NULL, NULL)

SELECT ISNULL(NULL, NULL)

This seems to suggest that ISNULL is using a different method of comparison then COALESCE. One that could very well be faster.|||Yeah, but this works...

DECLARE @.x int

SELECT COALESCE (NULL, @.x)

So perhaps it's just syntax and/or the use of the keyword NULL?|||The query engine has no way of knowing the runtime value of @.x when compiling the query plan. That's the same reason COALESCE(NULL, NULLIF(1,1)) will function properly; NULLIF() isn't evaluated until runtime. You can do the same trick using Case.

The conclusion I'm driving at is COALESCE() is nothing more than a wrapper for CASE whereas ISNULL() is doing something entirely different. This may explain why what would appear to be the logically superior structure for evaluating multiple elements isn't always the fastest. Now the question becomes:

Which is faster?

ISNULL(myScalar1, ISNULL(myScalar2, result))
or
CASE
WHEN myScalar1 <> NULL THEN myScalar1
WHEN myScalar2 <> NULL THEN myScalar2
ELSE result
END

Edit: A bit more co-noodling with another member here brought up an interesting opinion. COALESCE, being newer than ISNULL simply implements more stringent front-end validation as it is an ANSI standard. Now I'm all messed up... :(|||here's an example that came up today on another forum

scenario: there are two tables, persons and contacts, a person can have multiple contacts, each contact is a separate row with a contact type column, and the query is supposed to return each person with at most one contact -- return the person's email contact, and if email contact doesn't exist, return the work number, and if work number doesn't exist, return the home number

select p.name
, coalesce(e.contact
,w.contact
,h.contact) as contact
from persons as p
left outer
join contact as e
on e.personid = p.id
and e.contacttype = 'E' /* email */
left outer
join contact as w
on w.personid = p.id
and w.contacttype = 'W' /* work # */
left outer
join contact as h
on h.personid = p.id
and h.contacttype = 'H' /* home # */

Group by with criteria

I have a Problem with my Select statement!
I want to Select every Record, where ChNr fits the pattern the User
chooses. And sum up the different Source Columns.
So far this one works out fine, but I want to have the sum over ALL ChNr
that fit the pattern given, not a result for every ChNr.
If that is of any interest for you, ChNr always looks the same: three
numbers-three numbers e.g. 481-102, 581-235.
SELECT DISTINCT a.ChNr, Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL 25?m]) AS
BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
[BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
(1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
FROM tbAuswert a
WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
GROUP BY ChNr;
With this Statement I get one Result for every single ChNr. What I want is
ONE result for all ChNr that look like 491-... .
I thought I would have to make a criteria in the Group by part, but I just
can't make it work! I already tried to put another LIKE thing into Group
by, but it just won't do.
Unfortunately the DataBase I am working with is Access.
I am working with Visual Studio and C#.
I hope I made myself clear. It is really not so easy to explain my problem.
Thanks Julia
Message posted via http://www.webservertalk.comIf I understand you correctly, this should do it:
SELECT LEFT(a.ChNr,3) as [NAME], Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL
25?m]) AS
BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
[BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
(1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
FROM tbAuswert a
WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
GROUP BY LEFT(ChNr);
-oj
"Julia H?rtfelder via webservertalk.com" <forum@.webservertalk.com> wrote in
message news:2050d4f1fa8c4293b71ff00dc45057d1@.SQ
webservertalk.com...
>I have a Problem with my Select statement!
> I want to Select every Record, where ChNr fits the pattern the User
> chooses. And sum up the different Source Columns.
> So far this one works out fine, but I want to have the sum over ALL ChNr
> that fit the pattern given, not a result for every ChNr.
> If that is of any interest for you, ChNr always looks the same: three
> numbers-three numbers e.g. 481-102, 581-235.
> SELECT DISTINCT a.ChNr, Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL 25?m]) AS
> BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
> [BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
> (1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
> FROM tbAuswert a
> WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
> GROUP BY ChNr;
>
> With this Statement I get one Result for every single ChNr. What I want is
> ONE result for all ChNr that look like 491-... .
> I thought I would have to make a criteria in the Group by part, but I just
> can't make it work! I already tried to put another LIKE thing into Group
> by, but it just won't do.
> Unfortunately the DataBase I am working with is Access.
> I am working with Visual Studio and C#.
> I hope I made myself clear. It is really not so easy to explain my
> problem.
> Thanks Julia
> --
> Message posted via http://www.webservertalk.com|||The Idea is fine! This is exactly what I need!
I tried it and first I got the message of a missing parameter, so I looked
up left() and now my Statement looks like this:
SELECT DISTINCT a.ChNr, Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL 25?m]) AS
BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
[BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
(1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
FROM tbAuswert a
WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
GROUP BY LEFT (a.Auswert, 4);
But now I get the following Message:
"You tried to execute a query that does not include the specified
expression 'ChNr' as part of an aggregate function"
So, what could might be the Problem now?
Message posted via http://www.webservertalk.com|||You cannot be grouping by left(col,4) and not including it as part of your
select.
So, change your SELECT DISTINCT a.ChNr to SELECT left(a.ChNr,4).
DISTINCT is redundant here when you do grouping.
-oj
"Julia H?rtfelder via webservertalk.com" <forum@.webservertalk.com> wrote in
message news:b7da74bb92c645fb868096d31d45aa70@.SQ
webservertalk.com...
> The Idea is fine! This is exactly what I need!
> I tried it and first I got the message of a missing parameter, so I looked
> up left() and now my Statement looks like this:
> SELECT DISTINCT a.ChNr, Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL 25?m]) AS
> BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
> [BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
> (1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
> FROM tbAuswert a
> WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
> GROUP BY LEFT (a.Auswert, 4);
> But now I get the following Message:
> "You tried to execute a query that does not include the specified
> expression 'ChNr' as part of an aggregate function"
> So, what could might be the Problem now?
> --
> Message posted via http://www.webservertalk.com|||How blind can one woman be'
Thank you so much!!! It Works!
You are a genius! ;-)
Just another small question concerning the DISTINCT.
It might come up, that I have a record double except from the Key (which
are consecutive numbers ), doesen't DISTINCT filter the double out before
summing up?
Message posted via http://www.webservertalk.com|||;-) you're welcome.
See if this example helps:
create table #tmp(i sysname, j int)
insert #tmp select '123-456',1
insert #tmp select '123-456',1
insert #tmp select '123-456',2
insert #tmp select '123-456',2
insert #tmp select '123-456',2
insert #tmp select '456-456',2
insert #tmp select '456-456',3
insert #tmp select '789-456',5
insert #tmp select '789-456',5
insert #tmp select '789-456',1
go
--only distinct j
select left(i,4) i, sum(distinct j) s
from #tmp
where i like '123-%' or i like '789-%'
group by left(i,4)
--regular
select left(i,4) i, sum(j) s
from #tmp
where i like '123-%' or i like '789-%'
group by left(i,4)
--redundant distinct
select distinct left(i,4) i, sum(j) s
from #tmp
where i like '123-%' or i like '789-%'
group by left(i,4)
go
drop table #tmp
go
-oj
"Julia H?rtfelder via webservertalk.com" <forum@.webservertalk.com> wrote in
message news:e2ed74fec66e45c7b222a42b4f653e82@.SQ
webservertalk.com...
> How blind can one woman be'
> Thank you so much!!! It Works!
> You are a genius! ;-)
> Just another small question concerning the DISTINCT.
> It might come up, that I have a record double except from the Key (which
> are consecutive numbers ), doesen't DISTINCT filter the double out before
> summing up?
> --
> Message posted via http://www.webservertalk.com

Group By Date?

How would I group records by date from a table where each record has a
smalldatetime field called CreationDateTime that is auto-populated using
GetDate().
I need to produce a trends graph and want display the number of records
created per day. Obviously, my CreationDateTime field stored both both date
and time.
Thanks
BenThis isn't going to be very snappy, but...
SELECT
dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreationDateTime)),
COUNT(*)
FROM
yourTable
WHERE
CreationDateTime >= ?
AND CreationDateTime < ?
GROUP BY
DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreationDateTime))
ORDER BY
1
Note that if there are days in your date range with no data, they will not
show up in the result set. If you want to have a row for every day, even
when there are no relevant rows, use a calendar table (see
http://www.aspfaq.com/2519 for some examples).
You may want to consider adding a computed or static column that holds the
date only, if you are going to use a lot of queries like this. If you do
that, you will want to experiment with your clustered index, and whether it
resides on the column with both date and time, or on the column with just
the date. Your best scenario depends on whether you are querying by range
or just analyzing the entire table, and what else this table is being used
for...
A
"Ben Fidge" <ben.fidge@.nospambtopenworld.com> wrote in message
news:%23Yd2cbMBGHA.740@.TK2MSFTNGP12.phx.gbl...
> How would I group records by date from a table where each record has a
> smalldatetime field called CreationDateTime that is auto-populated using
> GetDate().
> I need to produce a trends graph and want display the number of records
> created per day. Obviously, my CreationDateTime field stored both both
> date and time.
> Thanks
> Ben
>|||Ben Fidge wrote:

> How would I group records by date from a table where each record has a
> smalldatetime field called CreationDateTime that is auto-populated using
> GetDate().
> I need to produce a trends graph and want display the number of records
> created per day. Obviously, my CreationDateTime field stored both both dat
e
> and time.
> Thanks
> Ben
SELECT MIN(creationdatetime) AS dt, COUNT(*) AS cnt
FROM your_table
GROUP BY DATEDIFF(DAY,'20000101',creationdatetime
) ;
David Portas
SQL Server MVP
--|||Exceelnt, works a treat. Thanks.
Ben
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u0D$9fMBGHA.1032@.TK2MSFTNGP11.phx.gbl...
> This isn't going to be very snappy, but...
>
> SELECT
> dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreationDateTime)),
> COUNT(*)
> FROM
> yourTable
> WHERE
> CreationDateTime >= ?
> AND CreationDateTime < ?
> GROUP BY
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreationDateTime))
> ORDER BY
> 1
>
> Note that if there are days in your date range with no data, they will not
> show up in the result set. If you want to have a row for every day, even
> when there are no relevant rows, use a calendar table (see
> http://www.aspfaq.com/2519 for some examples).
> You may want to consider adding a computed or static column that holds the
> date only, if you are going to use a lot of queries like this. If you do
> that, you will want to experiment with your clustered index, and whether
> it resides on the column with both date and time, or on the column with
> just the date. Your best scenario depends on whether you are querying by
> range or just analyzing the entire table, and what else this table is
> being used for...
> A
>
> "Ben Fidge" <ben.fidge@.nospambtopenworld.com> wrote in message
> news:%23Yd2cbMBGHA.740@.TK2MSFTNGP12.phx.gbl...
>

GROUP BY / HAVING clauses problem

I'm trying to set up my adhoc query to return just one single record,
which is aliased as 'foreign' in my sql statement (which is just the
total amount of foreign overseas orders for just one day. All
Sale_Type_Ids over 2 [integer datatype] are foreign orders):
SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
Orders.Sale_Type_Id ELSE NULL END) AS foreign
FROM Orders INNER JOIN
Processing ON Orders.ID = Processing.Order_ID
WHERE (Processing.Orderdate = '20050915') AND (Processing.status =
1)
GROUP BY CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id
ELSE NULL END
HAVING (SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
Orders.Sale_Type_Id ELSE NULL END) >= 0)
..but my resultset is returning two records. If I remove the HAVING
clause, it will return three records, with one being blank.
?
.netsportsIf I understand correctly, your GROUP BY is on:
All Orders.Sale_Type_ID greater than 2
NULL
So, without the HAVING I would expect one row returned for each Sale_Type_ID
> 2 and one for all the rest which become NULL. Do you have two ID>2 within
the rows covered by your WHERE clause.
The HAVING apparently is able to prune out the NULL value.
Perhaps all you wanted was:
SELECT SUM(Orders.Sale_Type_Id) AS foreign
FROM Orders INNER JOIN
Processing ON Orders.ID = Processing.Order_ID
WHERE (Processing.Orderdate = '20050915') AND (Processing.status =1) AND
Orders.Sale_Type_ID > 2
RLF
".Net Sports" <ballz2wall@.cox.net> wrote in message
news:1127848228.326875.56120@.g49g2000cwa.googlegroups.com...
> I'm trying to set up my adhoc query to return just one single record,
> which is aliased as 'foreign' in my sql statement (which is just the
> total amount of foreign overseas orders for just one day. All
> Sale_Type_Ids over 2 [integer datatype] are foreign orders):
> SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
> Orders.Sale_Type_Id ELSE NULL END) AS foreign
> FROM Orders INNER JOIN
> Processing ON Orders.ID = Processing.Order_ID
> WHERE (Processing.Orderdate = '20050915') AND (Processing.status =
> 1)
> GROUP BY CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id
> ELSE NULL END
> HAVING (SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
> Orders.Sale_Type_Id ELSE NULL END) >= 0)
> ..but my resultset is returning two records. If I remove the HAVING
> clause, it will return three records, with one being blank.
> ?
> .netsports
>|||If you only want a single row then remove the GROUP BY clause. GROUP BY
returns one row per group.
David Portas
SQL Server MVP
--|||Thanks. Looks like i'm getting the desired resultset. This sql
statement was sort of a permutation of an extensive one that would
bring back multiple records, but yes, I was starting to think if the
Group By was really necessary.|||I'm inferring your schema to be something like this:
CREATE TABLE Orders
(
ID INT PRIMARY KEY ?
, Sale_Type_Id INT
, Order_Total MONEY ?maybe
)
CREATE TABLE Processing
(
OrderID INT REFERENCES Orders(OrderID)
, Orderdate DATETIME
, Status INT
)
Your question sounds like you're either trying to find a count of
orders, or total amount purchased (which is why I made up that
ordertotal field). If either of those are what you're looking for,
there are much syntactically simpler solutions.
e.g.
SELECT COUNT(*) CountOfForeignOrders
, SUM(Order_Total) TotalOfForeignOrders
FROM Orders
WHERE Sale_Type_Id > 2 AND EXISTS(SELECT * FROM Processing WHERE
OrderID = Orders.ID AND OrderDate = '20050915' AND Status = 1)
When performing aggregates with join clauses, it's possible to
aggregate the same value more than once if a join causes the same row
to appear multiple times in the resultset. GROUP BY and HAVING aren't
necessary unless you're selecting your data based on your aggregate,
and if you'd return multiple aggregate sets.
-Alan

Gridview / SqlDataSource error - Procedure or function <stored procedure name> has t

Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.

Post your Gridview and SQL Proceedure code.|||

SqlDataSource:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:TPCConnectionString %>"SelectCommand="SELECT ID, AccountNumber, CompanyName, ExceptionDescription, PricingAdjustments FROM ExceptionList ORDER BY CompanyName"DeleteCommand="DELETE FROM ExceptionList WHERE (ID = @.ID)"ProviderName="<%$ ConnectionStrings:TPCConnectionString.ProviderName %>"UpdateCommand="updExceptionList"UpdateCommandType="StoredProcedure"><DeleteParameters>

<asp:ParameterName="ID"/>

</DeleteParameters><UpdateParameters><asp:ControlParameterControlID="GridView2"Name="AccountNumber"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="CompanyName"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="ExceptionDescription"PropertyName="SelectedValue"/></UpdateParameters></asp:SqlDataSource>

Stored Procedure:

CREATE PROCEDURE updExceptionList @.ID numeric(5), @.AccountNumber nvarchar(255),@.CompanyName nvarchar(255), @.ExceptionDescription nvarchar(255) AS

UPDATE ExceptionList SET AccountNumber = @.AccountNumber, CompanyName = @.CompanyName, ExceptionDescription = @.ExceptionDescription WHERE ID = @.ID
GO

But also fails if I specify ID parameter in SqlDataSpurce--here is the error:

Procedure or function updExceptionList has too many arguments specified.

|||

SqlDataSource:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:xxxConnectionString %>"SelectCommand="SELECT ID, AccountNumber, CompanyName, ExceptionDescription, PricingAdjustments FROM ExceptionList ORDER BY CompanyName"DeleteCommand="DELETE FROM ExceptionList WHERE (ID = @.ID)"ProviderName="<%$ ConnectionStrings:xxxConnectionString.ProviderName %>"UpdateCommand="updExceptionList"UpdateCommandType="StoredProcedure"><DeleteParameters>

<asp:ParameterName="ID"/>

</DeleteParameters><UpdateParameters><asp:ControlParameterControlID="GridView2"Name="AccountNumber"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="CompanyName"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="ExceptionDescription"PropertyName="SelectedValue"/></UpdateParameters></asp:SqlDataSource>

Stored Procedure:

CREATE PROCEDURE updExceptionList @.ID numeric(5), @.AccountNumber nvarchar(255),@.CompanyName nvarchar(255), @.ExceptionDescription nvarchar(255) AS

UPDATE ExceptionList SET AccountNumber = @.AccountNumber, CompanyName = @.CompanyName, ExceptionDescription = @.ExceptionDescription WHERE ID = @.ID
GO

But also fails if I specify ID parameter in SqlDataSpurce--here is the error:

Procedure or function updExceptionList has too many arguments specified.

|||

Change your UpadteParametrs their Control Id's are wronge.

Are u using some DropDownlists inside a Gridview?

|||

In what way are they wrong? They are for control Gridview2.

I actually solved this problem by entering the entirety of the stored procedure in the SqlDataSource configuration, which is the most unideal solution I could make work. I do not want any sql at all in my application but it seems that I'm forced to put it there.

|||

I got the same error.

As it turned out, the ConflictDetection on my datasource was set to "CompareAllValues" which forces the datasource to supplies all the columns to my stored procdure. Hence, the error because the stored procedure only take one parameter.

My fix, was just change the ConflictDetection to "OverwriteChanges". Then it worked.

NOTE: I did NOT have to write any code to add new parameter or set the parameter's value for the delete command at all.

Regards,

Minh

|||

correction on the "NOTE".

I did have to add parameter and value for the stored procdure in the RowDeleting event.

But make sure you don't add the parameters in your designer.

protectedvoid GridView1_RowDeleting(object sender,GridViewDeleteEventArgs e)

{

foreach (DictionaryEntry entryin e.Keys)

{

this.SqlDataSource1.DeleteParameters.Add(entry.Key.ToString(), entry.Value.ToString());

}

}

|||

Minh,

Thank you for looking at the issue. I revisited this page and found that the ConflictDetection parameter was set to "OverwriteChanges" so that doesn't seem to be the issue. I am finding many other gridview / parameter problems, although I've had some better success since this post. I would like to understand why you had to delete all the parameters in code, does the designer not function properly? I'm not really interested in writing code for my next update which has like 20 parameters.

|||

Hi sestyd,

I've had a similar problem before, where the update command is sending more parameters than I have defined in the Sqldatasource. (Assuming you have this connected to a grid view), what it seems to be doing is sending any parameter that is defined in the grid view that is not specified as read only (as well as the data keys).

I pretty much just either made the parameters read only in the grid view (if that was viable) or defined the parameters in the stored procedure, then just ignored them.

BTW here is some code that I wrote that will display all of the parameters and their values in a label on the web page for an update function and stop the function from executing, this helped me work out what was going on.

[VB Code]

Protected Sub MyDataSource_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles MyDataSource.Updating lblTest.Text =""For iAs Integer = 0To e.Command.Parameters.Count - 1Step 1 lblTest.Text &= e.Command.Parameters.Item(i).ParameterName.ToString &" :: " & e.Command.Parameters.Item(i).Value &"<br>"Next e.Cancel =TrueEnd Sub

[/VB Code]

HTH