SQL server connection: table

Showing posts with label table. Show all posts

Tuesday, March 20, 2012

Another way than using a Cursor

Hi all,
I have the following example Table & Data...
Create Table Test
(
Cust_Code varchar(10),
Acc_Deal_Type Varchar(3),
Account_No Varchar(30),
AccountTypes Varchar(50)
)
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123456','MMC','12345678')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123456','MMC','12345679')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123456','CCL','12345680')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123457','MMC','12345681')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123457','MMC','12345682')
I need to concatanate the distinct values of Acc_Deal_Types Column for
each Cust_Code and set the AccountTypes column with the Result.
So, in this example, the grouped results would be two rows with the
Cust_Code & AccountTypes Column selected:
1st Row
Cust_Code = 123456
AccountType = MMC,CCL
2nd Row
Cust_Code = 123457
AccountType = MMC
How can I create the AccountTypes column without using a cursor? The
Table in question has around 7000 rows.
Thanks
Barrytry
select distinct Acc_Deal_Type + Cust_Code as NewType into Test# from Test
update Test
set Acc_Deal_Type = NewType
-- select Cust_Code, Acc_Deal_Type, Account_No, AccountTypes , NewType
from Test a, Test# b
where NewType = Acc_Deal_Type + Cust_Code
increase the zize of the Acc_Deal_Type though|||Hi, Barry
See: http://www.aspfaq.com/show.asp?id=2529
Razvan

Another version of SQL Permission Question

How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base table
You can "update" a view in certain restricted conditions (like there
can't be any joins in the view for instance - see BOL for the complete
list of restrictions). Alternately, you can write an INSTEAD OF trigger
on the view so that the trigger code handles the updating of the
underlying tables when a user runs an UPDATE statement against the
view. You can use this method when the basic updatable view
requirements cannot be met.
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view).
Cheers,
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
KathyV wrote:

>How do I restrict update access to a table based on criteria within the
>table. I'm trying to use a view and limit access to the base table
>

Another version of SQL Permission Question

How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base tableYou can "update" a view in certain restricted conditions (like there
can't be any joins in the view for instance - see BOL for the complete
list of restrictions). Alternately, you can write an INSTEAD OF trigger
on the view so that the trigger code handles the updating of the
underlying tables when a user runs an UPDATE statement against the
view. You can use this method when the basic updatable view
requirements cannot be met.
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view).
Cheers,
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
KathyV wrote:

>How do I restrict update access to a table based on criteria within the
>table. I'm trying to use a view and limit access to the base table
>

Another version of SQL Permission Question

How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base tableThis is a multi-part message in MIME format.
--000602000304030604090506
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
You can "update" a view in certain restricted conditions (like there
can't be any joins in the view for instance - see BOL for the complete
list of restrictions). Alternately, you can write an INSTEAD OF trigger
on the view so that the trigger code handles the updating of the
underlying tables when a user runs an UPDATE statement against the
view. You can use this method when the basic updatable view
requirements cannot be met.
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view).
Cheers,
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
KathyV wrote:
>How do I restrict update access to a table based on criteria within the
>table. I'm trying to use a view and limit access to the base table
>
--000602000304030604090506
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>You can "update" a view in certain restricted conditions (like
there can't be any joins in the view for instance - see BOL for the
complete list of restrictions).Â Alternately, you can write an INSTEAD
OF trigger on the view so that the trigger code handles the updating of
the underlying tables when a user runs an UPDATE statement against the
view.Â You can use this method when the basic updatable view
requirements cannot be met. 
 
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view). 
 
Cheers, 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
KathyV wrote:
<blockquote cite="mid282DBEFC-5100-4936-A1EA-A11561D1DDD4@.microsoft.com"
type="cite">
<pre wrap="">How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base table
</pre>
</blockquote>
</body>
</html>
--000602000304030604090506--

Another T-SQL puzzle

Hi all,
Sorry if this has been asked before, I've had a very long day and I think my
brain has switched off...
I have a table that records transactions (changes) in another table, and I'm
interested in getting an audit trail (i.e. who did what) for specific state
changes.
So, given the following... (there are more columns, such as user id etc,
but these are irrelevant here)
CREATE TABLE [StateChanges] (
[m_id] [int] IDENTITY (1, 1) NOT NULL,
[ChangeDateTime] [datetime] NOT NULL CONSTRAINT
[DF_StateChanges_JournalDateTime] DEFAULT (GETDATE()),
[SensorID] [int] NOT NULL,
[State1] [varchar] (20) NOT NULL,
[State2] [varchar] (20) NOT NULL
) ON [PRIMARY]
GO
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 01:46:19.543', '1670', 'Pending', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 02:21:33.153', '1670', 'New', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 01:46:20.077', '1671', 'Pending', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 02:17:48.030', '1671', 'OK', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 03:32:20.450', '1672', 'Pending', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 03:32:20.483', '1672', 'New', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 04:37:35.390', '1673', 'OK', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 04:37:35.403', '1673', 'New', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 04:37:42.293', '1673', 'New', 'Requested')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 04:38:13.340', '1674', 'Pending', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 04:38:13.357', '1674', 'Manual', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 04:38:16.857', '1674', 'Manual', 'Requested')
If I'm looking for changes in State1 in each SensorID, then I'd want to get
the rows where State1 has changed from Pending to New, or from Pending to OK
etc. There are occasions where another state will change and State1 will
stay the same, in which case I only want the first occurence.
So for the above data, I'd be interested in rows 2, 4, 6, 8 and 11.
If anyone can help me here it would be greatly appreciated!!!
Cheers,
AlexThe key to this type of problem is a perfect sequence number. In your
example, for clarity I can cheat and use the identity value since you have
sorted the inserts by SensorIf and ChangeDateTime.
You just shift the data to check the previous value against the current
value. In SQL Server 2005, this is pretty easy to do
with orderedSet as
( --this set is used twice in the following query
select M_Id, ChangeDateTime, SensorID, State1, State2,
--this sets up an ordering number per group
row_number() over (partition by sensorId order by changeDateTime) as
ordering
from stateChanges
)
select *
from orderedSet as s
join orderedSet as s2
on s.sensorId = s2.sensorId
and s.ordering = s2.ordering + 1 --use this column instead of m_id
because it is safer
where s2.state1 <> s.state1
For 2000, you have to do the row number yourself, so it is more complex,
especially if your data set is far more complex.
select M_Id, ChangeDateTime, SensorID, State1, State2,
--this sets up an ordering number per group
(select count(*)
from stateChanges as s2
where s2.sensorId = stateChanges.sensorId
and s2.changeDateTime <= stateChanges.changeDateTime) as ordering
into #orderedSet
from stateChanges
select *
from #orderedSet as s
join #orderedSet as s2
on s.sensorId = s2.sensorId
and s.ordering = s2.ordering + 1 --use this column instead of m_id
because it is safer
where s2.state1 <> s.state1
I use a temp table because it is just so much cleaner to deal with with the
two references since temp views are not allowed. Hope this help :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Alex" <nospam@.hotmail.com> wrote in message
news:43ed6f20$0$5013$db0fefd9@.news.zen.co.uk...
> Hi all,
> Sorry if this has been asked before, I've had a very long day and I think
> my brain has switched off...
> I have a table that records transactions (changes) in another table, and
> I'm interested in getting an audit trail (i.e. who did what) for specific
> state changes.
> So, given the following... (there are more columns, such as user id etc,
> but these are irrelevant here)
> CREATE TABLE [StateChanges] (
> [m_id] [int] IDENTITY (1, 1) NOT NULL,
> [ChangeDateTime] [datetime] NOT NULL CONSTRAINT
> [DF_StateChanges_JournalDateTime] DEFAULT (GETDATE()),
> [SensorID] [int] NOT NULL,
> [State1] [varchar] (20) NOT NULL,
> [State2] [varchar] (20) NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 01:46:19.543', '1670', 'Pending', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 02:21:33.153', '1670', 'New', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 01:46:20.077', '1671', 'Pending', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 02:17:48.030', '1671', 'OK', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 03:32:20.450', '1672', 'Pending', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 03:32:20.483', '1672', 'New', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 04:37:35.390', '1673', 'OK', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 04:37:35.403', '1673', 'New', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 04:37:42.293', '1673', 'New', 'Requested')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 04:38:13.340', '1674', 'Pending', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 04:38:13.357', '1674', 'Manual', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 04:38:16.857', '1674', 'Manual', 'Requested')
>
> If I'm looking for changes in State1 in each SensorID, then I'd want to
> get the rows where State1 has changed from Pending to New, or from Pending
> to OK etc. There are occasions where another state will change and State1
> will stay the same, in which case I only want the first occurence.
> So for the above data, I'd be interested in rows 2, 4, 6, 8 and 11.
> If anyone can help me here it would be greatly appreciated!!!
> Cheers,
> Alex
>

another trigger question

I have a table named userInfo which has fields userID (uses Identity to fill
in), firstname, familyname.
I have a second table named administration which has fields userID and
userName.
I would like to have a trigger which when a new record is added to userInfo
creates a record in the administration table using the same userID and
joining the familyname and firstname fields from UserInfo together to make
the userName field in administration.
Could someone help me with the syntax.
Thank you
June
Why do you need an extra table for this? You can create a view called
vAdministration which calls:
SELECT userID, username = firstName + familyName FROM userInfo
No reason to store the data twice!
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"June Macleod" <junework@.hotmail.com> wrote in message
news:Oz734RpPEHA.2996@.TK2MSFTNGP12.phx.gbl...
> I have a table named userInfo which has fields userID (uses Identity to
fill
> in), firstname, familyname.
> I have a second table named administration which has fields userID and
> userName.
> I would like to have a trigger which when a new record is added to
userInfo
> creates a record in the administration table using the same userID and
> joining the familyname and firstname fields from UserInfo together to make
> the userName field in administration.
> Could someone help me with the syntax.
> Thank you
> June
>
|||On Thu, 20 May 2004 19:03:45 +0100, June Macleod wrote:

>I have a table named userInfo which has fields userID (uses Identity to fill
>in), firstname, familyname.
>I have a second table named administration which has fields userID and
>userName.
>I would like to have a trigger which when a new record is added to userInfo
>creates a record in the administration table using the same userID and
>joining the familyname and firstname fields from UserInfo together to make
>the userName field in administration.
>Could someone help me with the syntax.
>Thank you
>June
Hi June,
I could, but first I'll advice you to drop the administration table and
create an administration view instead:
CREATE VIEW administration AS
SELECT userID, familyname + ', ' + firstname AS userName
FROM userInfo
go
Another option would be to (again) drop the administration table and add
userName as computed column in the userInfo table:
ALTER TABLE userInfo
ADD userName AS familyname + ', ' + firstname
go
But if you really want to use seperate tables and keep it current with
triggers, you'll need not one but three triggers:
CREATE TRIGGER ins_userInfo
ON userInfo
AFTER INSERT
AS
IF @.@.ROWCOUNT > 0
INSERT administration (userID, userName)
SELECT userID, familyname + ', ' + firstname
FROM inserted
go
CREATE TRIGGER upd_userInfo
ON userInfo
AFTER UPDATE
AS
IF @.@.ROWCOUNT > 0 AND (UPDATE(familyname) OR UPDATE(firstname))
UPDATE administration
SET userName = familyname + ', ' + firstname
WHERE userID IN (SELECT userID FROM inserted)
go
CREATE TRIGGER del_userInfo
ON userInfo
AFTER DELETE
AS
IF @.@.ROWCOUNT > 0
DELETE administration
WHERE userID IN (SELECT userID FROM deleted)
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||> But if you really want to use seperate tables and keep it current with
> triggers, you'll need not one but three triggers:
Yes, excellent point... not only getting the data there on insert, but
keeping the tables in sync.
|||First of all let me thank you very much for your help. It is much
appreciated.
I have taken on board your advice about dropping the administration table
and using a view instead.
However, I am still trying to get the trigger to work as it will be good
practice for me for future tables which will require this type of update.
The insert and delete triggers work well however I am having problems with
the update trigger.
Create Trigger dbo.userInfo_Trigger1
On dbo.userInfo
AFTER UPDATE
AS
IF @.@.ROWCOUNT > 0 AND (UPDATE(familyname) OR UPDATE(firstname))
UPDATE administration
SET userName = familyname + ', ' + firstname
WHERE userID IN (SELECT userID FROM inserted)
When I try to save the trigger it comes back with an error message "ADO
Error: Invalid column name 'familyname'. Invalid column name 'firstname'."
These are the correct names in the userInfo table.
I am making the assumption that the 'inserted' table (and likewise the
'deleted' table ) are temporary tables created during the edit process.
Does an inserted table get created when an update is taking place or only
when a new record is created?
Thanks again
June
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:9o3qa0piuc0mm1j2lpvle020joi75glis4@.4ax.com...
> On Thu, 20 May 2004 19:03:45 +0100, June Macleod wrote:

> I could, but first I'll advice you to drop the administration table and
> create an administration view instead:
> CREATE VIEW administration AS
> SELECT userID, familyname + ', ' + firstname AS userName
> FROM userInfo
> go
> Another option would be to (again) drop the administration table and add
> userName as computed column in the userInfo table:
> ALTER TABLE userInfo
> ADD userName AS familyname + ', ' + firstname
> go
> But if you really want to use seperate tables and keep it current with
> triggers, you'll need not one but three triggers:
> CREATE TRIGGER ins_userInfo
> ON userInfo
> AFTER INSERT
> AS
> IF @.@.ROWCOUNT > 0
> INSERT administration (userID, userName)
> SELECT userID, familyname + ', ' + firstname
> FROM inserted
> go
> CREATE TRIGGER upd_userInfo
> ON userInfo
> AFTER UPDATE
> AS
> IF @.@.ROWCOUNT > 0 AND (UPDATE(familyname) OR UPDATE(firstname))
> UPDATE administration
> SET userName = familyname + ', ' + firstname
> WHERE userID IN (SELECT userID FROM inserted)
> go
> CREATE TRIGGER del_userInfo
> ON userInfo
> AFTER DELETE
> AS
> IF @.@.ROWCOUNT > 0
> DELETE administration
> WHERE userID IN (SELECT userID FROM deleted)
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Fri, 21 May 2004 11:14:02 +0100, June Macleod wrote:
Hi June,

>First of all let me thank you very much for your help. It is much
>appreciated.
>I have taken on board your advice about dropping the administration table
>and using a view instead.
Good - glad to hear that.

>However, I am still trying to get the trigger to work as it will be good
>practice for me for future tables which will require this type of update.
That's why I went on to give you the trigger code after advising against
it.

>The insert and delete triggers work well however I am having problems with
>the update trigger.
>Create Trigger dbo.userInfo_Trigger1
>On dbo.userInfo
>AFTER UPDATE
>AS
>IF @.@.ROWCOUNT > 0 AND (UPDATE(familyname) OR UPDATE(firstname))
>UPDATE administration
>SET userName = familyname + ', ' + firstname
>WHERE userID IN (SELECT userID FROM inserted)
>When I try to save the trigger it comes back with an error message "ADO
>Error: Invalid column name 'familyname'. Invalid column name 'firstname'."
>These are the correct names in the userInfo table.
My fault. When I wrote that trigger, I momentarily forgot that userName is
not in the same table as familyname and firstname. The UPDATE statement
should read
UPDATE administration
SET userName = (SELECT familyname + ', ' + firstname
FROM inserted
WHERE inserted.userID = administration.userID)
WHERE userID IN (SELECT userID FROM inserted)

>I am making the assumption that the 'inserted' table (and likewise the
>'deleted' table ) are temporary tables created during the edit process.
Though technically incorrect, you might as well think of it that way. (The
exact technical explanation is that deleted and inserted are not temporary
tables, but pseudo-tables - they never really exist, but their contents
are reconstructed from the log file every time they are needed. If you
have a trigger that refers to inserted and deleted a lot, you might gain
performance by explicitly copying the data from those pseudo-tables to
temporary tables).

>Does an inserted table get created when an update is taking place or only
>when a new record is created?
First, it's important to note that the inserted and deleted pseudo-tables
can only be referenced inside a trigger. Even a stored procedure that is
called from a trigger has no access to inserted or deleted.
If a trigger is started as a result of an INSERT statement, than the
deleted pseudo-table will always be empty; the inserted pseudotable
contains all rows inserted by the INSERT statement.
If a trigger is started as a result of a DELETE statement, than the
deleted pseudo-table will contain all rows that are deleted by the DELETE
statement; the inserted pseudotable will be empty.
If a trigger is started as a result of an UPDATE statement, the deleted
pseudotable will contain all rows that match the WHERE clause of the
update, with all data as it was BEFORE the update; the inserted
pseudotable will contain the same rows as they appear AFTER applying the
SET clause of the update. If the UPDATE statement changed the value of the
primary key column(s) (which is unfortunately allowed in SQL Server), it
can be quite hard to find out which row in inserted matches which row in
deleted.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

another trigger question

Is trigger based on table level or can I define it on certain fields level
against a table?
A trigger is defined on an INSERT, UPDATE or DELETE statement against a
table (in fact you can write a special kind (INSTEAD OF trigger) against
views now too). INSERTs & DELETEs work on an entire row so it doesn't make
much sense to do stuff at the column level there, but within a trigger on an
UPDATE operation you can define specific behaviour if certain columns on the
table are changed with the IF UPDATE() clause (it works with INSERT triggers
too but makes less sense). For example,
CREATE TRIGGER MyInsertTrigger on dbo.MyTable
FOR UPDATE AS
-- Check to see if a specific column has changed
if UPDATE(MyCol)
BEGIN
-- Do some SQL stuff in here because the column we're interested
in has changed
END
-- Do other general UPDATE trigger stuff
GO
SQL Books Online has more info on it in the "Creating and Maintaining
Databases" | "Enforcing Business Rules with Triggers" | "Creating a Trigger"
| "Programming Triggers" section.
HTH.
Cheers,
Mike
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:ud8HZU20EHA.3584@.TK2MSFTNGP11.phx.gbl...
> Is trigger based on table level or can I define it on certain fields level
> against a table?
>
sql

another trigger question

Is trigger based on table level or can I define it on certain fields level
against a table?A trigger is defined on an INSERT, UPDATE or DELETE statement against a
table (in fact you can write a special kind (INSTEAD OF trigger) against
views now too). INSERTs & DELETEs work on an entire row so it doesn't make
much sense to do stuff at the column level there, but within a trigger on an
UPDATE operation you can define specific behaviour if certain columns on the
table are changed with the IF UPDATE() clause (it works with INSERT triggers
too but makes less sense). For example,
CREATE TRIGGER MyInsertTrigger on dbo.MyTable
FOR UPDATE AS
-- Check to see if a specific column has changed
if UPDATE(MyCol)
BEGIN
-- Do some SQL stuff in here because the column we're interested
in has changed
END
-- Do other general UPDATE trigger stuff
GO
SQL Books Online has more info on it in the "Creating and Maintaining
Databases" | "Enforcing Business Rules with Triggers" | "Creating a Trigger"
| "Programming Triggers" section.
HTH.
--
Cheers,
Mike
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:ud8HZU20EHA.3584@.TK2MSFTNGP11.phx.gbl...
> Is trigger based on table level or can I define it on certain fields level
> against a table?
>

another trigger question

Is trigger based on table level or can I define it on certain fields level
against a table?A trigger is defined on an INSERT, UPDATE or DELETE statement against a
table (in fact you can write a special kind (INSTEAD OF trigger) against
views now too). INSERTs & DELETEs work on an entire row so it doesn't make
much sense to do stuff at the column level there, but within a trigger on an
UPDATE operation you can define specific behaviour if certain columns on the
table are changed with the IF UPDATE() clause (it works with INSERT triggers
too but makes less sense). For example,
CREATE TRIGGER MyInsertTrigger on dbo.MyTable
FOR UPDATE AS
-- Check to see if a specific column has changed
if UPDATE(MyCol)
BEGIN
-- Do some SQL stuff in here because the column we're interested
in has changed
END
-- Do other general UPDATE trigger stuff
GO
SQL Books Online has more info on it in the "Creating and Maintaining
Databases" | "Enforcing Business Rules with Triggers" | "Creating a Trigger"
| "Programming Triggers" section.
HTH.
Cheers,
Mike
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:ud8HZU20EHA.3584@.TK2MSFTNGP11.phx.gbl...
> Is trigger based on table level or can I define it on certain fields level
> against a table?
>

Monday, March 19, 2012

Another Temporary Table Q

Hi
I've been playing around with temporary tables ... everything works fine,
but I've read a few articles saying how they can affect overall performance
and I'm wondering if there may be a faster alternative performance.
Basically, the user fires off a query at the DB. With the results, I need to
delete some rows which are retrieved as well (they cannot be filtered out as
part of the SELECT statement because of the effects of LEFT JOINS) as amend
certain data values prior to returning the data to the user. I thought
temporary tables would be the way, so I could just
1) Create Temp Table
2) Populate with INSERT
3) DELETE rows fulfilling certain criteria
4) UPDATE certain data values
5) Return contents of Temporary Table to user as ADO dataset.
I'm wondering now if I could actually get all the Data back from the results
of the initial SELECT statement to the local machine fast, and then run the
DELETE/UPDATES locally so as to remove load from the central server. I've
done timings already comparing Temporary Tables to cycling through the raw
dataset locally and deleting rows and/or updating Data Values. Using
temporary tables is much faster.
So is there a way of getting all the data back as fast as possible from the
central server and then speedily deleting/updating rows of the local dataset
other than the way I've been testing. I'd wondered about creating some
in-local table (using DAO, for example) and then firing SQL at that ... but
I'm just not sure if that would be any quicker.
So in summary-
1) What's the quickest way of getting ALL the data from the server to the
local client?
2) What should I use on the local client?
Any thoughts would be greatly appreciated.
Thanks, again
SimonCan you post some more details, including DDL and some sample
data. There's a good chance this can all be achieved in a single
SELECT.|||<markc600@.hotmail.com> wrote in message
news:1137676726.723163.59050@.g47g2000cwa.googlegroups.com...
> Can you post some more details, including DDL and some sample
> data. There's a good chance this can all be achieved in a single
> SELECT.
Thanks for replying.
It will take me a while to get this together, I'm afraid.|||On Thu, 19 Jan 2006 12:40:29 -0000, Simon Woods wrote:

>Hi
>I've been playing around with temporary tables ... everything works fine,
>but I've read a few articles saying how they can affect overall performance
>and I'm wondering if there may be a faster alternative performance.
>Basically, the user fires off a query at the DB. With the results, I need t
o
>delete some rows which are retrieved as well (they cannot be filtered out a
s
>part of the SELECT statement because of the effects of LEFT JOINS) as amend
>certain data values prior to returning the data to the user. I thought
>temporary tables would be the way, so I could just
>1) Create Temp Table
>2) Populate with INSERT
>3) DELETE rows fulfilling certain criteria
>4) UPDATE certain data values
>5) Return contents of Temporary Table to user as ADO dataset.
>I'm wondering now if I could actually get all the Data back from the result
s
>of the initial SELECT statement to the local machine fast, and then run the
>DELETE/UPDATES locally so as to remove load from the central server.
Hi Simon.
Either I am completely misunderstanding what you're writing, or you are
completely misunderstanding how SQL Server works.
From this message, it appearrs as if you want to get all rows to the
client, then iterate over the results and send update or delete
statements for each row "to remove load from the server".
However, since the data is stored on the server, you'll have to do the
deletes and updates on the server as well. This means that in your
version, you'll have lots of network traffic and you force SQL Server to
do individual updates and deletes in the order that your application
processes them.
It's much better to keep all the logic in a single stored procedure on
the server. Not only will this save you lots of network traffic, it will
also enable the query optimizer to pick the best order of execution for
the updates and deletes (provided you write them as set-based UPDATE and
DELETE statement, of course).
Hugo Kornelis, SQL Server MVP|||Hugo ... thanks for replying ... see below
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:qp50t15rumqtlr0din5fdbqc4tlh1g01kf@.
4ax.com...
> On Thu, 19 Jan 2006 12:40:29 -0000, Simon Woods wrote:
>
> Hi Simon.
> Either I am completely misunderstanding what you're writing, or you are
> completely misunderstanding how SQL Server works.
... the latter is most likely ...

> From this message, it appearrs as if you want to get all rows to the
> client, then iterate over the results and send update or delete
> statements for each row "to remove load from the server".
> However, since the data is stored on the server, you'll have to do the
> deletes and updates on the server as well. This means that in your
> version, you'll have lots of network traffic and you force SQL Server to
> do individual updates and deletes in the order that your application
> processes them.
On the client I'm actually wanting a read-only set of data so I don't want
to do any updating of the source data only issuing SQL to update and delete
the temporary table.
Having chatted to some of my colleagues, I've been pointed to ADO
disconnected datasets to try and move load away from temporary tables on the
server to recordset processing on the client. However, at the moment my
preference is to go with the temporary table solution - but I'll have to do
a bit more digging.
Thanks again|||On Fri, 20 Jan 2006 13:06:32 -0000, Simon Woods wrote:
(snip)
>Having chatted to some of my colleagues, I've been pointed to ADO
>disconnected datasets to try and move load away from temporary tables on th
e
>server to recordset processing on the client. However, at the moment my
>preference is to go with the temporary table solution - but I'll have to do
>a bit more digging.
Hi Simon,
I'm not familiar with ADO, so I can't comment on the disconnected
datasets. But considering that SQL Server is designed to do quick and
efficient manipulations on large amounts of data, I'm very much inclined
to agree with your preference of doing it on the server.
Hugo Kornelis, SQL Server MVP

another table corruption question

sql2k sp3
Is there a way to just narrow down the rows of data that are corrupted and
delete them as opposed to the other alternatives? (Restore, CheckTable with
Repair%, etc.)
TIA, ChrisR.There are many types of corruption. The page could be bad. So it is not
always possible to delete the bad row.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"ChrisR" <noemail@.bla.com> wrote in message
news:eDCAX%23nSFHA.576@.TK2MSFTNGP15.phx.gbl...
> sql2k sp3
> Is there a way to just narrow down the rows of data that are corrupted and
> delete them as opposed to the other alternatives? (Restore, CheckTable
> with Repair%, etc.)
>
> TIA, ChrisR.
>
>

another table corruption question

sql2k sp3
Is there a way to just narrow down the rows of data that are corrupted and
delete them as opposed to the other alternatives? (Restore, CheckTable with
Repair%, etc.)
TIA, ChrisR.There are many types of corruption. The page could be bad. So it is not
always possible to delete the bad row.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"ChrisR" <noemail@.bla.com> wrote in message
news:eDCAX%23nSFHA.576@.TK2MSFTNGP15.phx.gbl...
> sql2k sp3
> Is there a way to just narrow down the rows of data that are corrupted and
> delete them as opposed to the other alternatives? (Restore, CheckTable
> with Repair%, etc.)
>
> TIA, ChrisR.
>
>

another table corruption question

sql2k sp3
Is there a way to just narrow down the rows of data that are corrupted and
delete them as opposed to the other alternatives? (Restore, CheckTable with
Repair%, etc.)
TIA, ChrisR.
There are many types of corruption. The page could be bad. So it is not
always possible to delete the bad row.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"ChrisR" <noemail@.bla.com> wrote in message
news:eDCAX%23nSFHA.576@.TK2MSFTNGP15.phx.gbl...
> sql2k sp3
> Is there a way to just narrow down the rows of data that are corrupted and
> delete them as opposed to the other alternatives? (Restore, CheckTable
> with Repair%, etc.)
>
> TIA, ChrisR.
>
>

Another Stored Procedure Question...

Hi,

Is it possible whithin a Stored Procedure send the table name as a parameter?
And some Columns to?

Ex.:

CREATE Procedure Xpto

@.TableName as ?
@.ColumnName as ?
@.SomeValue as nvarchar(10)

SELECT * FROM @.TableName WHERE @.ColumnName = @.SomeValue

Thanks

JPP

create procedure SelectFrom @.table sysname, @.column sysname, @.value sysname
as
exec ('select * from '+ @.table + ' where ' +@.column + ' = ''' + @.value + '''')

|||

You should protect the code above against SQL injection attacks like below:

declare @.tablename nvarchar(130), @.columnname nvarchar(130), @.sql nvarchar(4000)

set @.tablename = quotename(@.table)

set @.columnname = quotename(@.column)

set @.sql = 'select * from ' + @.tablename + ' where ' + @.columnname + ' = @.value'

exec sp_executesql @.sql, N'@.value nvarchar(4000)', @.value = @.value

However, there shouldn't be a need to write such generic stored procedures. It is not a good thing to do. You have to grant permissions to users since dynamic SQL is evaluated at run-time. So please write a stored procedure per table/feature/module that can handle the data access for you. Alternatively, you can also create views and expose the data.

|||Hi,

When I post this question I was thinking on using the solution to dynamicly update some tables with the Numers of the documents.
Ex.:

Table Numbers
NroInvoice
NroReceipt
....

Then, to update one of the numbers I dont have to write a stored procedure for each on.
Do you think this is a bad aprotch?

Thank you for your time.

JPP|||Another reason to avoid such generic stored procedures is that they cannot be optimized and do not generate a pre-compiled execution plan that would be kept for repeated execution.|||Hello...

I would not use a table to hold values like this...

Those information is already in the DB and there is no need to denormalize it. If there are propper indexes on those fields you want to evaluate you can wrtite a small view that extracts the data you need...

By the way... Why is there no SQL Code button on the form ;)

create view DocCount
as
select (select count(*) from Documents where type = 'Invoice') NrOfInvoices,
(select count(*) from Documents where type = 'Mail') NrOfMails

or another way...

create view DocCount2
as
select type, count(*) from Documents group by type

This way there is no need to update this table

Another SQL Stored Procedure Question

Thanks for the help with the last question... I finally got it to work... I have a new problem tho with the same setup -

I need to create a table in the Access97 database and then populate it with SQL Server2k data. I'm using a SELECT ... INTO statement (which works fine in the SQL Enterprise Manager QRY thingy (I'm soooo technical) but it's not working when the stored procedure is called. The Access97 database is set up as a linked server, I'm pulling the database name in thru a parameter along with the condition. Maybe one of you can see something I don't with this code?

CREATE PROCEDURE dbo.usp_PutData @.DbName nvarchar(20), @.State nvarchar(4), @.AirType nvarchar(10)
AS

declare @.QueryIs as varchar(8000)

select @.QueryIs = Case upper(@.AirType)
when 'NFDC'then "SELECT (FldName1, FldName2, FldName3, FldName4, FldName5, FldName6, FldName7) INTO " + @.DbName + "...Facility FROM tbl_NFDC_Facility WHERE (Assoc_State = '" + @.State + "')"
end

Select @.QueryIs, @.DbName
exec (@.QueryIs)
GO

Any and all help is greatly appreciated!Did you cut and paste this code directly from MSSQAT (Microsoft SQL Server Query Analyzer Thingy)? Your use of double quotes is a syntax error. You have to use single quotes, and tripled single quotes to represent an embedded single quote.

Also, you use too many dots qualifying your table name.

@.DbName + "...Facility

should be

@.DbName + "..Facility

otherwise SQL Server will look be looking for a server named after your database. A fully qualified table reference looks like this:
[ServerName].[DatabaseName].[Owner].[Table]

Also, be aware that there are some connection settings that may have different defaults in Query Analyzer than other DB interfaces. For instance, SET CONCAT_NULL_YIELDS_NULL { ON | OFF } defaults to ON for SQL Query Analyzer, but defaults to OFF in Crystal reports.

blindman

another sql query problem

I have a table like this

DESCRIPTION DEBI CREDIT
-----------
Deposit 0.00 100.00
Withdraw 10.00 0.00
Payroll 0.00 230.00
Withdraw 50.00 0.00

How can i make an query that this table look like this

ID DESCRIPTION DEBI CREDIT Current Balance
------------------
1. Deposit 0.00 100.00 100.00
2. Withdraw 10.00 0.00 90.00
3. Payroll 0.00 230.00 320.00
4. Withdraw 50.00 0.00 270.00

Please helpTry this:

SELECT TOP 100 PERCENT QTest.ID, QTest.Description, QTest.Debit, QTest.Credit, SUM(QTest.Credit) - SUM(QTest.Debit) AS [Current Balance]
FROM QTest,
(SELECT TOP 100 * FROM (SELECT ID, Credit, Debit FROM QTest) QTest_1)
AS QTest_1
WHERE QTest.ID >= QTest_1.ID
GROUP BY QTest.ID, QTest.Description, QTest.Debit, QTest.Credit
ORDER BY QTest.ID

where QTest is yout table

Originally posted by bashka_abdyli
I have a table like this

DESCRIPTION DEBI CREDIT
-----------
Deposit 0.00 100.00
Withdraw 10.00 0.00
Payroll 0.00 230.00
Withdraw 50.00 0.00

How can i make an query that this table look like this

ID DESCRIPTION DEBI CREDIT Current Balance
------------------
1. Deposit 0.00 100.00 100.00
2. Withdraw 10.00 0.00 90.00
3. Payroll 0.00 230.00 320.00
4. Withdraw 50.00 0.00 270.00

Please help

another sql query ?

I'm trying to do a mass update, w/different values: I have an Approval Table with the columns, FacilityID, CompanyID, EmployeeID, and SupervisorID. I would like to take the Users SuperID in the [User] Table who have a roleid= 'Supervisor' and place it in the Approval Table. However in order to know which super matches which employee you must match the Employee tables DeptID to the User Tables Department field.

I did the following and it gives me an error:

UPDATE Approval SET SupervisorID = (select u.EmployeeID from [user] u INNER JOIN Employee e ON u.Department = e.deptid WHERE roleid = 'supervisor')

The error is:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

The select gives me the right data--this much I've checked.

You sub-query is probably returning more than 1 row in some cases. You have to make sure you only get one result row. Try:

UPDATE Approval SET SupervisorID = (selectTOP 1u.EmployeeID from [user] u INNER JOIN Employee e ON u.Department = e.deptid WHERE roleid = 'supervisor')|||Well see that's what I'm confused about--I would like to update all of the SupervisorID's in the table at once. There are 125 rows in the Approval Table, and about 10 different superviosrID's in the User Table. Am I going about this wrong?|||

You have to make sure to provide a condition where the table you're updating has one-to-one relationship with the sub-query value returned for each row. In your query, you have nothing that defines the relationship between the Approval table (one you're updating) and other tables in the sub-query. I can't tell you what it should look like since I have no clue on what the tables look like. There must be some column in the Approval table that links to other tables being referenced in your sub-query, so provide that relationship in your where clause.

|||

You've almost got it. Problem is your subselect is returning ALL the supervisors for every department. Your statement therefore is trying to set the Supervisor in the Approval table to every supervisor, which...You can't do.

You want to set the supervisor in the approval table to a specific supervisor. In order to do that, your subselect needs to know how it relates to the rows in the approval table. In english, that means you need something else in your WHERE clause in the subselect, something along the lines of... AND e.EmployeeID=approval.EmployeeID.

|||I see I got it now--your addition of query code worked great. Appreciate the help.|||Ok--since I'm stuck I might as well keep on asking--this is a new SQL query I have an SP already and it's the following:

SELECT
e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText,
e.EmployeeID,
e.LastName + ',' + e.FirstName AS FullName
FROM Employee e
LEFT JOIN EmployeeEval ev --this left join will return all rows from Employee, but only rows from Eval where the employee is in.
ON e.EmployeeID = ev.EmployeeID AND ev.PeriodID = @.Period
WHERE
(
(ev.Approved = 0 OR ev.Approved IS NULL) --get the ones that aren't approved
OR ev.EmployeeID IS NULL -- get the ones that haven't reviewed
)
AND (e.DeptID = @.deptID) and Status = 'Active'
GO

So now I need to incorporate my Approval Table, we're trying to get rid of being dependent on finding employees based on Dept, b/c we need some supervisors to be in charge of multiple departments.

So I need to Pull back the Info in the SELECT above and keep the EmployeeEval stuff. As well I need to remove the Dept data at the bottom of the above query. My approval table has the following columns: FacilityID, CompanyID, EmployeeID, and SupervisorID so I was thinking do something like this:

SELECT
e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText,
e.EmployeeID,
e.LastName + ',' + e.FirstName AS FullName
FROM Employee e LEFT JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID AND ev.PeriodID = 175
INNER JOIN Approval a ON e.employeeid = a.employeeid
WHERE
(
(ev.Approved = 0 OR ev.Approved IS NULL) --get the ones that aren't approved
OR ev.EmployeeID IS NULL -- get the ones that haven't reviewed
)
AND e.Status = 'Active' AND a.supervisorID = '09246'
GO

But it's returning 0 rows--I have one person in the approval table with supervisorID set to '09246'. they have no data in the employeeeval table. But in my where clause i have the ev.employeeid is null which should still allow 4 them to be brought back--let me know if this doesn't make sense.|||well I guess my query was right--I just didn't have my settings in the new table set up right--it's now showing up. thanks for all the help.

Another simple prob I cant fig out

I have a table w/ 17K line items. There are only 8.5k I want because each one is duplicated exactly twice. How do I get one of each of these line Items into a new Table?

I tried this:

Select DISTINCT * from DuplicateTable INTO NewTable

And it doesnt work. I am still getting the 17K transported over... any solutions?Since you got the full 17K items, there is something that is different about the whole row. Pick a pair of "duplicates, and set them side by side. You should be able to pick out a difference, whether it be an identity column, GUID column, or just some detail column that is not part of what should have been the primary key. Good luck.

Another row to col issue

Hi,

I've got a Problem to craete a view which works like this:

I've got this Table:

USR_ID ATNAM ATVAL
-- -
2 USR_DEPARTMENT BS1
2 USR_MARITAL_STATE married
3 USR_DEPARTMENT BS1
3 USR_MARITAL_STATE single

and I have to display it in a view like this

USR_ID USR_DEPARTMENT USR_MARITAL_STATE
- - -
2 BS1 married
3 BS1 single

maybe there will be additional "ATNAMS" in future.

Has anybode an idea how i can achieve this?

thanks in advance

Raimund

Is there a reason that you cannot use PIVOT? Are you using SQL Server 2000 or SQL Server 2005?

With SQL Server 2005 you can create a view based on a pivot; it might look something like:

Code Snippet

create view dbo.aView
as
select USR_ID,
[USR_DEPARTMENT],
[USR_MARITAL_STATE]
from source
pivot( max(ATVAL) for ATNAM
in([USR_DEPARTMENT],[USR_MARITAL_STATE])
) pv

The MAX / CASE construct can be used either with SQL 2000 or SQL 2005; that version might look something like:

Code Snippet

create view dbo.aView
as

select USR_ID,
max( case when ATNAM = 'USR_DEPARTMENT'
then ATVAL end)
as USR_MARITAL_STATE,
max( case when ATNAM = 'USR_MARITAL_STATE'
then ATVAL end)
as USR_MARITAL_STATE
from source
group by USR_ID

|||

I just didn't know about the trick with te max statement used as aggregate function.

For dynamic adaption for new "ATNAMS" I'll try it with a trigger on the according definition table.

Thanks for fast reply

Raimund

Sunday, March 11, 2012

another question

Thanks for the note Vishal,
What if each had a date. So that
create table #cartype(manufacturer varchar(500), itemnumber int, datemade date)
insert into #cartype values('Toyota',1, 4/6/2004)
insert into #cartype values('Toyota',1, 4/6/2004)
insert into #cartype values('Honda',2, 4/6/2004)
insert into #cartype values('Honda',2, 4/6/2004)
insert into #cartype values('Toyota',1, 4/7/2004)
insert into #cartype values('Honda',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
So that
insert into #cartype values('Toyota',1, 4/6/2004)
insert into #cartype values('Honda',2, 4/6/2004)
Would get deleted because there the exact same records (same number) of records are duplicated for that date.
But the records:
insert into #cartype values('Toyota',1, 4/7/2004)
insert into #cartype values('Honda',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
The GE records would stay because all of the records are not duplicated, just the GE records are so I want to keep all the records.
Thanks for any ideas!
Try query as follows:
delete a
from #cartype a join
(select manufacturer, datemade, count(*) cnt
from #cartype
group by manufacturer, datemade
having count(*) > 1) b on a.manufacturer = b.manufacturer and
a.datemade = b.datemade and b.cnt <>
(select count(*)
from #cartype x
group by manufacturer
having x.manufacturer = b.manufacturer)
Vishal Parkar
vgparkar@.yahoo.co.in
|||Here is what I have done to make it fit my query. Here are my records:
store, deliverydate, itemnumber, qty
006SS,04/15/2004,070100,018
006SS,04/15/2004,090096,018
006SS,04/15/2004,070100,018
006SS,04/15/2004,090096,018
(this should get deleted, exact same as 2 lines above)
007SS,04/15/2004,030498,020
007SS,04/15/2004,030498,020
007SS,04/15/2004,030498,020
007SS,04/15/2004,090495,020
007SS,04/15/2004,090495,020
(all lines should stay because it is not exact same.)
selext a.*, cnt
from tblItemOrder a join
(select itemnumber, quantity, store, deliverydate, count(*) cnt
from tblItemOrder
group by itemnumber, quantity, store, deliverydate
having count(*) > 1) b on a.itemnumber = b.itemnumber and
a.quantity = b.quantity and a.store = b.store and a.deliverydate = b.deliverydate and b.cnt <>
(select count(*)
from tblItemOrder x
group by store, deliverydate
having x.store = b.store and x.deliverydate = b.deliverydate)
I get all records that have duplicate lines, not just the ones with same count of duplicate records (storeno, deliverydate).
Any ideas? I have thought about it many different ways and have not come up with a solution yet. Thanks again,
|||hi ashley,
Remember SELECT and DELETE are different statements. DELETE will delete
the data from the table while with the help of SELECT statement you can
filterout the rows from the table.
See following example:
create table tt
(store varchar(50),
deliverydate datetime,
itemnumber varchar(50),
qty int)
--insert some data
insert into tt
select '006SS','04/15/2004','070100','018' union all
select '006SS','04/15/2004','090096','018' union all
select '006SS','04/15/2004','070100','018' union all
select '006SS','04/15/2004','090096','018' union all
select '007SS','04/15/2004','030498','020' union all
select '007SS','04/15/2004','030498','020' union all
select '007SS','04/15/2004','030498','020' union all
select '007SS','04/15/2004','090495','020' union all
select '007SS','04/15/2004','090495','020'
--Try this query:
select a.*
from tt a join
(select store, deliverydate, itemnumber,count(*) cnt
from tt
group by store, deliverydate, itemnumber
having count(*) > 1) b on a.itemnumber = b.itemnumber and
a.store = b.store and a.deliverydate = b.deliverydate and 1 not in
(select 1
from tt x
group by store, deliverydate, itemnumber
having x.store = b.store and x.deliverydate = b.deliverydate and
x.itemnumber <> b.itemnumber and count(*) = b.cnt)
Vishal Parkar
vgparkar@.yahoo.co.in

Tuesday, March 20, 2012

Another way than using a Cursor

Another version of SQL Permission Question

Another version of SQL Permission Question

Another version of SQL Permission Question

Another T-SQL puzzle

another trigger question

another trigger question

another trigger question

another trigger question

Monday, March 19, 2012

Another Temporary Table Q

another table corruption question

another table corruption question

another table corruption question

Another Stored Procedure Question...

Another SQL Stored Procedure Question

another sql query problem

another sql query ?

Another simple prob I cant fig out

Another row to col issue

Sunday, March 11, 2012

another question

SQL server connection

Blog Archive

About Me