db time conversion: fields

Showing posts with label fields. Show all posts

Wednesday, March 28, 2012

query (deals with exclusions)

I have 2 tables. The first table is a master table with 3 fields: record id, list, and value. The second table is a lookup table that has the list and value in it, as well as fieldname. I need a query that will return a count of records in the master table that do not exist in the lookup table based on list and value. It seems straightforward but my brain doesn't seem to be working. I keep returning the count of records that don't match in the lookup table, instead of the master table. can anyone help? This was my code that isn't working:

select m.list, m.value, count(*)
from master m, lookup l
where m.list = l.list and
m.value <> l.value
and fieldname = 'BOC'
group by m.list, m.value

Thanks in advance.If the columns LIST and VALUE are the joining columns between the MASTER table and the LOOKUP table then try this query:

SELECT COUNT(*)
FROM MASTER m
WHERE NOT EXISTS
(
SELECT *
FROM LOOKUP l
WHERE l.LIST = m.LIST
AND l.VALUE = m.VALUE
)

Query

Hello folks

I have next tables:

t1: messages with fields fecha, hora, tamano, numero

t2: voice with fields min_id, min_user, min_ciudad

field t1.numero contains field t2.min_user. e.g numero = 0I1234585852555850234555255585023455 12.WAV and min_user = 5255585023455

So I want to build a query which can return me fecha,hora, tamano, numero for each min_user.

I was thinking on something like:

select fecha, hora, tamano from messages where numero like '%(select min_user from voice)'

Thanks in advance for your help.I know you could do this using Dynamic SQL. Personally, I would never prefer using this. But if there is no other option left for you try it.

Originally posted by jeremas
Hello folks

I have next tables:

t1: messages with fields fecha, hora, tamano, numero

t2: voice with fields min_id, min_user, min_ciudad

field t1.numero contains field t2.min_user. e.g numero = 0I1234585852555850234555255585023455 12.WAV and min_user = 5255585023455

So I want to build a query which can return me fecha,hora, tamano, numero for each min_user.

I was thinking on something like:

select fecha, hora, tamano from messages where numero like '%(select min_user from voice)'

Thanks in advance for your help.|||You know, Im not an SQL expert, I just started working with this without any serious SQL training.
So I will appreciate if you could help me with code, how it could look.
Regards,|||select fecha, hora, tamano
from messages where
numero IN (select min_user from voice)|||Hi Brett,

Thanks for your suggestion.

I ran it and returns nothing....

What it could happen?

What about stored procedures?

Regards,|||If I run just

select count(*) from messages where numero like '%5585090333%'

or select * from messages where numero like '%5585090333%'

where 5585090333 is one value of field min_user of table voice it returns all values for such condition. But when I run the same query using where numero like '%(select min_user from voice)%' it gives nothing, the same if I use IN instead LIKE
why?

Regards,|||SELECT fecha, hora, tamano
from messages a
where EXISTS
(select * from voice b WHERE '%'+a.numero+'%' LIKE '%'+b.min_user+'%')

Or

SELECT fecha, hora, tamano
from messages a
where '%'+a.numero+'%'
IN (select '%'+b.min_user+'%' from voice b)

Either way..not pretty|||Hello,

I get the attached sql errors...

Thanks|||Ups..
I get the attached sql error
Thanks|||Try:
declare @.str varchar(100)
select @.str = 'select fecha, hora, tamano from messages where numero like ''%' + min_user from voice + '%'''
print @.str

Originally posted by jeremas
Ups..
I get the attached sql error
Thanks|||Ooops ... Instead of print (@.str) it should be exec (@.str)
Originally posted by sbaru
Try:
declare @.str varchar(100)
select @.str = 'select fecha, hora, tamano from messages where numero like ''%' + min_user from voice + '%'''
print @.str|||Sorry for asking, are you with this declaring a new function or procedure?

I hope you dont mind but I'm new on SQL

Regards,|||jeramas,

surely the concatenated values in t1.numero are delimited in some logical way, such as fixed width? If not, you might as well throw up your hands in defeat right now, 'cause your problems with this are going to last the life of the application.

If they are concatenated with some sort of consistent logic, then you should develop an algorythym for parsing out the elements. Put it in a function if you can.

blindman|||The thing is that for a single record it work quite well
But using something like where numero like '%(select number_min from voice)%' returns empty row.

Regards,|||Originally posted by jeremas
where numero like '%(select number_min from voice)%'
Regards,

Man .. when you say '%(select number_min from voice)%' SQL server is not going to run the select query coz it assumes it is a string you are searching for which has '(select number_min from voice)' string in between. Hence your 0 rows.|||So can u correct the code...|||Originally posted by jeremas
So can u correct the code...

What do you think the guys have been doing prior to my post ?

Oh .. and BTW .. the error messages you posted make me think you are executing their code in EM ... use Query analyzer ...|||Thanks folks,

Now Im doing:

select r.fecha, r.hora, r.tamano, r.numero, c.number_min from messages r full join numeros c on r.numero containing '%c.number_min%' order by c.number_min;

Looks like it is worknig. Now I dont know how to send the results to a table.

Regards,|||select r.fecha, r.hora, r.tamano, r.numero, c.number_min
INTO #temp
from messages r
full join numeros c
on r.numero containing '%c.number_min%' order by c.number_min;|||While running the query the performance is very poor.

Is ther any way to speed up the process?

Regards,|||Placing a wildcard in front of a string will always cause a table scan

How many rows do you have in both tables?|||More than 500,000 in each table...

But I, not using the wildcar option...

Regards,|||What do you think the % sign is?

What's "Containing"?

Is this SQL Server?

Theres a CONTAINS, but no containing...|||Instead of %c.number% or whatever I jus use contains c.number

Regards,|||It is Interbase.

query

Imagine we have 2 tables
personal table with 3 fields:
personalid int
jobid int
oldjobid int

and table job with 2 fields :
jobid int
jobname varchar

now I want write a query with this result :
personalid,jobname,oldjobname
which jobname means the name in job table related with jobid in personal table
and oldjobname means the name in job table related with oldjobid in personal table
please help me

SELECT
personalid,
jobname,
(SELECT jobname FROM job INNER JOIN personal on personal.oldjobid=job.jobid) AS Oldjobname
FROM
personal
INNER JOIN job ON personal.jobid = job.jobid
|||

tanks for you answer
but when I run this query this error occure:
"subquery returned more than 1 value. This is not permitted when the subquery follows =,!=,<,<=,<,<= or when the subquery is used as an expression"

|||Try this:
SELECT personalid, j1.jobName, j2.jobName as oldJobName
FROM personal p
INNER JOIN job j1 ON p.jobID = j1.jobID
INNER JOIN job j2 ON p.oldJobID = j2.jobID

Nick|||

tank you

exactly true

Monday, March 26, 2012

Query

Hi,
I have an Audit table that has the following fields and values
FieldName ChangedValue
FirstName Scott
LastName Hello
how am i able to create a query to show the above in one line
FirstName LastName
Scott Hello
Thanks
EdHi
Unless you can identify how to pair your records then you may get spurious
results
If say there was an identifier column (called id) you can link using that
SELECT a.ChangedValue as [Firstname], b.ChangedValue as [LastName]
FROM dbo.MyAudit A
JOIN dbo.MyAudit B on A.id = B.Id
WHERE a.FieldName = 'FirstName'
b.FieldName = 'LastName'
This previous post may also help:
http://tinyurl.com/6rhsj
John
"Ed" wrote:

> Hi,
> I have an Audit table that has the following fields and values
> FieldName ChangedValue
> FirstName Scott
> LastName Hello
> how am i able to create a query to show the above in one line
> FirstName LastName
> Scott Hello
> Thanks
> Ed
>
>

Friday, March 23, 2012

query

Hi
a) I Have table(tmarcs) with fields
id, marc_id,ind1, indeks, teks
Lets say i have values in this table
1,245,4,'The Sun', 'is shining'
1,245,0,'Sun',''
b) on ind1 field is length of article(prefix)
c) user wants to search books by title and enters only Sun
Query must return both titles, beacause The_ must be ignored on search
My query looks something like this
select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
'Sun'
but this query is veeeeery slow.(of course query is more complex but when to
remove this "replicate", query is very fast)
Any ideas how to build better query ;)?
Regards;
MexHi
I'm not sure that you gain here performance try how does it work?
create table #t
(
c varchar(50)
)
insert into #t values ('The Sun')
insert into #t values ('The_ Sun')
insert into #t values ('The_Sun')
insert into #t values ('The_Sun_')
insert into #t values ('The Moon')
declare @.d varchar(50)
set @.d='Sun'
select * from #t where c not LIKE '%[_]%' and c like '%'+@.d+'%'
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
> Hi
> a) I Have table(tmarcs) with fields
> id, marc_id,ind1, indeks, teks
>
> Lets say i have values in this table
> 1,245,4,'The Sun', 'is shining'
> 1,245,0,'Sun',''
> b) on ind1 field is length of article(prefix)
> c) user wants to search books by title and enters only Sun
> Query must return both titles, beacause The_ must be ignored on search
> My query looks something like this
> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
> 'Sun'
> but this query is veeeeery slow.(of course query is more complex but when
> to remove this "replicate", query is very fast)
> Any ideas how to build better query ;)?
>
> Regards;
> Mex
>|||Meelis,
Your prefix is precooking how big a part of the title to strip off, but what
if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or 4 for
"Friend"?
For the data as you show it, you could use
... WHERE indeks IS LIKE '%Sun%'
However, just as your replicate code will not use an index on indeks, this
still will not use an index since the prefix is wild.
This need might be better met by fulltext indexing of the indeks column.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
> Hi
> a) I Have table(tmarcs) with fields
> id, marc_id,ind1, indeks, teks
>
> Lets say i have values in this table
> 1,245,4,'The Sun', 'is shining'
> 1,245,0,'Sun',''
> b) on ind1 field is length of article(prefix)
> c) user wants to search books by title and enters only Sun
> Query must return both titles, beacause The_ must be ignored on search
> My query looks something like this
> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
> 'Sun'
> but this query is veeeeery slow.(of course query is more complex but when
> to remove this "replicate", query is very fast)
> Any ideas how to build better query ;)?
>
> Regards;
> Mex
>|||The ind1 is ment for aritcles in the bginning of title.
for english The, German Der,Das,Die aso.(length from 0 to 9)
The Sun (in english)
Die Sonne(in german)
in both cases ind1=4
if user enters for search Sonne, query must return also Die Sonne
because ind1=4 and first 4 chars are ignored
Mex
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23uJFd%23WZHHA.4368@.TK2MSFTNGP06.phx.gbl...
> Meelis,
> Your prefix is precooking how big a part of the title to strip off, but
> what if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or
> 4 for "Friend"?
> For the data as you show it, you could use
> ... WHERE indeks IS LIKE '%Sun%'
> However, just as your replicate code will not use an index on indeks, this
> still will not use an index since the prefix is wild.
> This need might be better met by fulltext indexing of the indeks column.
> RLF
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> a) I Have table(tmarcs) with fields
>> id, marc_id,ind1, indeks, teks
>>
>> Lets say i have values in this table
>> 1,245,4,'The Sun', 'is shining'
>> 1,245,0,'Sun',''
>> b) on ind1 field is length of article(prefix)
>> c) user wants to search books by title and enters only Sun
>> Query must return both titles, beacause The_ must be ignored on search
>> My query looks something like this
>> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
>> 'Sun'
>> but this query is veeeeery slow.(of course query is more complex but when
>> to remove this "replicate", query is very fast)
>> Any ideas how to build better query ;)?
>>
>> Regards;
>> Mex
>>
>|||its much slower, because a '%'+@.d+'%'
Ill show you my original query :)
select top 500 t.id, 0, t.indeks, t.yhik_id, t.pealkiri, t.autor, t.aasta,
t.muud ,(select id from kohaviidad where teavik_id=t.id and kogu_id=37) ,
count(distinct e.id) from (TEAVIKUD t inner join eksemplarid e on
(e.teavik_id=t.id and e.asukoht_id not in(10,11) )) inner join (select
teavik_id from tmarcid where (marc_id in (210,222,240,242,245,246,740)) and
indeks like N'' + (select replicate('_',char(ind2))) +N'tuba number 10%')
tm1 on tm1.teavik_id=t.id where kustut=0 group by t.id, t.indeks,
t.yhik_id, t.pealkiri, t.autor, t.aasta, t.muud order by 5,6,7,8
Mex
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23W7U85WZHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Hi
> I'm not sure that you gain here performance try how does it work?
>
> create table #t
> (
> c varchar(50)
> )
> insert into #t values ('The Sun')
> insert into #t values ('The_ Sun')
> insert into #t values ('The_Sun')
> insert into #t values ('The_Sun_')
> insert into #t values ('The Moon')
>
> declare @.d varchar(50)
> set @.d='Sun'
> select * from #t where c not LIKE '%[_]%' and c like '%'+@.d+'%'
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> a) I Have table(tmarcs) with fields
>> id, marc_id,ind1, indeks, teks
>>
>> Lets say i have values in this table
>> 1,245,4,'The Sun', 'is shining'
>> 1,245,0,'Sun',''
>> b) on ind1 field is length of article(prefix)
>> c) user wants to search books by title and enters only Sun
>> Query must return both titles, beacause The_ must be ignored on search
>> My query looks something like this
>> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
>> 'Sun'
>> but this query is veeeeery slow.(of course query is more complex but when
>> to remove this "replicate", query is very fast)
>> Any ideas how to build better query ;)?
>>
>> Regards;
>> Mex
>>
>|||Meelis,
One common approach (in the US) for removing the article from the sort
criteria is to reform the title as:
1,245,4,'Sun, The', 'is shining'
1,245,0,'Sun',''
This means that the overhead is at update time when the code takes "The Sun"
and turns it into "Sun, The". Of course, if you want to display the title
as originally typed, the SELECT will need to turn it back around. In this
case, ind1 would be the length of the suffix.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:OCIcXEXZHHA.348@.TK2MSFTNGP02.phx.gbl...
> The ind1 is ment for aritcles in the bginning of title.
> for english The, German Der,Das,Die aso.(length from 0 to 9)
> The Sun (in english)
> Die Sonne(in german)
> in both cases ind1=4
> if user enters for search Sonne, query must return also Die Sonne
> because ind1=4 and first 4 chars are ignored
> Mex
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23uJFd%23WZHHA.4368@.TK2MSFTNGP06.phx.gbl...
>> Meelis,
>> Your prefix is precooking how big a part of the title to strip off, but
>> what if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or
>> 4 for "Friend"?
>> For the data as you show it, you could use
>> ... WHERE indeks IS LIKE '%Sun%'
>> However, just as your replicate code will not use an index on indeks,
>> this still will not use an index since the prefix is wild.
>> This need might be better met by fulltext indexing of the indeks column.
>> RLF
>> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
>> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> a) I Have table(tmarcs) with fields
>> id, marc_id,ind1, indeks, teks
>>
>> Lets say i have values in this table
>> 1,245,4,'The Sun', 'is shining'
>> 1,245,0,'Sun',''
>> b) on ind1 field is length of article(prefix)
>> c) user wants to search books by title and enters only Sun
>> Query must return both titles, beacause The_ must be ignored on search
>> My query looks something like this
>> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
>> 'Sun'
>> but this query is veeeeery slow.(of course query is more complex but
>> when to remove this "replicate", query is very fast)
>> Any ideas how to build better query ;)?
>>
>> Regards;
>> Mex
>>
>>
>

Saturday, February 25, 2012

q; how to write this trigger

I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
also have T2 in Database2 which has the same fields and some extra fields.
Now based on the T1.Type=Insert or Update, I need to perform either update
or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>’Done’. If thi
s is
a success, I need to set T1.Status=’Done’ for the updated records. So th
is
should only be updated records.
T1 is a frequently inserted table, so there might be more than one record
coming there at the same time. How should I write my insert trigger and
correctly set T1.Status=’Done’, any example would be greatly appreciated
.On Wed, 1 Nov 2006 06:09:02 -0800, JIM.H. wrote:

>I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
>also have T2 in Database2 which has the same fields and some extra fields.
>Now based on the T1.Type=Insert or Update, I need to perform either update
>or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>Done. If this i
s
>a success, I need to set T1.Status=Done for the updated records. So this
>should only be updated records.
>T1 is a frequently inserted table, so there might be more than one record
>coming there at the same time. How should I write my insert trigger and
>correctly set T1.Status=Done, any example would be greatly appreciated.
Hi Jim,
CREATE TRIGGER JimsTrigger
ON T1 AFTER INSERT
AS
IF @.@.ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
INSERT INTO T2 (ID, Type, Status, ...)
SELECT ID, Type, Status, ...
FROM inserted
WHERE Type = 'Insert'
AND Status <> 'Done'
UPDATE T2
SET Type = i.Type,
Status = i.Status,
..
FROM inserted AS i
JOIN T2 ON T2.ID = i.ID
WHERE i.Type = 'Update'
AND i.Status <> 'Done'
UPDATE T1
SET Status = 'Done'
WHERE EXISTS
(SELECT *
FROM inserted AS i
WHERE i.Type IN ('Update', 'Insert')
AND i.Status <> 'Done'
AND i.ID = T1.ID)
go
Untested, and you'll probably want to add error handling.
Hugo Kornelis, SQL Server MVP

q; how to write this trigger

I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
also have T2 in Database2 which has the same fields and some extra fields.
Now based on the T1.Type=Insert or Update, I need to perform either update
or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>â'Doneâ'. If this is
a success, I need to set T1.Status=â'Doneâ' for the updated records. So this
should only be updated records.
T1 is a frequently inserted table, so there might be more than one record
coming there at the same time. How should I write my insert trigger and
correctly set T1.Status=â'Doneâ', any example would be greatly appreciated.On Wed, 1 Nov 2006 06:09:02 -0800, JIM.H. wrote:
>I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
>also have T2 in Database2 which has the same fields and some extra fields.
>Now based on the T1.Type=Insert or Update, I need to perform either update
>or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>?Done?. If this is
>a success, I need to set T1.Status=?Done? for the updated records. So this
>should only be updated records.
>T1 is a frequently inserted table, so there might be more than one record
>coming there at the same time. How should I write my insert trigger and
>correctly set T1.Status=?Done?, any example would be greatly appreciated.
Hi Jim,
CREATE TRIGGER JimsTrigger
ON T1 AFTER INSERT
AS
IF @.@.ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
INSERT INTO T2 (ID, Type, Status, ...)
SELECT ID, Type, Status, ...
FROM inserted
WHERE Type = 'Insert'
AND Status <> 'Done'
UPDATE T2
SET Type = i.Type,
Status = i.Status,
...
FROM inserted AS i
JOIN T2 ON T2.ID = i.ID
WHERE i.Type = 'Update'
AND i.Status <> 'Done'
UPDATE T1
SET Status = 'Done'
WHERE EXISTS
(SELECT *
FROM inserted AS i
WHERE i.Type IN ('Update', 'Insert')
AND i.Status <> 'Done'
AND i.ID = T1.ID)
go
Untested, and you'll probably want to add error handling.
--
Hugo Kornelis, SQL Server MVP

q; how to write this

Hello,
I have MyTable with ID, IsYesNo fields
ID is duplicated so I need perform select on MyTable with the following
conditions:
1. Select all the ID distinct where IsYesNo=â'Yesâ' first
2. Then select all the ID distinct where IsYesNo=â'Noâ' if ID is not in the
first selection
Combine these two and return the resultTry:
select distinct
ID
, IsYesNo
from
MyTable
where
IsYesNo = 'Yes'
union all
select distinct
ID
, IsYesNo
from
MyTable o
where
IsYesNo = 'No'
and not exists
(
select distinct
*
from
MyTable i
where
i.IsYesNo = 'Yes'
and
i.ID = o.ID
)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:7E592B5E-81BB-420A-BDBE-9ED0C67689F4@.microsoft.com...
Hello,
I have MyTable with ID, IsYesNo fields
ID is duplicated so I need perform select on MyTable with the following
conditions:
1. Select all the ID distinct where IsYesNo=â'Yesâ' first
2. Then select all the ID distinct where IsYesNo=â'Noâ' if ID is not in the
first selection
Combine these two and return the result|||Looks like homework so ignoring the detail of the question but going straight
to the result
select ID, IsYesNo = max(IsYesNo)
from MyTable
Group by ID
"JIM.H." wrote:
> Hello,
> I have MyTable with ID, IsYesNo fields
> ID is duplicated so I need perform select on MyTable with the following
> conditions:
> 1. Select all the ID distinct where IsYesNo=â'Yesâ' first
> 2. Then select all the ID distinct where IsYesNo=â'Noâ' if ID is not in the
> first selection
> Combine these two and return the result
>

q; a trigger code

I have table T1 and I am trying to extract some date from T1 table based on
inserts, updates and deletes. The destination table T2 has three fields F1,
F2, F3, so I need something like
Insert into T2 (F1, F2, F3)
Select (F1, F2,Type)
From T1
Type should be defined based on Insert, Update, or Delete. This will be my
first trigger, Can anyone write this trigger for me?I would NEVER write any code that had table names like T1 or T2, and columns
named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
that you can post?
Give us more to work with, and you may get some good assistance here.
For example, the the code snipplet, is TYPE a column name, a literal, a
varable? Where did it come from?
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>I have table T1 and I am trying to extract some date from T1 table based on
> inserts, updates and deletes. The destination table T2 has three fields
> F1,
> F2, F3, so I need something like
> Insert into T2 (F1, F2, F3)
> Select (F1, F2,Type)
> From T1
> Type should be defined based on Insert, Update, or Delete. This will be my
> first trigger, Can anyone write this trigger for me?
>
>|||Hi Arnie,
That was just to simplify the case. I do not have actual table definitions
yet. Type is what I am trying to determine in the trigger. This will be
INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
"Arnie Rowland" wrote:
> I would NEVER write any code that had table names like T1 or T2, and columns
> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
> that you can post?
> Give us more to work with, and you may get some good assistance here.
> For example, the the code snipplet, is TYPE a column name, a literal, a
> varable? Where did it come from?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
> >I have table T1 and I am trying to extract some date from T1 table based on
> > inserts, updates and deletes. The destination table T2 has three fields
> > F1,
> > F2, F3, so I need something like
> >
> > Insert into T2 (F1, F2, F3)
> > Select (F1, F2,Type)
> > From T1
> >
> > Type should be defined based on Insert, Update, or Delete. This will be my
> > first trigger, Can anyone write this trigger for me?
> >
> >
> >
> >
>
>|||JIM.H. wrote:
> I have table T1 and I am trying to extract some date from T1 table based on
> inserts, updates and deletes. The destination table T2 has three fields F1,
> F2, F3, so I need something like
> Insert into T2 (F1, F2, F3)
> Select (F1, F2,Type)
> From T1
> Type should be defined based on Insert, Update, or Delete. This will be my
> first trigger, Can anyone write this trigger for me?
>
>
I'm guessing this is some sort of audit mechanism? If so, lots of info
available online regarding auditing, my friend Google found this one for me:
http://www.nigelrivett.net/AuditTrailTrigger.html
Incidentally, this type of activity, if done poorly, can cause BLOCKING
on updates and inserts. Just tossing that out there, since you've been
complaining all week about deadlocks.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, inside the Trigger you will be able to determine if it is executing
because of INSERT, UPDATE, DELETE.
If you are exploring some form of auditing, the archive table 'should' have
additional columns for the user and current date/time. Those are also
available internal to the Trigger.
Let us know how to help you when you're closer to the need.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
> Hi Arnie,
> That was just to simplify the case. I do not have actual table definitions
> yet. Type is what I am trying to determine in the trigger. This will be
> INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
>
> "Arnie Rowland" wrote:
>> I would NEVER write any code that had table names like T1 or T2, and
>> columns
>> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
>> that you can post?
>> Give us more to work with, and you may get some good assistance here.
>> For example, the the code snipplet, is TYPE a column name, a literal, a
>> varable? Where did it come from?
>> --
>> Arnie Rowland*
>> "To be successful, your heart must accompany your knowledge."
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>> >I have table T1 and I am trying to extract some date from T1 table based
>> >on
>> > inserts, updates and deletes. The destination table T2 has three fields
>> > F1,
>> > F2, F3, so I need something like
>> >
>> > Insert into T2 (F1, F2, F3)
>> > Select (F1, F2,Type)
>> > From T1
>> >
>> > Type should be defined based on Insert, Update, or Delete. This will be
>> > my
>> > first trigger, Can anyone write this trigger for me?
>> >
>> >
>> >
>> >
>>|||Thansk Arnie. Does this do update and insert safely? how can I do deleted?
CREATE TRIGGER trMyTrigger ON T1
FOR INSERT, UPDATE
AS
if exists (select * from inserted)
Insert Into T2(ID, Name,TrType)
Select ID, Name,'Insert'
From inserted
Where Name='TestData'
else
Update t
Set t.Name=i.Name, t.TrType='Update'
From inserted i INNER JOIN T2 t on i.ID=t.ID
GO
"Arnie Rowland" wrote:
> Yes, inside the Trigger you will be able to determine if it is executing
> because of INSERT, UPDATE, DELETE.
> If you are exploring some form of auditing, the archive table 'should' have
> additional columns for the user and current date/time. Those are also
> available internal to the Trigger.
> Let us know how to help you when you're closer to the need.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
> > Hi Arnie,
> >
> > That was just to simplify the case. I do not have actual table definitions
> > yet. Type is what I am trying to determine in the trigger. This will be
> > INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
> >
> >
> >
> > "Arnie Rowland" wrote:
> >
> >> I would NEVER write any code that had table names like T1 or T2, and
> >> columns
> >> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
> >> that you can post?
> >>
> >> Give us more to work with, and you may get some good assistance here.
> >>
> >> For example, the the code snipplet, is TYPE a column name, a literal, a
> >> varable? Where did it come from?
> >>
> >> --
> >> Arnie Rowland*
> >> "To be successful, your heart must accompany your knowledge."
> >>
> >>
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
> >> >I have table T1 and I am trying to extract some date from T1 table based
> >> >on
> >> > inserts, updates and deletes. The destination table T2 has three fields
> >> > F1,
> >> > F2, F3, so I need something like
> >> >
> >> > Insert into T2 (F1, F2, F3)
> >> > Select (F1, F2,Type)
> >> > From T1
> >> >
> >> > Type should be defined based on Insert, Update, or Delete. This will be
> >> > my
> >> > first trigger, Can anyone write this trigger for me?
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>|||Triggers have access to 2 virtual tables, [inserted] and [deleted]. They are
identical in schema to the action table.
If there is an INSERT, [inserted] will have row(s) and [deleted] will be
empty.
If there is an UPDATE, both will have rows,
[deleted] has the state of the data before the UPDATE, and
[inserted] has the state of data after the UPDATE
If there is a DELETE, [deleted] will have row(s) and [inserted] will be
empty.
So you check both tables to determine what action fired the Trigger.
You probably should refer to Books on Line for additional information.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:EF507C01-C12B-439B-B9A9-856113703E38@.microsoft.com...
> Thansk Arnie. Does this do update and insert safely? how can I do deleted?
> CREATE TRIGGER trMyTrigger ON T1
> FOR INSERT, UPDATE
> AS
> if exists (select * from inserted)
> Insert Into T2(ID, Name,TrType)
> Select ID, Name,'Insert'
> From inserted
> Where Name='TestData'
> else
> Update t
> Set t.Name=i.Name, t.TrType='Update'
> From inserted i INNER JOIN T2 t on i.ID=t.ID
> GO
>
> "Arnie Rowland" wrote:
>> Yes, inside the Trigger you will be able to determine if it is executing
>> because of INSERT, UPDATE, DELETE.
>> If you are exploring some form of auditing, the archive table 'should'
>> have
>> additional columns for the user and current date/time. Those are also
>> available internal to the Trigger.
>> Let us know how to help you when you're closer to the need.
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
>> > Hi Arnie,
>> >
>> > That was just to simplify the case. I do not have actual table
>> > definitions
>> > yet. Type is what I am trying to determine in the trigger. This will be
>> > INSER,UPDATE,DELETE trigger, can I somehow figure it out in the
>> > trigger?
>> >
>> >
>> >
>> > "Arnie Rowland" wrote:
>> >
>> >> I would NEVER write any code that had table names like T1 or T2, and
>> >> columns
>> >> named F1, F2, F3. Is this real (and I hope not), or do you have table
>> >> DDL
>> >> that you can post?
>> >>
>> >> Give us more to work with, and you may get some good assistance here.
>> >>
>> >> For example, the the code snipplet, is TYPE a column name, a literal,
>> >> a
>> >> varable? Where did it come from?
>> >>
>> >> --
>> >> Arnie Rowland*
>> >> "To be successful, your heart must accompany your knowledge."
>> >>
>> >>
>> >>
>> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> >> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>> >> >I have table T1 and I am trying to extract some date from T1 table
>> >> >based
>> >> >on
>> >> > inserts, updates and deletes. The destination table T2 has three
>> >> > fields
>> >> > F1,
>> >> > F2, F3, so I need something like
>> >> >
>> >> > Insert into T2 (F1, F2, F3)
>> >> > Select (F1, F2,Type)
>> >> > From T1
>> >> >
>> >> > Type should be defined based on Insert, Update, or Delete. This will
>> >> > be
>> >> > my
>> >> > first trigger, Can anyone write this trigger for me?
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||Thanks Tracy, this helps a lot.
"Tracy McKibben" wrote:
> JIM.H. wrote:
> > I have table T1 and I am trying to extract some date from T1 table based on
> > inserts, updates and deletes. The destination table T2 has three fields F1,
> > F2, F3, so I need something like
> >
> > Insert into T2 (F1, F2, F3)
> > Select (F1, F2,Type)
> > From T1
> >
> > Type should be defined based on Insert, Update, or Delete. This will be my
> > first trigger, Can anyone write this trigger for me?
> >
> >
> >
> >
> I'm guessing this is some sort of audit mechanism? If so, lots of info
> available online regarding auditing, my friend Google found this one for me:
> http://www.nigelrivett.net/AuditTrailTrigger.html
> Incidentally, this type of activity, if done poorly, can cause BLOCKING
> on updates and inserts. Just tossing that out there, since you've been
> complaining all week about deadlocks.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

q; a trigger code

Q: write this query

Hello,
I have table T1 and F1, F2, F3, F4 and F5, and a Count field
Now I need to write a query that groups by fields and returns sum of count
for each field such as
F1, Sum(Count)ForF1,F2, Sum(Count)ForF2, …F5, Sum(Count)ForF5
How can I do this?On Wed, 19 Apr 2006 08:15:03 -0700, JIM.H. wrote:

>Hello,
>I have table T1 and F1, F2, F3, F4 and F5, and a Count field
>Now I need to write a query that groups by fields and returns sum of count
>for each field such as
>F1, Sum(Count)ForF1,F2, Sum(Count)ForF2, F5, Sum(Count)ForF5
>How can I do this?
>
Hi Jim,
It's not clear what you need exactly. Can you post the table structure
(as CREATE TABLE statement, including constraints, properties and
indexes), some rows of sample data (as INSERT statements) and expected
results?
Hugo Kornelis, SQL Server MVP

Q: write this query

Hello,
I have table T1 and F1, F2, F3, F4 and F5, and a Count field
Now I need to write a query that groups by fields and returns sum of count
for each field such as
F1, Sum(Count)ForF1,F2, Sum(Count)ForF2, â?¦F5, Sum(Count)ForF5
How can I do this?On Wed, 19 Apr 2006 08:15:03 -0700, JIM.H. wrote:
>Hello,
>I have table T1 and F1, F2, F3, F4 and F5, and a Count field
>Now I need to write a query that groups by fields and returns sum of count
>for each field such as
>F1, Sum(Count)ForF1,F2, Sum(Count)ForF2, ?F5, Sum(Count)ForF5
>How can I do this?
>
Hi Jim,
It's not clear what you need exactly. Can you post the table structure
(as CREATE TABLE statement, including constraints, properties and
indexes), some rows of sample data (as INSERT statements) and expected
results?
--
Hugo Kornelis, SQL Server MVP

Monday, February 20, 2012

Q: Trigger - reference to fields in "inserted" ?

Hi all,

I have a ranking system where I wish to update the ranking every time a result is reported. Performance is no issue what-so-ever. More specifically, two players are to devide their points after each series of games to reflect the fraction of over-all games that each player have won.

I've written the trigger below, but Visual Web Developer 2005 Express (SQL Server 2005 Express) complains about the references to the 'inserted'-table.

I find it very difficult to transform the code below to something that looks like the examples found in documentation.

Could someone get me started in the right direction?

Thanks in advance,

Anders

create trigger result_insertonresult

after insert as

begin

declare@.won1as int

declare@.won2as int

declare@.oldRank1as float

declare@.oldRank2as float

declare@.oldranksumas float

select@.won1 =sum(wongames1)fromresultwhereplayer1 = inserted.player1andplayer2=inserted.player2

select@.won2 =sum(wongames2)fromresultwhereplayer1 = inserted.player1andplayer2=inserted.player2

select@.oldrank1 = RankfromRankingInfowherememberid = inserted.playerid1

select@.oldrank2 = RankfromRankingInfowherememberid = inserted.playerid2

set@.oldranksum = @.oldrank1 + @.oldrank2

updaterankingInfosetRank = @.won1 / ( @.won1+@.won2) * @.oldranksumwherememberid = inserted.player1

updaterankingInfosetRank = @.won2 / ( @.won1+@.won2) * @.oldranksumwherememberid = inserted.player2

end

Hello Anders,

tha fact is theinserted table is but a table, so you need JOIN-ing to it as you would for any standard table...

Hope this helps. -LV

Wednesday, March 28, 2012

query (deals with exclusions)

Query

query

Monday, March 26, 2012

Query

Friday, March 23, 2012

query

Saturday, February 25, 2012

q; how to write this trigger

q; how to write this trigger

q; how to write this

q; a trigger code

q; a trigger code

Q: write this query

Q: write this query

Monday, February 20, 2012

Q: Trigger - reference to fields in "inserted" ?

db time conversion

Blog Archive

About Me