Query Help

Hello all, question on a query
How do I turn this into an update query?
SELECT MTE.dbo.GL10110.YEAR1, MTE.dbo.GL10110.PERIODID,
tblEdit.Acct, SUM(MTE.dbo.GL10110.DEBITAMT) - SUM(MTE.dbo.GL10110.CRDTAMNT)
AS Net
FROM tblEdit INNER JOIN
MTE.dbo.GL10110 ON tblEdit.Period =
MTE.dbo.GL10110.PERIODID AND tblEdit.Yr = MTE.dbo.GL10110.YEAR1 INNER JOIN
MTE.dbo.GL00100 ON MTE.dbo.GL10110.ACTINDX =
MTE.dbo.GL00100.ACTINDX AND tblEdit.Acct = MTE.dbo.GL00100.ACTNUMBR_1
GROUP BY MTE.dbo.GL10110.YEAR1, MTE.dbo.GL10110.PERIODID, tblEdit.Acct
ORDER BY tblEdit.Acct
I want it to update the tblEdit table. Just the DeptNet Column. just
as to what appliesHello, Brian
Because you provided such a brief description of the problem, I am only
guessing that you want one of the following (equivalent) queries:
UPDATE tblEdit SET DeptNet=(
SELECT SUM(A.DEBITAMT) - SUM(A.CRDTAMNT)
FROM MTE.dbo.GL10110 A
INNER JOIN MTE.dbo.GL00100 B
ON A.ACTINDX = B.ACTINDX
WHERE tblEdit.Period = A.PERIODID
AND tblEdit.Yr = A.YEAR1
AND tblEdit.Acct = B.ACTNUMBR_1
)
WHERE EXISTS (
SELECT * FROM MTE.dbo.GL10110 A
INNER JOIN MTE.dbo.GL00100 B
ON A.ACTINDX = B.ACTINDX
WHERE tblEdit.Period = A.PERIODID
AND tblEdit.Yr = A.YEAR1
AND tblEdit.Acct = B.ACTNUMBR_1
)
UPDATE tblEdit SET DeptNet=Net
FROM tblEdit INNER JOIN (
SELECT A.YEAR1, A.PERIODID, B.ACTNUMBR_1,
SUM(A.DEBITAMT) - SUM(A.CRDTAMNT) AS Net
FROM MTE.dbo.GL10110 A
INNER JOIN MTE.dbo.GL00100 B
ON A.ACTINDX = B.ACTINDX
GROUP BY A.YEAR1, A.PERIODID, B.ACTNUMBR_1
) x
ON tblEdit.Period = X.PERIODID
AND tblEdit.Yr = X.YEAR1
AND tblEdit.Acct = X.ACTNUMBR_1
The queries are untested, because you did not provide DDL, sample data
and expected results. See: http://www.aspfaq.com/etiquette.asp?id=5006
I assume that (Period, Yr, Acct) is a unique key in tblEdit.
The first query uses ANSI syntax; the second query uses T-SQL syntax.
Razvan

Query Frustrations

Hi i got this query

SELECT COUNT(dbo.Table1.activity) AS TOTAL, dbo.Table2.name
FROM dbo.Table1 LEFT OUTER JOIN
dbo.Table2 ON dbo.Table1.activity = dbo.Table2.type
WHERE (dbo.Table1.activity = 2)
GROUP BY dbo.Table2.name

#-------------------------
and the result is this.....

name TOTAL
Not Sold 12179
Hangup 12179
Doesn't Want to Give ACH 12179
Doesn't Want to Give CC 12179
#-------------------------

what chages should i make in my query to have a result like this....

name TOTAL
Not Sold 13
Hangup 300
Doesn't Want to Give ACH 25
Doesn't Want to Give CC 30

here's my table definitions
Table1
---
activity
2
3
2
3
5

Table2
-------
type | name
2 | Not Sold
3 | Blah
2 | Hangup
2 | Doesn't Want to Give ACH
5 | Do Not Call
2 | Doesn't Want to Give CCType needs to be unique in Table2.sql

Query execution time

Hi,
Is there any command that i can use, that tells me with
precision the execution time of a query?
I'm using:
select getdate()
execute Query
select getdate()
Thanks,
Best regards
Have you tried: SET STATISTICS TIME ON ?
This will return a message with CPU time and elapsed time.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards
|||Look at the SET STATISTICS TIME statement. You might want to look ate SET
STATISTICS IO and SET STATISTICS PROFILE as additional performance tuning
tools.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards

Query execution time

Hi,
Is there any command that i can use, that tells me with
precision the execution time of a query?
I'm using:
select getdate()
execute Query
select getdate()
Thanks,
Best regardsHave you tried: SET STATISTICS TIME ON ?
This will return a message with CPU time and elapsed time.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx
.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards|||Look at the SET STATISTICS TIME statement. You might want to look ate SET
STATISTICS IO and SET STATISTICS PROFILE as additional performance tuning
tools.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx
.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards

Query error during runtime

I get Invalid object name 'bstr'. when I try to run this query

Select distinct c0.oid, c1.Value, c2.Value, c3.Value
From
(SELECT oid FROM dbo.COREAttribute
WHERE CLSID IN (
'{1449DB2B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB31-DB97-11D6-A551-00B0D021E10A}',
'{1449DB33-DB97-11D6-A551-00B0D021E10A}',
'{1449DB35-DB97-11D6-A551-00B0D021E10A}',
'{1449DB37-DB97-11D6-A551-00B0D021E10A}',
'{1449DB39-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB43-DB97-11D6-A551-00B0D021E10A}',
'{1449DB45-DB97-11D6-A551-00B0D021E10A}',
'{1449DB47-DB97-11D6-A551-00B0D021E10A}',
'{1449DB49-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB51-DB97-11D6-A551-00B0D021E10A}',
'{DAA598D9-E7B5-4155-ABB7-0C2C24466740}',
'{6921DAC3-5F91-4188-95B9-0FCE04D3A04D}',
'{128F17D4-2014-480A-96C6-370599F32F67}',
'{9F3A64C9-28F3-440B-B694-3E341471ED8E}',
'{2E3AB438-7652-4656-9A18-4F9C1DC27E8C}',
'{B69E74A7-0E48-4BA2-B4B7-5D9FFEDC2D97}',
'{2BB836D3-2DC1-4899-9406-6A495ED395C3}',
'{9CFFDC3A-5DF5-4AD8-B067-6EF5A9736681}',
'{E18E470B-B297-43D2-B9CD-71AF65654970}',
'{9BDCDA97-1171-409D-B3AB-71DA08B1E6D3}',
'{0E91AC62-7929-4B42-B771-7A6399A9E3B0}',
'{C8BAE335-CCB7-4F1D-8E9D-85C301188BE2}',
'{97E6E186-8F32-42E6-B81C-8E2E0D7C5ABA}',
'{BE5B6233-D4E7-4EF6-B5FC-91EA52128723}',
'{4ECDAAE1-828A-4C43-8A66-A7AB6966F368}',
'{19082B90-EF02-45CC-B037-AFD0CF91D69E}',
'{6F76CEF7-EBC0-48C6-8B78-C5330324C019}',
'{18492042-B22A-4370-BFA3-D0481800BBC7}',
'{A71343AD-CC09-4033-A224-D2D8C300904A}',
'{EC10BD0A-FDE3-4484-BEA6-D5A2E456256C}',
'{F7F8A4E1-651A-4A48-B55A-E8DA59D401B2}',
'{A923226F-B920-4CFA-9B0D-F422D1C36902}',
'{A95ACA6A-16AC-47E4-A9A6-F530D50A475A}',
'{C31DB61A-5221-42CF-9A73-FE76D5158647}'
)) AS c0 ,

(select oid, dispid, value
FROM dbo.COREBSTRAttribute
WHERE iid = '{1449DB20-DB97-11D6-A551-00B0D021E10A}'
) As bstr

LEFT JOIN bstr AS c1
ON (c0.oid = c1.oid)
AND c1.dispid = 28
LEFT JOIN bstr AS c2
ON (c0.oid = c2.oid)
AND c2.dispid = 112
LEFT JOIN bstr AS c3
ON (c0.oid = c3.oid)
AND c3.dispid = 192

thanks
Sunitsjoshi (sjoshi@.ingr.com) writes:
> I get Invalid object name 'bstr'. when I try to run this query
> Select distinct c0.oid, c1.Value, c2.Value, c3.Value
> From
> (SELECT oid FROM dbo.COREAttribute
> WHERE CLSID IN (
> '{1449DB2B-DB97-11D6-A551-00B0D021E10A}',
>...
> '{C31DB61A-5221-42CF-9A73-FE76D5158647}'
> )) AS c0 ,
> (select oid, dispid, value
> FROM dbo.COREBSTRAttribute
> WHERE iid = '{1449DB20-DB97-11D6-A551-00B0D021E10A}'
> ) As bstr
> LEFT JOIN bstr AS c1
> ON (c0.oid = c1.oid)
> AND c1.dispid = 28
> LEFT JOIN bstr AS c2
> ON (c0.oid = c2.oid)
> AND c2.dispid = 112
> LEFT JOIN bstr AS c3
> ON (c0.oid = c3.oid)
> AND c3.dispid = 192

You cannot refer a virtual table in this way in a query. What you are
trying is a Common Table Expression, which is a new feature in SQL 2005
(culled from ANSI SQL). There you would write:

WITH bstr AS
(select oid, dispid, value
FROM dbo.COREBSTRAttribute
WHERE iid = '{1449DB20-DB97-11D6-A551-00B0D021E10A}')
Select distinct c0.oid, c1.Value, c2.Value, c3.Value
From (SELECT oid FROM dbo.COREAttribute
WHERE CLSID IN ('{1449DB2B-DB97-11D6-A551-00B0D021E10A}',
...
'{C31DB61A-5221-42CF-9A73-FE76D5158647}'
)) AS c0 ,
LEFT JOIN bstr AS c1
ON (c0.oid = c1.oid)
AND c1.dispid = 28
LEFT JOIN bstr AS c2
ON (c0.oid = c2.oid)
AND c2.dispid = 112
LEFT JOIN bstr AS c3
ON (c0.oid = c3.oid)
AND c3.dispid = 192

In SQL 2000, you will have to paste in the query in all the three
LEFT JOIN. Or put the stuff into a temp table or table variable first,
so that the query is evaluated only once. In fact this is necessary
in SQL 2005, as the nice syntax only acts as a macro definition.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Query Cost - Big diffrence

I have a table called test with five fields of which no is the primary
key
When I run the following query
select * from tt where no not in (23,76)
select * from tt where no !=23 and no!=76
The cost of first query is 0.53% and that of second is 99.47%
Why is this huge difference?
MadhivananIt's hard to offer any help without the exact table definitions and exact
query you are running. Is your table called tt or test?
In any case, the percentages you quote are estimates, and they
may or may not correspond to actual relative costs. My guess
is that the difference is the result of how the literal constants 23 and 76
are typed by the query processor. Assuming the column [no] is of
type INT, try
select * from tt where [no] != cast(23 as int) and [no] != cast(76 as int)
If this doesn't help, please post the CREATE TABLE statements and
the exact query, along with the query plans for each query.
Steve Kass
Drew University
Madhivanan wrote:

>I have a table called test with five fields of which no is the primary
>key
>When I run the following query
>select * from tt where no not in (23,76)
>select * from tt where no !=23 and no!=76
>The cost of first query is 0.53% and that of second is 99.47%
>Why is this huge difference?
>Madhivanan
>
>|||There are a few places in MSDN which make passing mention of the possibility
that SQL Server's query optimizer may not choose indexes efficiently when
the OR operator a negative comparisons (ex: != ) are used in a WHERE clause.
Look closely at the execution plan and see if index selection is different
between the two queries.
http://msdn.microsoft.com/library/d...
etchapt14.asp
http://support.microsoft.com/defaul...=kb;en-us;68470
You can explicitly tell the query processor which index to use by specifying
an index hint.
http://msdn.microsoft.com/library/d...r />
_8upf.asp
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1123770945.884995.22410@.g43g2000cwa.googlegroups.com...
>I have a table called test with five fields of which no is the primary
> key
> When I run the following query
> select * from tt where no not in (23,76)
> select * from tt where no !=23 and no!=76
> The cost of first query is 0.53% and that of second is 99.47%
> Why is this huge difference?
> Madhivanan
>