Query (count) question

I am somewhat new to SQL and I have a simple question (I think)... I have a field called results that contains several numbers seperated by columns, such as 3,6,16,22 etc.

I want to write a query that answers how many of each number occurs from the range.

Example:

Select (total) results where id = 6 and results = 16

so the query would have to search within the string results for all the records retrieved and count the instances

how would I do this?

I'd do the following. First you'll need a numbers table as in

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

now create a view on your table that splits out the numbers

create view SplitResults
as
select id,
cast(substring(results,
Number,
charindex(',',
results + ',',
Number) - Number) as int) as results
from MyTable
inner join Numbers on Number between 1 and len(results) + 1
and substring(',' + results, Number, 1) = ','
GO

Finally, run a query on the view to get your results

select count(*)
from SplitResults
where id=6 and results=16

|||Thanks, I new it was simple

Query

Hi,

I have a table called Strategic Programmes which has 2 columns "ProgNo" (unique ID) and "ProgName"

I need to connect to SQL Server from Access using connection and recordset objects to find the next highest "ProgNo"

What is the SQL query I need to execute to find the next highest ID number and how do I extract this from my recordset?

Thanks,

Mark

Track:

This query will work both for SQL Server 2000 and SQL Server 2005:

select max (progNo)
from ( select top 2
progNo
from [Strategic Programmes]
order by progNo
) a

Note that it will perform better if the table is either keyed or indexed on the progNo column.

|||

If you are looking for "second" highest, above solutions works perfect.

But if you are looking for "next" highest, following is my way:

select
nextHighestProgNo = max(ProgNo)
from
YourTable
where
ProgNo < CurrentProgNo

If you are currennt ProgNo is 15 and if you execute above query by replaceing CurrentProgNo with 15, you will get the next highest ProgNo. (will be 14 if there is one)

sql

Query

Hey

I have a table where i store answers for a questionare

i have the columns id(key), userId, QuestionId, answerID

QuestionID and answerID are related to different tables

one of the users didnt answer Question 6 and the rest of the users did. there is no empty entry for Question 6 so the database skips from Question 5 to Question 7

how do i write a query to find out the userID of the person that didnt answer Question 6

select userId from <questionaretable> where userid not in (select userid from <questionaretable> where questionid = 6)|||

Thx champion

Query

Hi

I am trying to execute the below query in Query analyzer, the same query is executed in Sybase with the corresponding columns.

select (a.OnlineAccessId) + CASE
WHEN (a.onlineaccessid2) <> '00000000000000' THEN (a.onlineaccessid2)
else ''
End + CASE
WHEN (a.onlineaccessid3) <> '00000000000000' THEN (a.onlineaccessid3)
else ''
End as onlineaccessids, a.id as AcctInfoId, BankNo, RegionNo, OfficeNo, AcctNo,AcctShortNm ,SecLending, q.id as UsersId
from AcctInfo AS a INNER JOIN "104030" AS p ON a.AdminOfficer = p.CrmCode
inner join Users as q ON p.CrmDesc = q.OnlineaccessId union select
OnlineAccessIds = (a.OnlineAccessId) + CASE
WHEN (a.onlineaccessid2) <> '00000000000000' THEN (a.onlineaccessid2)
else ''
End + CASE
WHEN (a.onlineaccessid3) <> '00000000000000' THEN (a.onlineaccessid3)
else ''
End,q.id, a.Id, BankNo, RegionNo,
OfficeNo, AcctNo,AcctShortNm, SecLending
from AcctInfo as a
inner join users as q on
(q.onlineaccessid = substring(onlineaccessids, 1, 3))

The error message is

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'onlineaccessids'..

It is able to recognize the field "onlineaccessids" and giving me the results in Sybase database and giving the error in SQL Server.
Try to help me out.

Thanx in advanceFirst, double check that both the AcctInfo and users tables have an attribute "onlineaccessids".

Secondly, add an alias to:

(q.onlineaccessid = substring(a/q.onlineaccessids, 1, 3))

Query

i have written a SP to select multiple columns from a table.now how do i
display the same in the corresponding text boxes that i have in my form...If your text boxes are called text1 and text2, then you would say:
text1.Text = RecordSetVar("Column1")
text2.Text = RecordSetVar("Column2")
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Smitha" <Smitha@.discussions.microsoft.com> wrote in message
news:82507647-4C68-45DB-9E84-457938670013@.microsoft.com...
>i have written a SP to select multiple columns from a table.now how do i
> display the same in the corresponding text boxes that i have in my form...

querry: rows must be displayed as columns

hi all,

currently i have a table:
studentId course score year
6.1 math 9 2007
6.1 french 5 2007
6.1 science 8 2007
6.1 math 6 2006
6.1 french 5 2006
6.1 science 5 2006
6.2 math 4 2007
6.2 french 6 2007

is it possible to have a query that gives this as result:
student year mathscore frenchscore sciencescore
6.1 2007 9 5 8
6.1 2006 6 5 5
6.2 2007 4 6 NULL

use PIVOT operator (if you SQL Server 2005).

Code Snippet

Create Table #score (

[studentId] Varchar(100) ,

[course] Varchar(100) ,

[score] int ,

[year] int

);

Insert Into #score Values('6.1','math','9','2007');

Insert Into #score Values('6.1','french','5','2007');

Insert Into #score Values('6.1','science','8','2007');

Insert Into #score Values('6.1','math','6','2006');

Insert Into #score Values('6.1','french','5','2006');

Insert Into #score Values('6.1','science','5','2006');

Insert Into #score Values('6.2','math','4','2007');

Insert Into #score Values('6.2','french','6','2007');

Select

[studentId]

, [year]

, [math]

, [french]

, [science]

From

#score S

Pivot(

Max([score]) For

[course] in ([math], [french], [science])

) as Pvt

|||

You could ue PIVOT function:

Code Snippet

create table #Scores

(

studentid int,

course varchar(10),

score int,

year int

)

insert into #Scores values(6.1,'math', 9, 2007)

insert into #Scores values(6.1,'french', 5, 2007)

insert into #Scores values(6.1,'science', 8, 2007)

insert into #Scores values(6.1,'math', 6, 2006)

insert into #Scores values(6.1,'french', 5, 2006)

insert into #Scores values(6.1,'science', 5, 2006)

insert into #Scores values(6.2,'math', 4, 2007)

insert into #Scores values(6.2,'french', 6, 2007)

select studentid, year, [math],[french], [science]

FROM #Scores PIVOT( MAX(SCORE) FOR course IN ([math],[french], [science]) ) as pvt

|||hi try this..

DECLARE @.Subjects TABLE (
studentId numeric(10,1)
, course varchar(20)
, score numeric
, [year] int
)

INSERT
INTO @.Subjects
SELECT 6.1, 'math', 9, 2007 UNION ALL
SELECT 6.1, 'french', 5, 2007 UNION ALL
SELECT 6.1, 'science', 8, 2007 UNION ALL
SELECT 6.1, 'math', 6, 2006 UNION ALL
SELECT 6.1, 'french', 5, 2006 UNION ALL
SELECT 6.1, 'science', 5, 2006 UNION ALL
SELECT 6.2, 'math', 4, 2007 UNION ALL
SELECT 6.2, 'french', 6, 2007

SELECT DISTINCT
s.studentId
, s.[year]
, m.score as mathscore
, f.score as frenchscore
, c.score as sciencescore
FROM @.Subjects s LEFT OUTER JOIN
(
SELECT *
FROM @.Subjects
WHERE course = 'math'
) m ON s.studentId = m.studentId
AND s.[year] = m.[year] LEFT OUTER JOIN
(
SELECT *
FROM @.Subjects
WHERE course = 'french'
) f ON s.studentId = f.studentId
AND s.[year] = f.[year] LEFT OUTER JOIN
(
SELECT *
FROM @.Subjects
WHERE course = 'science'
) c ON s.studentId = c.studentId
AND s.[year] = c.[year]|||

If you use SQL Server 2000 (This query will work with 2005 also)..

Code Snippet

Create Table #score (

[studentId] Varchar(100) ,

[course] Varchar(100) ,

[score] int ,

[year] int

);

Insert Into #score Values('6.1','math','9','2007');

Insert Into #score Values('6.1','french','5','2007');

Insert Into #score Values('6.1','science','8','2007');

Insert Into #score Values('6.1','math','6','2006');

Insert Into #score Values('6.1','french','5','2006');

Insert Into #score Values('6.1','science','5','2006');

Insert Into #score Values('6.2','math','4','2007');

Insert Into #score Values('6.2','french','6','2007');

Select

studentId

, year

, Sum(Case When Course = 'math' Then score End) as [math]

, Sum(Case When Course = 'french' Then score End) as [french]

, Sum(Case When Course = 'science' Then score End) as [science]

From

#score [main]

Group By

studentId,year

|||nice post mani,|||hi,
in SQL Server 2000 you can try this query :

Code Snippet

select studentID, year
, (select score from #tbl where course = 'math' and studentID = x.studentid and year = x.year) as matchscore
, (select score from #tbl where course = 'science' and studentID = x.studentid and year = x.year) as sciencescore
, (select score from #tbl where course = 'french' and studentID = x.studentid and year = x.year) as frenchscore
from #tbl x

- clintz

q; duplicate dhcek on two columns

Hello,
I have a table T1 and on this table I have an insert trigger. In the trigger
I need to check if T1.ID and T1.Type=’xyz’ together are duplicated or not
(duplicate dhcek on two columns), if yes return error from trigger. There
might be T1.ID and T1.Type=’abc’ duplicated, that is fine.
I guess I'm a bit confused about what you are attempting to accomplish.
It seems that you allow some duplicate values ('abc') between the two
columns-but not others. (If it was a simple as no duplicate values, then a
PK would work.)
Is it that a new row cannot have both t1.ID AND t1.Type = 'xyz',
OR
A new row can have a t1.ID or t1.Type = 'xyz' IF another row has either but
not both,
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values of either t1.ID or t1.Type = 'xyz',
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values for t1.ID AND t1.Type = 'xyz',
OR ...
Is it only the value 'xyz' that is of concern?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A17263B7-72E0-46D7-8431-FECFC522C810@.microsoft.com...
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the
> trigger
> I need to check if T1.ID and T1.Type='xyz' together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type='abc' duplicated, that is fine.
>
|||Hi
Why not have a unique index on these two columns?
Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
Try the following code in your trigger:
IF EXISTS (SELECT *
FROM inserted i
JOIN T1 T ON i.id = T1.id AND i.type = T1.type
)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Duplicate Values Entered.', 16, 1)
RETURN
END
John
"JIM.H." wrote:

> Hello,
> I have a table T1 and on this table I have an insert trigger. In the trigger
> I need to check if T1.ID and T1.Type=’xyz’ together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type=’abc’ duplicated, that is fine.
>
|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
|||Hi Arnie
That is not the way I interpreted the post, but the post is not well
defined. Posting DDL, sample data and example statements that would work and
would not work would have been better!
John
"Arnie Rowland" wrote:

> Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
> probably won't work for him.
> "check if T1.ID and T1.Type='xyz' together are duplicated"
> AND
> "There might be T1.ID and T1.Type='abc' duplicated, that is fine."
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
>
>
|||Thanks Hugo, unique constraints would not work since a specific value should
not be repeated not all. I would go with your second suggestion.
"Hugo Kornelis" wrote:

> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
>
|||Hi
Without examples it is still ambiguous what you are trying to do. A compound
unique constraint on the two columns would have the same effect as the sample
code.
John
"JIM.H." wrote:
[vbcol=seagreen]
> Thanks Hugo, unique constraints would not work since a specific value should
> not be repeated not all. I would go with your second suggestion.
> "Hugo Kornelis" wrote:
|||Hi Hugo
Jim seems to start a new thread each time he hits a problem with this trigger!
John
"Hugo Kornelis" wrote:

> On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:
>
> Hi Arnie,
> You're right - I missed that part of Jim's message.
> If this is indeed the requirement (Jim's post, as already pointed out by
> several others, is not entirely clear), he could use a trigger like
> this:
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> WHERE i.type = 'xyz'
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> Or, as an alternative, use an indexed view:
> CREATE VIEW dbo.MyView WITH SCHEMABINDING
> AS
> SELECT id -- more columns may be added to help other queries
> FROM dbo.<BaseTable>
> WHERE type = 'xyz';
> go
> CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
> go
> (Untested)
> --
> Hugo Kornelis, SQL Server MVP
>
|||Or doens't like the responses.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C96ED8FF-B564-4960-93DD-2F139ADCB18E@.microsoft.com...[vbcol=seagreen]
> Hi Hugo
> Jim seems to start a new thread each time he hits a problem with this
> trigger!
> John
>
> "Hugo Kornelis" wrote:

q; duplicate dhcek on two columns

Hello,
I have a table T1 and on this table I have an insert trigger. In the trigger
I need to check if T1.ID and T1.Type=’xyz’ together are duplicated or no
t
(duplicate dhcek on two columns), if yes return error from trigger. There
might be T1.ID and T1.Type=’abc’ duplicated, that is fine.I guess I'm a bit confused about what you are attempting to accomplish.
It seems that you allow some duplicate values ('abc') between the two
columns-but not others. (If it was a simple as no duplicate values, then a
PK would work.)
Is it that a new row cannot have both t1.ID AND t1.Type = 'xyz',
OR
A new row can have a t1.ID or t1.Type = 'xyz' IF another row has either but
not both,
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values of either t1.ID or t1.Type = 'xyz',
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values for t1.ID AND t1.Type = 'xyz',
OR ...
Is it only the value 'xyz' that is of concern?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A17263B7-72E0-46D7-8431-FECFC522C810@.microsoft.com...
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the
> trigger
> I need to check if T1.ID and T1.Type='xyz' together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type='abc' duplicated, that is fine.
>|||Hi
Why not have a unique index on these two columns?
Adapted from http://www.sommarskog.se/error-hand...#triggercontext
Try the following code in your trigger:
IF EXISTS (SELECT *
FROM inserted i
JOIN T1 T ON i.id = T1.id AND i.type = T1.type
)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Duplicate Values Entered.', 16, 1)
RETURN
END
John
"JIM.H." wrote:

> Hello,
> I have a table T1 and on this table I have an insert trigger. In the trigg
er
> I need to check if T1.ID and T1.Type=’xyz’ together are duplicated or
not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type=’abc’ duplicated, that is fine.
>|||On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:

>Hi
>Why not have a unique index on these two columns?
Hi John,
Agreed 100%.

>Adapted from http://www.sommarskog.se/error-hand...#triggercontext
>Try the following code in your trigger:
>IF EXISTS (SELECT *
> FROM inserted i
> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
> )
>BEGIN
> ROLLBACK TRANSACTION
> RAISERROR('Duplicate Values Entered.', 16, 1)
> RETURN
>END
But this won't work. In an AFTER trigger, this will always result in
error since the new rows are already in the table once the trigger
fires. And in an INSTEAD OF trigger, this will catch the addition of a
second row if the first is already there, but it will miss the insertion
of two duplicate rows in a single INSERT execution.
For an AFTER trigger, this would probably work (untested, though):
IF EXISTS (SELECT *
FROM inserted AS i
INNER JOIN <BaseTable> AS b
ON i.id = b.id
AND i.type = b.type
GROUP BY b.id, b.type
HAVING COUNT(*) > 1)
BEGIN;
ROLLBACK TRANSACTION;
RAISERROR('Duplicate Values Entered.', 16, 1);
RETURN;
END;
Hugo Kornelis, SQL Server MVP|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.
4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.
4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP|||Hi Arnie
That is not the way I interpreted the post, but the post is not well
defined. Posting DDL, sample data and example statements that would work and
would not work would have been better!
John
"Arnie Rowland" wrote:

> Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
> probably won't work for him.
> "check if T1.ID and T1.Type='xyz' together are duplicated"
> AND
> "There might be T1.ID and T1.Type='abc' duplicated, that is fine."
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.
4ax.com...
>
>|||Thanks Hugo, unique constraints would not work since a specific value should
not be repeated not all. I would go with your second suggestion.
"Hugo Kornelis" wrote:

> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
>|||Hi
Without examples it is still ambiguous what you are trying to do. A compound
unique constraint on the two columns would have the same effect as the sampl
e
code.
John
"JIM.H." wrote:
[vbcol=seagreen]
> Thanks Hugo, unique constraints would not work since a specific value shou
ld
> not be repeated not all. I would go with your second suggestion.
> "Hugo Kornelis" wrote:
>|||On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:

>Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
>probably won't work for him.
>"check if T1.ID and T1.Type='xyz' together are duplicated"
>AND
>"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Hi Arnie,
You're right - I missed that part of Jim's message.
If this is indeed the requirement (Jim's post, as already pointed out by
several others, is not entirely clear), he could use a trigger like
this:
IF EXISTS (SELECT *
FROM inserted AS i
INNER JOIN <BaseTable> AS b
ON i.id = b.id
AND i.type = b.type
WHERE i.type = 'xyz'
GROUP BY b.id, b.type
HAVING COUNT(*) > 1)
BEGIN;
ROLLBACK TRANSACTION;
RAISERROR('Duplicate Values Entered.', 16, 1);
RETURN;
END;
Or, as an alternative, use an indexed view:
CREATE VIEW dbo.MyView WITH SCHEMABINDING
AS
SELECT id -- more columns may be added to help other queries
FROM dbo.<BaseTable>
WHERE type = 'xyz';
go
CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
go
(Untested)
Hugo Kornelis, SQL Server MVP

q; duplicate dhcek on two columns

Hello,
I have a table T1 and on this table I have an insert trigger. In the trigger
I need to check if T1.ID and T1.Type=â'xyzâ' together are duplicated or not
(duplicate dhcek on two columns), if yes return error from trigger. There
might be T1.ID and T1.Type=â'abcâ' duplicated, that is fine.I guess I'm a bit confused about what you are attempting to accomplish.
It seems that you allow some duplicate values ('abc') between the two
columns-but not others. (If it was a simple as no duplicate values, then a
PK would work.)
Is it that a new row cannot have both t1.ID AND t1.Type = 'xyz',
OR
A new row can have a t1.ID or t1.Type = 'xyz' IF another row has either but
not both,
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values of either t1.ID or t1.Type = 'xyz',
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values for t1.ID AND t1.Type = 'xyz',
OR ...
Is it only the value 'xyz' that is of concern?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A17263B7-72E0-46D7-8431-FECFC522C810@.microsoft.com...
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the
> trigger
> I need to check if T1.ID and T1.Type='xyz' together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type='abc' duplicated, that is fine.
>|||Hi
Why not have a unique index on these two columns?
Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
Try the following code in your trigger:
IF EXISTS (SELECT *
FROM inserted i
JOIN T1 T ON i.id = T1.id AND i.type = T1.type
)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Duplicate Values Entered.', 16, 1)
RETURN
END
John
"JIM.H." wrote:
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the trigger
> I need to check if T1.ID and T1.Type=â'xyzâ' together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type=â'abcâ' duplicated, that is fine.
>|||On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>Hi
>Why not have a unique index on these two columns?
Hi John,
Agreed 100%.
>Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
>Try the following code in your trigger:
>IF EXISTS (SELECT *
> FROM inserted i
> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
>)
>BEGIN
> ROLLBACK TRANSACTION
> RAISERROR('Duplicate Values Entered.', 16, 1)
> RETURN
>END
But this won't work. In an AFTER trigger, this will always result in
error since the new rows are already in the table once the trigger
fires. And in an INSTEAD OF trigger, this will catch the addition of a
second row if the first is already there, but it will miss the insertion
of two duplicate rows in a single INSERT execution.
For an AFTER trigger, this would probably work (untested, though):
IF EXISTS (SELECT *
FROM inserted AS i
INNER JOIN <BaseTable> AS b
ON i.id = b.id
AND i.type = b.type
GROUP BY b.id, b.type
HAVING COUNT(*) > 1)
BEGIN;
ROLLBACK TRANSACTION;
RAISERROR('Duplicate Values Entered.', 16, 1);
RETURN;
END;
--
Hugo Kornelis, SQL Server MVP|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>>Hi
>>Why not have a unique index on these two columns?
> Hi John,
> Agreed 100%.
>
>>Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
>>Try the following code in your trigger:
>>IF EXISTS (SELECT *
>> FROM inserted i
>> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
>>)
>>BEGIN
>> ROLLBACK TRANSACTION
>> RAISERROR('Duplicate Values Entered.', 16, 1)
>> RETURN
>>END
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>>Hi
>>Why not have a unique index on these two columns?
> Hi John,
> Agreed 100%.
>
>>Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
>>Try the following code in your trigger:
>>IF EXISTS (SELECT *
>> FROM inserted i
>> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
>>)
>>BEGIN
>> ROLLBACK TRANSACTION
>> RAISERROR('Duplicate Values Entered.', 16, 1)
>> RETURN
>>END
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP|||Hi Arnie
That is not the way I interpreted the post, but the post is not well
defined. Posting DDL, sample data and example statements that would work and
would not work would have been better!
John
"Arnie Rowland" wrote:
> Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
> probably won't work for him.
> "check if T1.ID and T1.Type='xyz' together are duplicated"
> AND
> "There might be T1.ID and T1.Type='abc' duplicated, that is fine."
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> > On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
> >
> >>Hi
> >>
> >>Why not have a unique index on these two columns?
> >
> > Hi John,
> >
> > Agreed 100%.
> >
> >
> >>Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
> >>
> >>Try the following code in your trigger:
> >>
> >>IF EXISTS (SELECT *
> >> FROM inserted i
> >> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
> >>)
> >>BEGIN
> >> ROLLBACK TRANSACTION
> >> RAISERROR('Duplicate Values Entered.', 16, 1)
> >> RETURN
> >>END
> >
> > But this won't work. In an AFTER trigger, this will always result in
> > error since the new rows are already in the table once the trigger
> > fires. And in an INSTEAD OF trigger, this will catch the addition of a
> > second row if the first is already there, but it will miss the insertion
> > of two duplicate rows in a single INSERT execution.
> >
> > For an AFTER trigger, this would probably work (untested, though):
> >
> > IF EXISTS (SELECT *
> > FROM inserted AS i
> > INNER JOIN <BaseTable> AS b
> > ON i.id = b.id
> > AND i.type = b.type
> > GROUP BY b.id, b.type
> > HAVING COUNT(*) > 1)
> > BEGIN;
> > ROLLBACK TRANSACTION;
> > RAISERROR('Duplicate Values Entered.', 16, 1);
> > RETURN;
> > END;
> >
> > --
> > Hugo Kornelis, SQL Server MVP
>
>|||Thanks Hugo, unique constraints would not work since a specific value should
not be repeated not all. I would go with your second suggestion.
"Hugo Kornelis" wrote:
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
> >Hi
> >
> >Why not have a unique index on these two columns?
> Hi John,
> Agreed 100%.
>
> >Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
> >
> >Try the following code in your trigger:
> >
> >IF EXISTS (SELECT *
> > FROM inserted i
> > JOIN T1 T ON i.id = T1.id AND i.type = T1.type
> >)
> >BEGIN
> > ROLLBACK TRANSACTION
> > RAISERROR('Duplicate Values Entered.', 16, 1)
> > RETURN
> >END
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
>|||Hi
Without examples it is still ambiguous what you are trying to do. A compound
unique constraint on the two columns would have the same effect as the sample
code.
John
"JIM.H." wrote:
> Thanks Hugo, unique constraints would not work since a specific value should
> not be repeated not all. I would go with your second suggestion.
> "Hugo Kornelis" wrote:
> > On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
> >
> > >Hi
> > >
> > >Why not have a unique index on these two columns?
> >
> > Hi John,
> >
> > Agreed 100%.
> >
> >
> > >Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
> > >
> > >Try the following code in your trigger:
> > >
> > >IF EXISTS (SELECT *
> > > FROM inserted i
> > > JOIN T1 T ON i.id = T1.id AND i.type = T1.type
> > >)
> > >BEGIN
> > > ROLLBACK TRANSACTION
> > > RAISERROR('Duplicate Values Entered.', 16, 1)
> > > RETURN
> > >END
> >
> > But this won't work. In an AFTER trigger, this will always result in
> > error since the new rows are already in the table once the trigger
> > fires. And in an INSTEAD OF trigger, this will catch the addition of a
> > second row if the first is already there, but it will miss the insertion
> > of two duplicate rows in a single INSERT execution.
> >
> > For an AFTER trigger, this would probably work (untested, though):
> >
> > IF EXISTS (SELECT *
> > FROM inserted AS i
> > INNER JOIN <BaseTable> AS b
> > ON i.id = b.id
> > AND i.type = b.type
> > GROUP BY b.id, b.type
> > HAVING COUNT(*) > 1)
> > BEGIN;
> > ROLLBACK TRANSACTION;
> > RAISERROR('Duplicate Values Entered.', 16, 1);
> > RETURN;
> > END;
> >
> > --
> > Hugo Kornelis, SQL Server MVP
> >|||On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:
>Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
>probably won't work for him.
>"check if T1.ID and T1.Type='xyz' together are duplicated"
>AND
>"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Hi Arnie,
You're right - I missed that part of Jim's message.
If this is indeed the requirement (Jim's post, as already pointed out by
several others, is not entirely clear), he could use a trigger like
this:
IF EXISTS (SELECT *
FROM inserted AS i
INNER JOIN <BaseTable> AS b
ON i.id = b.id
AND i.type = b.type
WHERE i.type = 'xyz'
GROUP BY b.id, b.type
HAVING COUNT(*) > 1)
BEGIN;
ROLLBACK TRANSACTION;
RAISERROR('Duplicate Values Entered.', 16, 1);
RETURN;
END;
Or, as an alternative, use an indexed view:
CREATE VIEW dbo.MyView WITH SCHEMABINDING
AS
SELECT id -- more columns may be added to help other queries
FROM dbo.<BaseTable>
WHERE type = 'xyz';
go
CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
go
(Untested)
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo
Jim seems to start a new thread each time he hits a problem with this trigger!
John
"Hugo Kornelis" wrote:
> On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:
> >Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
> >probably won't work for him.
> >
> >"check if T1.ID and T1.Type='xyz' together are duplicated"
> >AND
> >"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
> Hi Arnie,
> You're right - I missed that part of Jim's message.
> If this is indeed the requirement (Jim's post, as already pointed out by
> several others, is not entirely clear), he could use a trigger like
> this:
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> WHERE i.type = 'xyz'
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> Or, as an alternative, use an indexed view:
> CREATE VIEW dbo.MyView WITH SCHEMABINDING
> AS
> SELECT id -- more columns may be added to help other queries
> FROM dbo.<BaseTable>
> WHERE type = 'xyz';
> go
> CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
> go
> (Untested)
> --
> Hugo Kornelis, SQL Server MVP
>|||Or doens't like the responses.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C96ED8FF-B564-4960-93DD-2F139ADCB18E@.microsoft.com...
> Hi Hugo
> Jim seems to start a new thread each time he hits a problem with this
> trigger!
> John
>
> "Hugo Kornelis" wrote:
>> On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:
>> >Unless the OP has misstated his scenario, I think that a UNIQUE
>> >CONSTRAINT
>> >probably won't work for him.
>> >
>> >"check if T1.ID and T1.Type='xyz' together are duplicated"
>> >AND
>> >"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
>> Hi Arnie,
>> You're right - I missed that part of Jim's message.
>> If this is indeed the requirement (Jim's post, as already pointed out by
>> several others, is not entirely clear), he could use a trigger like
>> this:
>> IF EXISTS (SELECT *
>> FROM inserted AS i
>> INNER JOIN <BaseTable> AS b
>> ON i.id = b.id
>> AND i.type = b.type
>> WHERE i.type = 'xyz'
>> GROUP BY b.id, b.type
>> HAVING COUNT(*) > 1)
>> BEGIN;
>> ROLLBACK TRANSACTION;
>> RAISERROR('Duplicate Values Entered.', 16, 1);
>> RETURN;
>> END;
>> Or, as an alternative, use an indexed view:
>> CREATE VIEW dbo.MyView WITH SCHEMABINDING
>> AS
>> SELECT id -- more columns may be added to help other queries
>> FROM dbo.<BaseTable>
>> WHERE type = 'xyz';
>> go
>> CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
>> go
>> (Untested)
>> --
>> Hugo Kornelis, SQL Server MVP

Q: subtotal of rows in matrix

Hello,
I have a matrix report, how can I sum rows under a column. It seems add
subtotal works for summing of columns not rows.
Thanks,Nver mind. Found it.
"JIM.H." wrote:
> Hello,
> I have a matrix report, how can I sum rows under a column. It seems add
> subtotal works for summing of columns not rows.
> Thanks,
>|||Hey
I've been having the same problem, only with both rows and columns.
The subtotal for the row only grabs the first column's value and th
column subtotal only grabs the first row's value. Could you pleas
explain how you got the subtotals to work? If you can't at least pos
the solution you figured out
Thanks