logical inserted table and after triggers

for some reason my create trigger query fails because sql server cannot resolve the term inserted. It won't even parse without errors. This is what my t-sql looks like

CREATE TRIGGER UserTypeTrig ON GTData.dbo.GTUserType

AFTER Insert, Update

AS

SET NOCOUNT ON

IF EXISTS(SELECT *

FROM GTData.dbo.GTUserType G

JOIN INSERTED I

ON G.TypeID != I.TypeID AND LOWER(G.UserType) = LOWER(I.UserType);

BEGIN;

RAISERROR('cannot insert duplicate userType', 16, 1)

ROLLBACK TRANSACTION

RETURN

END;

appreciate all the help i can get.

Your syntax is not correct. You are using statement terminators in the wrong place. See the modified code:

CREATE TRIGGER UserTypeTrig ON GTData.dbo.GTUserType

AFTER Insert, Update

AS

SET NOCOUNT ON;

IF EXISTS(SELECT *

FROM GTData.dbo.GTUserType G

JOIN INSERTED I

ON G.TypeID != I.TypeID AND LOWER(G.UserType) = LOWER(I.UserType)

)

BEGIN;

RAISERROR('cannot insert duplicate userType', 16, 1);

ROLLBACK TRANSACTION;

RETURN;

END;

|||

Umachandar Jayachandran

Thanks a lot!

Logical fragmentation

Hi:

when does a table not show any Logical fragmentation?

1). Is it possible if the table has only non-clustered indexes and no clustered indexes, it wont show any logical fragmentation at all or shows less logical fragmentation?.

Also what is the difference between logical fragmentation and Physical fragmentation of a index?.Is there actually something like a physical fragmentation for a index?

Thanks

AK

An example repro T-SQL Script would be helpful. Experts please comment.|||

Logical fragmentation only applies to indexes (clustered or non-clustered). If the physical location of key orders from leaf level of an index is the same as physcial page order in the database files, then logical fragmentation is 0. You can find more information in Book Online from DBCC SHOWCONTIG in SQL Server 2000/2005 and sys.dm_db_index_physcial_stats in SQL Server 2005.

To answer your first question, if a table does not have clustered index (it is heap), then SQL Server cannot do a order scan on the data. Heap will have 0 logical fragmentation; but your query performance may not be good. Logical fragmentation is independent from different indexes/heaps. You may see logical fragmentation from non-clustered indexes.

From SQL Server point of view, it has no control of physcial fragmentation (aka file fragmentation for a database file). The physical fragmentation is from OS when allocating space for database files. It does not know if an index has physcial fragmentation.

I suggest that you read the following white paper ("Microsoft SQL Server 2000 Index Defragmentation Best Practices") to gain more ideas about logical fragmentation and file fragmentation: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Thanks

Sherry

Logical Design!

Hi all,
I have a table called students, with the following structure:
Students:
- StudentID
- InstitutionID
- AccountID
- FirstName
- LastName
- Phone
- Mobile
- Email
- StreetAddress
- Suburb
- PostCode
- StateID
I have different types of students that will be stored in the db table:
Mature age students are required to provide all info in the respective
table.
Under age students are only required to provide the following info:
- InstitutionID
- AccountID
- FirstName
- LastName
- Email
For reasons of PERFORMANCE & GOOD DATABASE LOGICAL DESIGN,
should i create two db tables to store this info:
Or is the amount of redundancy acceptible, if i use the one table to store
both types of students.
Is there another option? Maybe involving a VIEW?
Would appreciate any insight into this !!!!
Cheers,
AdamGoing with just one table, you will not only have a lot of NULLs, but you
also have other limitations, like a student can have only one email address
(the student may actually have more than one, but you're table can't hold it
).
Here's a structure you might consider, in quasi-sql
Students (
StudentID,
InstitutionID,
AccountID,
FirstName,
LastName
PRIMARY KEY (StudentID)
)
StudentPhoneNumbers (
StudentID
PhoneNumber
PhoneType --consider a bit flag for land line or mobile
PRIMARY KEY (StudentID, PhoneNumber)
FOREIGN KEY (StudentID) REFERENCES Students (StudentID)
)
StudentEmailAddresses (
StudentID
EmailAddress
PRIMARY KEY (StudentID, EmailAddress)
FOREIGN KEY (StudentID) REFERENCES Students (StudentID)
)
StudentMailingAddress (
StudentID
StreetAddress
Suburb
PostCode
StateID
PRIMARY KEY (StudentID,StreetAddress,PostCode)
FOREIGN KEY (StudentID) REFERENCES Students (StudentID)
)
--
"Adam J Knight" wrote:

> Hi all,
> I have a table called students, with the following structure:
> Students:
> - StudentID
> - InstitutionID
> - AccountID
> - FirstName
> - LastName
> - Phone
> - Mobile
> - Email
> - StreetAddress
> - Suburb
> - PostCode
> - StateID
> I have different types of students that will be stored in the db table:
> Mature age students are required to provide all info in the respective
> table.
> Under age students are only required to provide the following info:
> - InstitutionID
> - AccountID
> - FirstName
> - LastName
> - Email
> For reasons of PERFORMANCE & GOOD DATABASE LOGICAL DESIGN,
> should i create two db tables to store this info:
> Or is the amount of redundancy acceptible, if i use the one table to store
> both types of students.
> Is there another option? Maybe involving a VIEW?
> Would appreciate any insight into this !!!!
> Cheers,
> Adam
>
>|||On Sun, 15 Jan 2006 09:20:37 +1000, Adam J Knight wrote:
(snip)
>Mature age students are required to provide all info in the respective
>table.
>Under age students are only required to provide the following info:
(snip)
Hi Adam,
In such cases, you'll often want to set up one table for the information
required for all students (Students), and a second table for the
information that is only entered for a subset of the students
(MatureStudents). If there are also columns that only apply to underage
students, you'll have a third table (UnderageStudents) for that info.
Both "subtables" have StudentID as both Primary Key *AND* Foreign Key
into the main Students table.
Note that this is generic advice, not based on the specific columns
mentioned in your message. Mark already addressed those.
Hugo Kornelis, SQL Server MVP

Logic problem - a challenge if you will

This is killing, me and I think that I'm failing to see something simple here:

If I have a table with logins and datetimes. I need to output any logins that have logged in more than 3 times in any 3 hour period of time, and how may times it was done. For example:

Login table:
user1 01:00
user2 01:13
user1 02:32
user2 01:17
user1 01:12
user2 07:00
user1 04:10

I would need:
user1 2 <-- (times user 1 logged in more than 3 times in 3 hours)

Because:
01:00, 02:32, 01:12 are all within 3 hours of each other
02:32, 01:12, 04:10 are all within 3 hours of each other

Obviously I have alot more data than this, but I'm failing to grasp the logic properly. Trying to do this in a Sybase stored proc.create table #tmp (
login char(5),
log_time smalldatetime
)

insert into #tmp
select 'user1', '01:00'
union all
select 'user2', '01:13'
union all
select 'user1', '02:32'
union all
select 'user2', '01:17'
union all
select 'user1', '01:12'
union all
select 'user2', '07:00'
union all
select 'user1', '04:10'

select rs1.login, count(*) as cnt
from (
select #tmp.Login
from #tmp inner join (
select login, log_time as mintime, dateadd(hh,3,log_time) as maxtime from #tmp) rs
on #tmp.login=rs.login
where #tmp.log_time between rs.mintime and rs.maxtime
group by #tmp.login, rs.mintime, rs.maxtime
having count(#tmp.log_time)>=3) rs1
group by rs1.login

drop table #tmp|||This would be assuming a limited data set, though, correct? Suppose I do not know how many logins and times there are?|||This would be assuming a limited data set, though, correct? Why are you thinking that? Did you try the query?

Logic problem

Hi,

This might just be my brain not working after the weekend, but I'm having problems working out just how to do this.

The table:

CREATE TABLE [dbo].[tblQuiz] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[q1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q2] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q3] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q4] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q5] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q6] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q7] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q8] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[quizdate] [datetime] NULL ,
[ipaddress] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sessionid] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[score] [int] NULL
)

The field [q5] has one of three values in it: "Happy", "Unhappy" or "Neither".

I have a list of about 50 session ID's. If a record in [tblQuiz] has a [sessionid] that matches one in this list, then:
if [q5]= 'Happy' then change it to 'Unhappy'
if [q5]= 'Unhappy' then change it to 'Happy'

The only way I can think of is to change all of one matching record type to some other value, then change all the other type, then change all the original type back to the other type. Which doesn't make much sense even when I've written it down, much less when I'm doing it and have to remember where I'm at. Is there a better way?Is this an abstraction of a real world problem?
Anyway - check out CASE in BoL - it is just the ticket for you.|||Is this an abstraction of a real world problem?
Anyway - check out CASE in BoL - it is just the ticket for you.

I'm not sure what you mean by abstraction? It IS a real-world problem: "Real" as in "my boss is swearing at me". :eek:

But you're right: CASE does the job perfectly. Thankyou very much :)|||Generally it's better to use a lookup table to store your Happy/Unhappy/Neither strings and refer to them through fk ids in tblQuiz. That way you don't waste space storing the same string over and over.

If there are truly only 3 possible values, you could be using a tinyint in the q5 column instead of nvarchar(100), which is a pretty big space savings. this begins to matter pretty quickly in large databases with millions of rows.

Logic on UPDATE query

I am dealing with two tables and I am trying to take one column from a table and match the records with another table and append the data of that column.

I used an update query that looks like this:

UPDATE Acct_table Set Acct_table.Score =
(Select Score_tbl.Score from Score_tbl
Where Acct_table.Acctnb = Score_tbl.Acctnb

This process has been running for over an hour and a half and is building a large log file. I am curious to know if there is a better command that I can use in order to join the tables and then just drop the column from one to the other. Both tables are indexed on Acctnb.

Any insight would truly help.
Thanks!UPDATE A Set A.Score = S.Score
from Acct_table A
JOIN Score_tbl S
ON A.Acctnb = S.Acctnb|||Does anyone else hear an echo?|||Does anyone else hear an echo?No, not a thing. Why?

Yes, I deleted Thrasy's duplicated post

-PatP|||aaaaaaaaaa

logic of sum() with joins and using query hint

hi everybody
I have a question about the sum() function. when I join two tabeles and one
of them is the main table which I used in the from statement, sum function I
used for the joined table is giving the sum incorrectly(it is governing time
s
the other joined tabele). how can i eleminate the problem? do I have to use
query hint or someting. if so how?
the query is the basis of a fifo report. I didnt want to use a cursor and so
I wrote such a query. I solved the problem with UDFs but i want to learn the
logic and how to use query hint for sum function
id is primary key for both tables
CREATE TABLE [order] (
[id] [int] NULL ,
[date_order] [datetime] NULL ,
[product] [char] (10) ,
[quantity] [int] NULL
)
go
CREATE TABLE [distribute] (
[id] [int] NULL ,
[date_distribute] [datetime] NULL ,
[product] [char] (10),
[quantity] [int] NULL
)
--sample data
insert into distribute (id,date_distribute,product,quantity) values
(51,'2005-01-01','aaa',10)
insert into distribute (id,date_distribute,product,quantity) values
(52,'2005-01-04','aaa',13)
insert into distribute (id,date_distribute,product,quantity) values
(53,'2005-01-05','aaa',3)
insert into distribute (id,date_distribute,product,quantity) values
(54,'2005-01-06','aaa',-2)
insert into distribute (id,date_distribute,product,quantity) values
(55,'2005-01-07','aaa',8)
insert into distribute (id,date_distribute,product,quantity) values
(56,'2005-01-08','aaa',45)
insert into distribute (id,date_distribute,product,quantity) values
(57,'2005-01-10','aaa',10)
insert into [order] (id,date_order,product,quantity) values
(11,'2005-01-01','aaa',10)
insert into [order] (id,date_order,product,quantity) values
(12,'2005-01-03','aaa',20)
insert into [order] (id,date_order,product,quantity) values
(13,'2005-01-05','aaa',30)
insert into [order] (id,date_order,product,quantity) values
(14,'2005-01-08','aaa',15)
insert into [order] (id,date_order,product,quantity) values
(15,'2005-01-09','aaa',10)
--query
select o1.id,o1.date_order,o1.product, o1.quantity,d1.id as Distribute_id ,
isnull(sum(d2.quantity),0)as forobservingdist,
isnull(sum(o2.quantity),0)as forobservingord
from [order] o1
left join [order] o2 on o1.id>=o2.id and o1.product=o2.product
left join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
left join Distribute d2 on d2.id<=d1.id and d2.product=d1.product
group by o1.id, o1.date_order,o1.product, o1.quantity,d1.id
,d1.date_distribute,d1.quantity,d1.product
the query below has the same logic with the above query. I used UDFs for the
sum functions.
when you run the queries, forobservingord column must be the same as
forobservingord in the results of the query below
CREATE FUNCTION getorderdogan
(@.id int,@.product nvarchar(50))
RETURNS int
AS
BEGIN
DECLARE @.sum AS int
select @.sum = sum(o2.quantity) from [order] o2 where o2.id<=@.id and
o2.product=@.product
RETURN @.sum
END
go
CREATE FUNCTION getdistributedogan
(@.id int,@.product nvarchar(50))
RETURNS int
AS
BEGIN
DECLARE @.sum AS int
select @.sum = sum(d2.quantity) from Distribute d2 where d2.id<=@.id and
d2.product=@.product
RETURN isnull(@.sum,0)
END
select o1.id,o1.date_order,o1.product, o1.quantity,d1.id as Distribute_id ,
d1.date_distribute,
dbo.getdistributedogan(d1.id,d1.product)as forobservingdist,
dbo.getorderdogan(o1.id,o1.product)as forobservingord
from dbo.[order] o1
left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
group by o1.id, o1.date_order,o1.product, o1.quantity,d1.id
,d1.date_distribute,d1.quantity,d1.productYou can achieve the same results (with better performance) by using
subqueries instead of UDF-s:
select o1.id, o1.date_order, o1.product,o1.quantity,
d1.id as Distribute_id, d1.date_distribute, (
select sum(d2.quantity) from Distribute d2
where d2.id<=d1.id and d2.product=d1.product
) as forobservingdist, (
select sum(o2.quantity) from [order] o2
where o2.id<=o1.id and o2.product=o1.product
) as forobservingord
from dbo.[order] o1
left outer join Distribute d1
on o1.date_order<=d1.date_distribute and o1.product=d1.product
group by o1.id, o1.date_order, o1.product, o1.quantity,
d1.id, d1.date_distribute, d1.quantity, d1.product
Razvan|||tnx Razvan sure I didnt think this:))
do you have any info about using query hint works like that?
also the last form of the query is like that
select o1.id, o1.date_order, o1.product, o1.quantity, d1.id as Distribute_id
, d1.date_distribute,
case when
0>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produ
ct))
then
isnull(d1.quantity,0)+(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistribute
dogan(d1.id,d1.product))
when
d1.quantity>o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistribu
tedogan(d1.id,d1.product))
then
(o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.
id,d1.product)))
else
isnull(d1.quantity,0)
end as distributed_quantity,
case when
(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product
))>o1.quantity
then
o1.quantity
when
(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product
))<0
then
0
else
(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product
))
end as remainingorder,
dbo.getdistributedogan(d1.id,d1.product)as forobservingdist,
dbo.getorderdogan(o1.id,o1.product)as forobservingord
from dbo.[order] o1
left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
group by o1.id, o1.date_order, o1.product, o1.quantity, d1.id ,
d1.date_distribute, d1.quantity, d1.product
having
((dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produc
t))+isnull(d1.quantity,0)>0
and
o1.quantity>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.i
d,d1.product)))
or isnull(d1.quantity,0)=0
I changed the UDFs with subqueries and its working. I looked at the query
execution plan and it looks more simple with the udfs. are you sure this wil
l
work with better performance? probably you are:))
also plan of the query with subqueries shows many hash matches. Can't I use
query hint making hash matches with left join?
thanks again
select o1.id, o1.date_order, o1.product, o1.quantity, d1.id as Distribute_id
, d1.date_distribute,
case when
0>((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))
then
isnull(d1.quantity,0)+((select sum(o2.quantity) from [order] o2 where
o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
Distribute d2 where d2.id<=d1.id and d2.product=d1.product))
when
d1.quantity>o1.quantity-((select sum(o2.quantity) from [order] o2 where
o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
Distribute d2 where d2.id<=d1.id and d2.product=d1.product))
then
(o1.quantity-((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id
and o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product)))
else
isnull(d1.quantity,0)
end as distributed_quantity,
case when
((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))>o1.quantity
then
o1.quantity
when
((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))<0
then
0
else
((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))
end as remainingorder,
(select sum(d2.quantity) from Distribute d2 where d2.id<=d1.id and
d2.product=d1.product)as forobservingdist,
(select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)as forobservingord
from dbo.[order] o1
left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
group by o1.id, o1.date_order, o1.product, o1.quantity, d1.id ,
d1.date_distribute, d1.quantity, d1.product
having (((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))+isnull(d1.quantity,0)>0
and o1.quantity>((select sum(o2.quantity) from [order] o2 where
o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
Distribute d2 where d2.id<=d1.id and d2.product=d1.product)))
or isnull(d1.quantity,0)=0
"Razvan Socol" wrote:

> You can achieve the same results (with better performance) by using
> subqueries instead of UDF-s:
> select o1.id, o1.date_order, o1.product,o1.quantity,
> d1.id as Distribute_id, d1.date_distribute, (
> select sum(d2.quantity) from Distribute d2
> where d2.id<=d1.id and d2.product=d1.product
> ) as forobservingdist, (
> select sum(o2.quantity) from [order] o2
> where o2.id<=o1.id and o2.product=o1.product
> ) as forobservingord
> from dbo.[order] o1
> left outer join Distribute d1
> on o1.date_order<=d1.date_distribute and o1.product=d1.product
> group by o1.id, o1.date_order, o1.product, o1.quantity,
> d1.id, d1.date_distribute, d1.quantity, d1.product
> Razvan
>|||> do you have any info about using query hint works like that?
There are no query hints that modify the results; the hints are used
only for optimizations. For informations about hints, see the "query
hints" topic in Books Online:
http://msdn.microsoft.com/library/e..._qd_03_8upf.asp

> I looked at the query execution plan and it looks more simple with the udfs.[/colo
r]
The execution plan for a query that calls multi-statement UDF-s (scalar
or table-valued) do not contain the cost of the statements contained in
the UDF. Only in-line table-valued UDF-s are expaded in the execution
plan of the calling query.
> are you sure this will work with better performance? probably you are:))
To be sure, test it yourself using Profiler or using something like
this:
DECLARE @.t datetime
SET @.t=GETDATE()
SELECT ...
PRINT CONVERT(varchar(10),DATEDIFF(ms,@.t,GETDA
TE()))+' ms'

> also plan of the query with subqueries shows many hash matches.
> Can't I use query hint making hash matches with left join?
SQL Server can execute joins in one of three ways: nested loops, merge
or hash. These ways can be used for inner joins, as well as for outer
joins (left joins, right joins or full outer joins). The query
optimizer automatically selects the best way to execute a join (nested
loops, merge or hash), based on the number of rows and the available
indexes on the joined columns. If the optimizer used a hash join,
that's because this is probably the best way to execute the query in
this particular case. Adding a join hint will force SQL Server to use
nested loops joins or merge joins, but in most cases that would have an
inferior performance. A better idea would be to add indexes to the
columns that are used in the join (in this case: product and id) and
let the query optimizer choose the way it executes the query (the query
optimizer may realize that it's better not to use the index on the id
column, and use only the index on the product column, for example).
For more informations about the ways a join can be executed, see
"Advanced Query Tuning Concepts" topic in Books Online:
http://msdn.microsoft.com/library/e..._tun_1_8pv7.asp
Razvan|||thank you very much for the easy performance test code:))
I've tried it both query gives sometimes 10ms sometimes 20ms result
I know I had to test it with much more data. thanks again.
I've tried to add index as you but results did not changed.
also I tried left hash join which returns a warning about changing the plan
and didnt change anything in result set.
then I tried some unconscious synthax but these instinctly tries didnt
change the result:)
so I give up:))
thanks
"Razvan Socol" wrote:

> There are no query hints that modify the results; the hints are used
> only for optimizations. For informations about hints, see the "query
> hints" topic in Books Online:
> http://msdn.microsoft.com/library/e..._qd_03_8upf.asp
>
> The execution plan for a query that calls multi-statement UDF-s (scalar
> or table-valued) do not contain the cost of the statements contained in
> the UDF. Only in-line table-valued UDF-s are expaded in the execution
> plan of the calling query.
>
> To be sure, test it yourself using Profiler or using something like
> this:
> DECLARE @.t datetime
> SET @.t=GETDATE()
> SELECT ...
> PRINT CONVERT(varchar(10),DATEDIFF(ms,@.t,GETDA
TE()))+' ms'
>
> SQL Server can execute joins in one of three ways: nested loops, merge
> or hash. These ways can be used for inner joins, as well as for outer
> joins (left joins, right joins or full outer joins). The query
> optimizer automatically selects the best way to execute a join (nested
> loops, merge or hash), based on the number of rows and the available
> indexes on the joined columns. If the optimizer used a hash join,
> that's because this is probably the best way to execute the query in
> this particular case. Adding a join hint will force SQL Server to use
> nested loops joins or merge joins, but in most cases that would have an
> inferior performance. A better idea would be to add indexes to the
> columns that are used in the join (in this case: product and id) and
> let the query optimizer choose the way it executes the query (the query
> optimizer may realize that it's better not to use the index on the id
> column, and use only the index on the product column, for example).
> For more informations about the ways a join can be executed, see
> "Advanced Query Tuning Concepts" topic in Books Online:
> http://msdn.microsoft.com/library/e..._tun_1_8pv7.asp
> Razvan
>

logic of sum() with joining the same table

hi everybody
I have a question about the sum() function. when I join two tabeles and one
of them is the main table which I used in the from statement, sum function I
used for the joined table is giving the sum incorrectly(it is governing time
s
the other joined tabele). how can i eleminate the problem? do I have to use
query hint or someting. if so how?
CREATE TABLE [order] (
[id] [int] NULL ,
[date_order] [datetime] NULL ,
[product] [char] (10) ,
[quantity] [int] NULL
)
go
CREATE TABLE [distribute] (
[id] [int] NULL ,
[date_distribute] [datetime] NULL ,
[product] [char] (10),
[quantity] [int] NULL
)
select o1.id,o1.date_order,o1.product, o1.quantity,d1.id as Distribute_id ,
isnull(sum(d2.quantity),0)as forobservingdist,
isnull(sum(o2.quantity),0)as forobservingord
from [order] o1
left join [order] o2 on o1.id>=o2.id and o1.product=o2.product
left join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
left join Distribute d2 on d2.id<=d1.id and d2.product=d1.product
group by o1.id, o1.date_order,o1.product, o1.quantity,d1.id
,d1.date_distribute,d1.quantity,d1.product
the query below has the same logic with the above query. I used UDFs for the
sum functions.
when you run the queries, forobservingord column must be the same as
forobservingord in the results of the query below
CREATE FUNCTION getorderdogan
(@.id int,@.product nvarchar(50))
RETURNS int
AS
BEGIN
DECLARE @.sum AS int
select @.sum = sum(o2.quantity) from [order] o2 where o2.id<=@.id and
o2.product=@.product
RETURN @.sum
END
go
CREATE FUNCTION getdistributedogan
(@.id int,@.product nvarchar(50))
RETURNS int
AS
BEGIN
DECLARE @.sum AS int
select @.sum = sum(d2.quantity) from Distribute d2 where d2.id<=@.id and
d2.product=@.product
RETURN isnull(@.sum,0)
END
select o1.id,o1.date_order,o1.product, o1.quantity,d1.id as Distribute_id ,
d1.date_distribute,
dbo.getdistributedogan(d1.id,d1.product)as forobservingdist,
dbo.getorderdogan(o1.id,o1.product)as forobservingord
from dbo.[order] o1
left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
group by o1.id, o1.date_order,o1.product, o1.quantity,d1.id
,d1.date_distribute,d1.quantity,d1.productYou're description of the problem is not very clear. Firstly, neither of you
r
tables contains a primary key. This is critical to understanding why your su
m
function may be producing unexpected results. I'm assuming that the sum func
tion
is coming out too high because there are values being included multiple time
s.
In addition, your query formatting leaves something to be desired and makes
it
difficult to read the query.
Lastly, show us some sample data and result. It might help us understand wha
t
you are trying to achieve and why you have both tables joined twice in the
query.
Thomas|||hi thomas
the query is the basis of a fifo report. I didnt want to use a cursor and so
Iwrote such a query. I solved the problem with UDFs but i want to learn the
logic and how to use query hint for sum function
id is primary key for both tables
and here are the sample data:)
"In addition, your query formatting leaves something to be desired and makes
it
difficult to read the query." didnt understand that part. I would like to
change my query formating if you explain what is wrong.
tnx
insert into distribute (id,date_distribute,product,quantity) values
(51,'2005-01-01','aaa',10)
insert into distribute (id,date_distribute,product,quantity) values
(52,'2005-01-04','aaa',13)
insert into distribute (id,date_distribute,product,quantity) values
(53,'2005-01-05','aaa',3)
insert into distribute (id,date_distribute,product,quantity) values
(54,'2005-01-06','aaa',-2)
insert into distribute (id,date_distribute,product,quantity) values
(55,'2005-01-07','aaa',8)
insert into distribute (id,date_distribute,product,quantity) values
(56,'2005-01-08','aaa',45)
insert into distribute (id,date_distribute,product,quantity) values
(57,'2005-01-10','aaa',10)
insert into [order] (id,date_order,product,quantity) values
(11,'2005-01-01','aaa',10)
insert into [order] (id,date_order,product,quantity) values
(12,'2005-01-03','aaa',20)
insert into [order] (id,date_order,product,quantity) values
(13,'2005-01-05','aaa',30)
insert into [order] (id,date_order,product,quantity) values
(14,'2005-01-08','aaa',15)
insert into [order] (id,date_order,product,quantity) values
(15,'2005-01-09','aaa',10)
"Thomas Coleman" wrote:

> You're description of the problem is not very clear. Firstly, neither of y
our
> tables contains a primary key. This is critical to understanding why your
sum
> function may be producing unexpected results. I'm assuming that the sum fu
nction
> is coming out too high because there are values being included multiple ti
mes.
> In addition, your query formatting leaves something to be desired and make
s it
> difficult to read the query.
> Lastly, show us some sample data and result. It might help us understand w
hat
> you are trying to achieve and why you have both tables joined twice in the
> query.
>
> Thomas
>
>|||> the query is the basis of a fifo report. I didnt want to use a cursor and sod">
> Iwrote such a query. I solved the problem with UDFs but i want to learn th
e
> logic and how to use query hint for sum function
> id is primary key for both tables
> and here are the sample data:)
Ok. The only thing missing now is exactly what is it that you want? What wou
ld
the results look like?
In addition, some clarity on the meaning of the two tables would be helpful.
Is
it the case that the Order table contains products that were ordered and the
Distribute table contains products that were actually distributed? What are
the
columns "ForObservingDist" and "ForObservingOrd" supposed to denote?

> "In addition, your query formatting leaves something to be desired and mak
es
> it
> difficult to read the query." didnt understand that part. I would like to
> change my query formating if you explain what is wrong.
1. You might consider at least using Pascal casing. Pascal casing means that
the
first letter of each word in a name is capitalized. e.g. Instead of:
forobservingdist you would have: ForObservingDist. This makes it much easier
to
read.
2. Include a space on either side of an operator. So instead of: o1.id>=o2.i
d
Do this: o1.id >= o2.id
3. Include a space after a comman. So instead of o1.id,o1.date_order,o1.prod
uct.
Do this: o1.id, o1.date_order, o1.product.
4. Include some indenting in your query. I generally indent each Join clause
and
each On clause. So re-written, your query would look like:
Select o1.id, o1.date_order, o1.product, o1.quantity
, d1.id As Distribute_id
, IsNull(Sum(d2.quantity), 0) As ForObservingDist
, IsNull(Sum(o2.quantity), 0) As ForObservingOrd
From [order] o1
Left Join [order] o2
On o1.id >= o2.id
And o1.product = o2.product
Left Join Distribute d1
On o1.date_order <= d1.date_distribute
And o1.product = d1.product
Left Join Distribute d2
On d2.id <= d1.id
And d2.product = d1.product
Group By o1.id, o1.date_order, o1.product, o1.quantity
, d1.id, d1.date_distribute, d1.quantity, d1.product
Placing commas at the beginning of a line is my personal preference because
I
make fewer "missing comma" mistakes. SQL purists would want keywords like
Select, Left, Join etc. in all caps so they might write this query like so (
with
commas at the end):
SELECT o1.id, o1.date_order, o1.product, o1.quantity,
d1.id As Distribute_id,
IsNull(Sum(d2.quantity), 0) As ForObservingDist,
IsNull(Sum(o2.quantity), 0) As ForObservingOrd
FROM [order] o1
LEFT JOIN [order] o2
ON o1.id >= o2.id
AND o1.product = o2.product
LEFT JOIN Distribute d1
ON o1.date_order <= d1.date_distribute
AND o1.product = d1.product
LEFT JOIN Distribute d2
ON d2.id <= d1.id
AND d2.product = d1.product
GROUP BY o1.id, o1.date_order, o1.product, o1.quantity,
d1.id, d1.date_distribute, d1.quantity, d1.product
HTH
Thomas|||>> [query formatting] .. didnt understand that part. I would like to
change my query formating if you explain what is wrong. <<
Get a copy of my SQL PROGRAMMING STYLE. I go into painful details and
explain why you use certain formatting techniques, based on readabilty
and eye movement. Years ago when I worked for AIRMIC, I did 6 months
of full time research on this and about a year of part-time follow up.|||Hi Thomas
first of all thanks for your advices about the query style. I will do my
best:))
simply what I want is the ForObservingOrd column must be the same as in the
query with UDFs. the Query with UDFs returns the results I want. I'm sing
the solution with query hint like hash match while left joining [Orders] o2
table if possible.
I've rewrite the question and Razvan suggested to use subqueries instead of
UDFs. it is working but ly it is not what I want.
tnx again
here is the suggestion of Razvan and my reply
You can achieve the same results (with better performance) by using
subqueries instead of UDF-s:
select o1.id, o1.date_order, o1.product,o1.quantity,
d1.id as Distribute_id, d1.date_distribute, (
select sum(d2.quantity) from Distribute d2
where d2.id<=d1.id and d2.product=d1.product
) as forobservingdist, (
select sum(o2.quantity) from [order] o2
where o2.id<=o1.id and o2.product=o1.product
) as forobservingord
from dbo.[order] o1
left outer join Distribute d1
on o1.date_order<=d1.date_distribute and o1.product=d1.product
group by o1.id, o1.date_order, o1.product, o1.quantity,
d1.id, d1.date_distribute, d1.quantity, d1.product
Razvan
--
tnx Razvan sure I didnt think this:))
do you have any info about using query hint works like that?
I changed the UDFs with subqueries and its working. I looked at the query
execution plan and it looks more simple with the udfs. are you sure this wil
l
work with better performance? probably you are:))
also plan of the query with subqueries shows many hash matches. Can't I use
query hint making hash matches with left join?
thanks again
also the last form of the query is like that
select o1.id, o1.date_order, o1.product, o1.quantity, d1.id as Distribute_id
, d1.date_distribute,
case when
0>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produ
ct))
then
isnull(d1.quantity,0)+(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistribute
dogan(d1.id,d1.product))
when
d1.quantity>o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistribu
tedogan(d1.id,d1.product))
then
(o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.
id,d1.product)))
else
isnull(d1.quantity,0)
end as distributed_quantity,
case when
(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product
))>o1.quantity
then
o1.quantity
when
(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product
))<0
then
0
else
(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product
))
end as remainingorder,
dbo.getdistributedogan(d1.id,d1.product)as forobservingdist,
dbo.getorderdogan(o1.id,o1.product)as forobservingord
from dbo.[order] o1
left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
group by o1.id, o1.date_order, o1.product, o1.quantity, d1.id ,
d1.date_distribute, d1.quantity, d1.product
having
((dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produc
t))+isnull(d1.quantity,0)>0
and
o1.quantity>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.i
d,d1.product)))
or isnull(d1.quantity,0)=0
select o1.id, o1.date_order, o1.product, o1.quantity, d1.id as Distribute_id
, d1.date_distribute,
case when
0>((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))
then
isnull(d1.quantity,0)+((select sum(o2.quantity) from [order] o2 where
o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
Distribute d2 where d2.id<=d1.id and d2.product=d1.product))
when
d1.quantity>o1.quantity-((select sum(o2.quantity) from [order] o2 where
o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
Distribute d2 where d2.id<=d1.id and d2.product=d1.product))
then
(o1.quantity-((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id
and o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product)))
else
isnull(d1.quantity,0)
end as distributed_quantity,
case when
((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))>o1.quantity
then
o1.quantity
when
((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))<0
then
0
else
((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))
end as remainingorder,
(select sum(d2.quantity) from Distribute d2 where d2.id<=d1.id and
d2.product=d1.product)as forobservingdist,
(select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)as forobservingord
from dbo.[order] o1
left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
group by o1.id, o1.date_order, o1.product, o1.quantity, d1.id ,
d1.date_distribute, d1.quantity, d1.product
having (((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))+isnull(d1.quantity,0)>0
and o1.quantity>((select sum(o2.quantity) from [order] o2 where
o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
Distribute d2 where d2.id<=d1.id and d2.product=d1.product)))
or isnull(d1.quantity,0)=0
"Thomas Coleman" wrote:

> Ok. The only thing missing now is exactly what is it that you want? What w
ould
> the results look like?
> In addition, some clarity on the meaning of the two tables would be helpfu
l. Is
> it the case that the Order table contains products that were ordered and t
he
> Distribute table contains products that were actually distributed? What ar
e the
> columns "ForObservingDist" and "ForObservingOrd" supposed to denote?
>
> 1. You might consider at least using Pascal casing. Pascal casing means th
at the
> first letter of each word in a name is capitalized. e.g. Instead of:
> forobservingdist you would have: ForObservingDist. This makes it much easi
er to
> read.
> 2. Include a space on either side of an operator. So instead of: o1.id>=o2
.id
> Do this: o1.id >= o2.id
> 3. Include a space after a comman. So instead of o1.id,o1.date_order,o1.pr
oduct.
> Do this: o1.id, o1.date_order, o1.product.
> 4. Include some indenting in your query. I generally indent each Join clau
se and
> each On clause. So re-written, your query would look like:
> Select o1.id, o1.date_order, o1.product, o1.quantity
> , d1.id As Distribute_id
> , IsNull(Sum(d2.quantity), 0) As ForObservingDist
> , IsNull(Sum(o2.quantity), 0) As ForObservingOrd
> From [order] o1
> Left Join [order] o2
> On o1.id >= o2.id
> And o1.product = o2.product
> Left Join Distribute d1
> On o1.date_order <= d1.date_distribute
> And o1.product = d1.product
> Left Join Distribute d2
> On d2.id <= d1.id
> And d2.product = d1.product
> Group By o1.id, o1.date_order, o1.product, o1.quantity
> , d1.id, d1.date_distribute, d1.quantity, d1.product
> Placing commas at the beginning of a line is my personal preference becaus
e I
> make fewer "missing comma" mistakes. SQL purists would want keywords like
> Select, Left, Join etc. in all caps so they might write this query like so
(with
> commas at the end):
> SELECT o1.id, o1.date_order, o1.product, o1.quantity,
> d1.id As Distribute_id,
> IsNull(Sum(d2.quantity), 0) As ForObservingDist,
> IsNull(Sum(o2.quantity), 0) As ForObservingOrd
> FROM [order] o1
> LEFT JOIN [order] o2
> ON o1.id >= o2.id
> AND o1.product = o2.product
> LEFT JOIN Distribute d1
> ON o1.date_order <= d1.date_distribute
> AND o1.product = d1.product
> LEFT JOIN Distribute d2
> ON d2.id <= d1.id
> AND d2.product = d1.product
> GROUP BY o1.id, o1.date_order, o1.product, o1.quantity,
> d1.id, d1.date_distribute, d1.quantity, d1.product
>
> HTH
>
> Thomas
>
>|||Can you provide a sample of the input data and the expected results? Include
enough data to illustrated why Razvan's solution does not work for you.
Thomas
"POKEMON" <POKEMON@.discussions.microsoft.com> wrote in message
news:B648F5AB-2BE0-4CE8-A5E2-D8B6C43FD60B@.microsoft.com...
> Hi Thomas
> first of all thanks for your advices about the query style. I will do my
> best:))
> simply what I want is the ForObservingOrd column must be the same as in th
e
> query with UDFs. the Query with UDFs returns the results I want. I'm si
ng
> the solution with query hint like hash match while left joining [Orders] o2
> table if possible.
> I've rewrite the question and Razvan suggested to use subqueries instead o
f
> UDFs. it is working but ly it is not what I want.
> tnx again
> here is the suggestion of Razvan and my reply
> You can achieve the same results (with better performance) by using
> subqueries instead of UDF-s:
> select o1.id, o1.date_order, o1.product,o1.quantity,
> d1.id as Distribute_id, d1.date_distribute, (
> select sum(d2.quantity) from Distribute d2
> where d2.id<=d1.id and d2.product=d1.product
> ) as forobservingdist, (
> select sum(o2.quantity) from [order] o2
> where o2.id<=o1.id and o2.product=o1.product
> ) as forobservingord
> from dbo.[order] o1
> left outer join Distribute d1
> on o1.date_order<=d1.date_distribute and o1.product=d1.product
> group by o1.id, o1.date_order, o1.product, o1.quantity,
> d1.id, d1.date_distribute, d1.quantity, d1.product
> Razvan
> --
> tnx Razvan sure I didnt think this:))
> do you have any info about using query hint works like that?
> I changed the UDFs with subqueries and its working. I looked at the query
> execution plan and it looks more simple with the udfs. are you sure this w
ill
> work with better performance? probably you are:))
> also plan of the query with subqueries shows many hash matches. Can't I us
e
> query hint making hash matches with left join?
> thanks again
> also the last form of the query is like that
> select o1.id, o1.date_order, o1.product, o1.quantity, d1.id as Distribute_
id
> , d1.date_distribute,
> case when
> 0>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.pro
duct))
> then
> isnull(d1.quantity,0)+(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistribu
tedogan(d1.id,d1.product))
> when
> d1.quantity>o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistri
butedogan(d1.id,d1.product))
> then
> (o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d
1.id,d1.product)))
> else
> isnull(d1.quantity,0)
> end as distributed_quantity,
> case when
> (dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produ
ct))>o1.quantity
> then
> o1.quantity
> when
> (dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produ
ct))<0
> then
> 0
> else
> (dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produ
ct))
> end as remainingorder,
> dbo.getdistributedogan(d1.id,d1.product)as forobservingdist,
> dbo.getorderdogan(o1.id,o1.product)as forobservingord
> from dbo.[order] o1
> left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
> o1.product=d1.product
> group by o1.id, o1.date_order, o1.product, o1.quantity, d1.id ,
> d1.date_distribute, d1.quantity, d1.product
> having
> ((dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.prod
uct))+isnull(d1.quantity,0)>0
> and
> o1.quantity>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1
.id,d1.product)))
> or isnull(d1.quantity,0)=0
>
> --
> select o1.id, o1.date_order, o1.product, o1.quantity, d1.id as Distribute_
id
> , d1.date_distribute,
> case when
> 0>((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
> o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
> d2.id<=d1.id and d2.product=d1.product))
> then
> isnull(d1.quantity,0)+((select sum(o2.quantity) from [order] o2 where
> o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
> Distribute d2 where d2.id<=d1.id and d2.product=d1.product))
> when
> d1.quantity>o1.quantity-((select sum(o2.quantity) from [order] o2 where
> o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
> Distribute d2 where d2.id<=d1.id and d2.product=d1.product))
> then
> (o1.quantity-((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id
> and o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 whe
re
> d2.id<=d1.id and d2.product=d1.product)))
> else
> isnull(d1.quantity,0)
> end as distributed_quantity,
> case when
> ((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
> o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
> d2.id<=d1.id and d2.product=d1.product))>o1.quantity
> then
> o1.quantity
> when
> ((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
> o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
> d2.id<=d1.id and d2.product=d1.product))<0
> then
> 0
> else
> ((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
> o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
> d2.id<=d1.id and d2.product=d1.product))
> end as remainingorder,
> (select sum(d2.quantity) from Distribute d2 where d2.id<=d1.id and
> d2.product=d1.product)as forobservingdist,
> (select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
> o2.product=o1.product)as forobservingord
> from dbo.[order] o1
> left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
> o1.product=d1.product
> group by o1.id, o1.date_order, o1.product, o1.quantity, d1.id ,
> d1.date_distribute, d1.quantity, d1.product
> having (((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
> o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
> d2.id<=d1.id and d2.product=d1.product))+isnull(d1.quantity,0)>0
> and o1.quantity>((select sum(o2.quantity) from [order] o2 where
> o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
> Distribute d2 where d2.id<=d1.id and d2.product=d1.product)))
> or isnull(d1.quantity,0)=0
>
> "Thomas Coleman" wrote:
>|||I RESPECT
"--CELKO--" wrote:

> change my query formating if you explain what is wrong. <<
> Get a copy of my SQL PROGRAMMING STYLE. I go into painful details and
> explain why you use certain formatting techniques, based on readabilty
> and eye movement. Years ago when I worked for AIRMIC, I did 6 months
> of full time research on this and about a year of part-time follow up.
>|||sorry i think it is my mistake.
the UDF solution and subquery solution is workin as I wanted.
I just want a solution with query hint. Thats all.
"Thomas Coleman" wrote:

> Can you provide a sample of the input data and the expected results? Inclu
de
> enough data to illustrated why Razvan's solution does not work for you.
>
> Thomas
>
> "POKEMON" <POKEMON@.discussions.microsoft.com> wrote in message
> news:B648F5AB-2BE0-4CE8-A5E2-D8B6C43FD60B@.microsoft.com...
>
>

Logic for Stored Procedure(s)

I have a table containing a 'queue' of rows waiting to be processed by my application.

Is it possible to call a single stored procedure that selects a row, returns the data and then deletes the row ?

If not, what is the best logic for doing it with two stored procedures ?

The table contains a unique ID, DateTime and nVarChar colums and I could easily add a 'flag' if required.

Any suggestions appreciated.

Steve.A single stored prodedure can have many lines of code, so sure, what you are asking is possible.

I'd do it like this:

SET NOCOUNT ON
BEGIN TRANSACTION
 SELECT TOP 1 @.myID = uniqueID FROM myTable ORDER BY datetime DESC
 SELECT someFields FROM myTable WHERE uniqueID = @.myID
 DELETE FROM myTable WHERE uniqueID = @.myID
COMMIT

I'd also put some error checking code in there and perform a ROLLBACK if there's an error.

Terri|||I might create a flag on myTable indicating that the record has processed, move the tran to the client, and update the flag once the process has sucessfully completed.

If the server commits the tran before the client finishes processing and the client error you will have no "easy" way of recovering a deleted record.|||Thanks guys - that helps a lot.

Steve.

Logic for picking values from columns

Hi
Can anyone please help me ceate a logic (and SQL syntax) for this. I
have a table which has the 1st column as some Primary key and the rest
of the columns has integer values stored in them (there may be any no.
of columns with the integer values).
Now, I want to read the values in each record one by one for diff
columns starting from the 1st column with integer values, and pick the
column name of 1st non-zero integer.
Please see if someone can help with this.
Thanks
SGSG
CREATE TABLE #Test
(
rowid INT NOT NULL,
col1 INT,
col2 INT,
col3 INT
.....
)
So far everything is ok ,now I'm not sure understood you
DECLARE @.var VARCHAR(50)
SET @.var=''
SELECT @.var=@.var+ COALESCE(col1,0)+','+COALESCE(col2,0)+...... FROM #Test
SELECT @.var
--Or did you mean to get all values under one (the first one column)?
SELECT col1 FROM #Test
UNION ALL
SELECT col2 FROM #Test
UNION ALL
........
If it does not help please post desired output.
"SG" <shekhar.gupta@.gmail.com> wrote in message
news:1137998791.360110.248740@.g47g2000cwa.googlegroups.com...
> Hi
> Can anyone please help me ceate a logic (and SQL syntax) for this. I
> have a table which has the 1st column as some Primary key and the rest
> of the columns has integer values stored in them (there may be any no.
> of columns with the integer values).
> Now, I want to read the values in each record one by one for diff
> columns starting from the 1st column with integer values, and pick the
> column name of 1st non-zero integer.
> Please see if someone can help with this.
> Thanks
> SG
>|||SG wrote:
> Hi
> Can anyone please help me ceate a logic (and SQL syntax) for this. I
> have a table which has the 1st column as some Primary key and the rest
> of the columns has integer values stored in them (there may be any no.
> of columns with the integer values).
> Now, I want to read the values in each record one by one for diff
> columns starting from the 1st column with integer values, and pick the
> column name of 1st non-zero integer.
> Please see if someone can help with this.
> Thanks
> SG
"Pick the column name" I assume means you want to return the first
column name for each row? If I'm wrong then please post DDL, sample
data and show your required end result so that we don't have to guess
again.
Try:
SELECT
CASE
WHEN col1<>0 THEN 'col1'
WHEN col2<>0 THEN 'col2'
WHEN col3<>0 THEN 'col3'
.. etc
END AS col
FROM your_table ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks Uri / David,
I have got the idea how to do this. I am sorry about not posting the
table structure and the code, will take care abt this in future.
Regards,
SG

logging who did what

I have a web application accessing a SQL Server database (the ususal stuff).

I want to be able to log who did what on which table. I need to display this information on the web application. Is there an easy way of doing this, rather that making duplicates of a lot of data?

The best way I have thought of so far is making a new table with the following fields:
Table_Changed
Table_Primary_Key
Old_Field_Value
New_Field_Value
User
Date_Changed

Every time someone changes something, it is logged in this table, so that, at any time, I can display who changed what.
I have one more question. If I do do it this way, is there a way of getting the primary key value of any table? E.G. could I do something like this_table.primary_key.value ?

JagsYou may try to use "Trigger" to do this.|||Thank you for the help, but I am not actually worried about how I am going to do it (I was thinking of using triggers anyway).
I am more worried about whether the method I am using will work nicely, or will the table I create become so large that it will slow the server down too much.|||I guess how large the table gets will depend on how many updates your site will do each day. One option to get around this is to archive the data at set intervals. For instance, setup a sql server job once a month to copy all the data from the production server's logging table onto a 2nd servers logging table. This table would reside on a server that doesn't matter as much how fast it is running as the backup would happen in the middle of the night or some other time of inactivity.

Logging User

Hi,
I am trying to log the exact user (a.k.a., Domain/User) into a column in X
data table. Currently, our users login with a connection string passing
Mixed Mode Authentication with username/pwd. Again, what we want to capture
domain/user login info - "SUSER_SNAME" captures the connection string info.
Thanks!Chris,
TMK this would require your end-users to log into SQL Server using Windows
Authenticated accounts which is a best practice. This approach is more
secure and allows for better auditing.
HTH
Jerry
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:OXlICL3fGHA.3456@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I am trying to log the exact user (a.k.a., Domain/User) into a column in X
> data table. Currently, our users login with a connection string passing
> Mixed Mode Authentication with username/pwd. Again, what we want to
> capture domain/user login info - "SUSER_SNAME" captures the connection
> string info.
> Thanks!
>|||Thank you - now I have to figure out why are are then using Mixed Mode
authentication I guess.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%236D8ja3fGHA.2032@.TK2MSFTNGP02.phx.gbl...
> Chris,
> TMK this would require your end-users to log into SQL Server using Windows
> Authenticated accounts which is a best practice. This approach is more
> secure and allows for better auditing.
> HTH
> Jerry
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:OXlICL3fGHA.3456@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I am trying to log the exact user (a.k.a., Domain/User) into a column in
>> X data table. Currently, our users login with a connection string
>> passing Mixed Mode Authentication with username/pwd. Again, what we want
>> to capture domain/user login info - "SUSER_SNAME" captures the connection
>> string info.
>> Thanks!
>

Logging User

Hi,
I am trying to log the exact user (a.k.a., Domain/User) into a column in X
data table. Currently, our users login with a connection string passing
Mixed Mode Authentication with username/pwd. Again, what we want to capture
domain/user login info - "SUSER_SNAME" captures the connection string info.
Thanks!Chris,
TMK this would require your end-users to log into SQL Server using Windows
Authenticated accounts which is a best practice. This approach is more
secure and allows for better auditing.
HTH
Jerry
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:OXlICL3fGHA.3456@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I am trying to log the exact user (a.k.a., Domain/User) into a column in X
> data table. Currently, our users login with a connection string passing
> Mixed Mode Authentication with username/pwd. Again, what we want to
> capture domain/user login info - "SUSER_SNAME" captures the connection
> string info.
> Thanks!
>|||Thank you - now I have to figure out why are are then using Mixed Mode
authentication I guess.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%236D8ja3fGHA.2032@.TK2MSFTNGP02.phx.gbl...
> Chris,
> TMK this would require your end-users to log into SQL Server using Windows
> Authenticated accounts which is a best practice. This approach is more
> secure and allows for better auditing.
> HTH
> Jerry
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:OXlICL3fGHA.3456@.TK2MSFTNGP05.phx.gbl...
>

Logging Usage (logins)?

Hi everyone!

Can anyone tell me if it's possible to set up some kind of a trigger that will write out to a file or a table and log details of all database connections?
ie: user, datetime of connection, database connected to, etc.

Any help appreciated.

Cheers,
MeganHi Meagan ,

Use SQL Profiler and run a trace you will then see which user connected to which database and at which time they did so ...and more.

Regards
Burner

Logging table activity

Hi peeps,

We have a great big database (90gb) which has been populated (monopolised) by our finance team, and its full of tables that probably aren't being used at all. But know knows whats being used and what isn't or they don't have the time to go through it with me.

So I have decided to implement a procedure that logs table activity on this database, and if for example a table isn't used for a month then it will be archived off and zipped up.

I have a few ideas in my head how I can acheive this, but I am looking for some opinions and ideas from you guys?

Thanks in advancetriggers everywhere.|||Triggers won't do much for reporting

I'd say you need to use Profiler

Logging Sql error to a table

CREATE PROCEDURE GetData
AS
SELECT * FROM Bogus
When I am executing above procedure I got following error.
Server: Msg 208, Level 16, State 1, Procedure GetData, Line 3
Invalid object name 'Bogus'.
I want to Insert above sql generated error message to a table from a
procedure (Ver.SQl 2000)
How can I do it ?You can't. Such errors are batch-aborting and need to be handled by the clie
nt application. Read the
error handling articles at www.sommarskog.se.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sha" <Sha@.discussions.microsoft.com> wrote in message
news:D0E50949-4A04-4DF3-AD58-D0140D3A3B87@.microsoft.com...
> CREATE PROCEDURE GetData
> AS
> SELECT * FROM Bogus
> When I am executing above procedure I got following error.
> Server: Msg 208, Level 16, State 1, Procedure GetData, Line 3
> Invalid object name 'Bogus'.
> I want to Insert above sql generated error message to a table from a
> procedure (Ver.SQl 2000)
> How can I do it ?
>

Logging query errors

Hi,
Is there a way to log errors raised while running a query. For instance a
table contains a duplicate entry and a scheduled query notices this. Is it
then possible to get an alert in for instance the SQL log?
Greets,
FredFred
If you are on SQL Server 2005 take a look at Notification Services in the
BOL
One method is
IF EXISTS(SELECT *
FROM TableA
WHERE col = @.key
GROUP BY col
HAVING COUNT(*)>1)
BEGIN
RAISERROR ('There are duplicates in the table',16, 1)
END
ELSE
"Fred Wouters" <f.wouters@.tigra-tuning.nl> wrote in message
news:788AA79C-20C2-438B-9556-8AFF574B129B@.microsoft.com...
> Hi,
> Is there a way to log errors raised while running a query. For instance a
> table contains a duplicate entry and a scheduled query notices this. Is it
> then possible to get an alert in for instance the SQL log?
> Greets,
> Fred|||Not really without catching that error in the client code. If you want to do this at the TSQL level:
2000: You can only capture the error number using @.@.ERROR
2005: You can capture almost anything you want, using functions such as ERROR_MESSAGE(),
ERROR_NUMBER(). Do read up on TRY/CATCH if you want to go this route.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Fred Wouters" <f.wouters@.tigra-tuning.nl> wrote in message
news:788AA79C-20C2-438B-9556-8AFF574B129B@.microsoft.com...
> Hi,
> Is there a way to log errors raised while running a query. For instance a
> table contains a duplicate entry and a scheduled query notices this. Is it
> then possible to get an alert in for instance the SQL log?
> Greets,
> Fred|||I'm using SQL 2000. Would the query below also work on 2000?
"Uri Dimant" wrote:
> Fred
> If you are on SQL Server 2005 take a look at Notification Services in the
> BOL
>
> One method is
> IF EXISTS(SELECT *
> FROM TableA
> WHERE col = @.key
> GROUP BY col
> HAVING COUNT(*)>1)
> BEGIN
> RAISERROR ('There are duplicates in the table',16, 1)
> END
> ELSE
>
>
> "Fred Wouters" <f.wouters@.tigra-tuning.nl> wrote in message
> news:788AA79C-20C2-438B-9556-8AFF574B129B@.microsoft.com...
> > Hi,
> >
> > Is there a way to log errors raised while running a query. For instance a
> > table contains a duplicate entry and a scheduled query notices this. Is it
> > then possible to get an alert in for instance the SQL log?
> >
> > Greets,
> >
> > Fred
>
>|||Fred
Yes
"Fred Wouters" <f.wouters@.tigra-tuning.nl> wrote in message
news:AB5685F6-EF5D-4346-A4C0-A8D02D54DDFB@.microsoft.com...
> I'm using SQL 2000. Would the query below also work on 2000?
> "Uri Dimant" wrote:
>> Fred
>> If you are on SQL Server 2005 take a look at Notification Services in the
>> BOL
>>
>> One method is
>> IF EXISTS(SELECT *
>> FROM TableA
>> WHERE col = @.key
>> GROUP BY col
>> HAVING COUNT(*)>1)
>> BEGIN
>> RAISERROR ('There are duplicates in the table',16, 1)
>> END
>> ELSE
>>
>>
>> "Fred Wouters" <f.wouters@.tigra-tuning.nl> wrote in message
>> news:788AA79C-20C2-438B-9556-8AFF574B129B@.microsoft.com...
>> > Hi,
>> >
>> > Is there a way to log errors raised while running a query. For instance
>> > a
>> > table contains a duplicate entry and a scheduled query notices this. Is
>> > it
>> > then possible to get an alert in for instance the SQL log?
>> >
>> > Greets,
>> >
>> > Fred
>>

Logging packages info

Hi!

I want to log package info like when the package starts and ends, and write info to a sql server table. there are of course many ways to do this. I just want some opinions from you if you have some clever ways to do this.

regards geir f

Have you looked at the provided logging capabilities? Right click the control flow, select Logging. We provide many logging types, including logging to SQL server.

Logging packages info

Hi!

I want to log package info like when the package starts and ends, and write info to a sql server table. there are of course many ways to do this. I just want some opinions from you if you have some clever ways to do this.

regards geir f

Have you looked at the provided logging capabilities? Right click the control flow, select Logging. We provide many logging types, including logging to SQL server.

Logging on SQL Server 2000

I am new to SQL Server 2000, I am looking to set up some type of
loging on out test database.
I need to track what table are hit when a record is inserted via a
front end piece of software (Struxure).

Any help would be appreciated.

Daniel Kubicek
Senior Programmer Analyst - Engineering & Procurement
Grede Foundries, Inc.
414.256.9210
dkubicek@.grede.comdkubicek@.upgradesetc.com (Daniel Kubicek) wrote in message news:<e54046c4.0404070554.4097df39@.posting.google.com>...
> I am new to SQL Server 2000, I am looking to set up some type of
> loging on out test database.
> I need to track what table are hit when a record is inserted via a
> front end piece of software (Struxure).
> Any help would be appreciated.
> Daniel Kubicek
> Senior Programmer Analyst - Engineering & Procurement
> Grede Foundries, Inc.
> 414.256.9210
> dkubicek@.grede.com

You can use Profiler to view all SQL sent to the server, or triggers
if you need more functionality.

Simon