Null values: query

Showing posts with label query. Show all posts

Friday, March 30, 2012

Rogue Cursor

Can anybody help'
I have a stored procedure that takes 6 minutes to run when
executed through Query analyser When its called by a
scheduled job it takes over 6 hours. Nothing else runs on
the server so there are no issues with conflicts.
The stored procedure is a simple fast forward cursor that
goes through the rows of a table containing approx 300k
rows. I have evaluated the execution using the SQL
profiler and when the stored procedure is run from a job
the cursor seems to pause for approx 600ms every 7
cycles , this pause does not happen at all when it is
executed via query analyser. As there are over
The server is SQLServer7 Standard (evaluation) and run on
an NT4 platform. I have tried to run the same stored
procedure on a much higher spec machine (SQLServer 2k,
windows 2k server, dual processor, raid config) but
although the runtimes for either method were a lot
quicker, calling the stored procedure from a job is at
least 50% slower.
Can anyone tell me?
A. What causes this?
B. Can it be fixed?
C. What else could be affected?You very very seldom need any cursors at all. Can you post the code so
someone here can rewrite it without a cursor? Based on my experience your sp
will then run in about 6 seconds, without a cursor.
--
Jacco Schalkwijk
SQL Server MVP
"H jones" <hmjones@.atlanticeg.com> wrote in message
news:0a3e01c3c975$333c1cc0$a401280a@.phx.gbl...
> Can anybody help'
> I have a stored procedure that takes 6 minutes to run when
> executed through Query analyser When its called by a
> scheduled job it takes over 6 hours. Nothing else runs on
> the server so there are no issues with conflicts.
> The stored procedure is a simple fast forward cursor that
> goes through the rows of a table containing approx 300k
> rows. I have evaluated the execution using the SQL
> profiler and when the stored procedure is run from a job
> the cursor seems to pause for approx 600ms every 7
> cycles , this pause does not happen at all when it is
> executed via query analyser. As there are over
> The server is SQLServer7 Standard (evaluation) and run on
> an NT4 platform. I have tried to run the same stored
> procedure on a much higher spec machine (SQLServer 2k,
> windows 2k server, dual processor, raid config) but
> although the runtimes for either method were a lot
> quicker, calling the stored procedure from a job is at
> least 50% slower.
> Can anyone tell me?
> A. What causes this?
> B. Can it be fixed?
> C. What else could be affected?
>

Wednesday, March 28, 2012

ROBUST PLAN error

Hi all
I'm trying to INSERT rows from one table to the other in a Oracle Linked server to SQL server
My query is something like this
INSERT INTO ORACLE_LINK..User.Table
SELECT *
FROM ORACLE_LINK..User.Table
I'm getting the following error
Server: Msg 510, Level 16, State 1, Line
Cannot create a worktable row larger than allowable maximum. Resubmit your query with the ROBUST PLAN hint
(Both are oracle tables only)
Can anybody tell the reason for this error
Thanks
Siva.It looks like the rows in the Oracle tables are longer than the maximum
rowlength in SQL Server, which is 8060 bytes.
You are probably best off to issue this query as a pass-through query using
OPENQUERY(ORACLE_LINK, 'INSERT INTO User.Table1SELECT * FROM User.Table2')
--
Jacco Schalkwijk
SQL Server MVP
"Siva" <siva116@.yahoo.com> wrote in message
news:0CD3DD0E-9B5A-441F-A040-030C9E108750@.microsoft.com...
> Hi all,
> I'm trying to INSERT rows from one table to the other in a Oracle Linked
server to SQL server.
> My query is something like this:
> INSERT INTO ORACLE_LINK..User.Table1
> SELECT *
> FROM ORACLE_LINK..User.Table2
> I'm getting the following error:
> Server: Msg 510, Level 16, State 1, Line 1
> Cannot create a worktable row larger than allowable maximum. Resubmit your
query with the ROBUST PLAN hint.
> (Both are oracle tables only).
> Can anybody tell the reason for this error?
> Thanks,
> Siva.

ROBUST PLAN error

Hi all,
I'm trying to INSERT rows from one table to the other in a Oracle Linked se
rver to SQL server.
My query is something like this:
INSERT INTO ORACLE_LINK..User.Table1
SELECT *
FROM ORACLE_LINK..User.Table2
I'm getting the following error:
Server: Msg 510, Level 16, State 1, Line 1
Cannot create a worktable row larger than allowable maximum. Resubmit your q
uery with the ROBUST PLAN hint.
(Both are oracle tables only).
Can anybody tell the reason for this error?
Thanks,
Siva.It looks like the rows in the Oracle tables are longer than the maximum
rowlength in SQL Server, which is 8060 bytes.
You are probably best off to issue this query as a pass-through query using
OPENQUERY(ORACLE_LINK, 'INSERT INTO User.Table1SELECT * FROM User.Table2')
Jacco Schalkwijk
SQL Server MVP
"Siva" <siva116@.yahoo.com> wrote in message
news:0CD3DD0E-9B5A-441F-A040-030C9E108750@.microsoft.com...
> Hi all,
> I'm trying to INSERT rows from one table to the other in a Oracle Linked
server to SQL server.
> My query is something like this:
> INSERT INTO ORACLE_LINK..User.Table1
> SELECT *
> FROM ORACLE_LINK..User.Table2
> I'm getting the following error:
> Server: Msg 510, Level 16, State 1, Line 1
> Cannot create a worktable row larger than allowable maximum. Resubmit your
query with the ROBUST PLAN hint.
> (Both are oracle tables only).
> Can anybody tell the reason for this error?
> Thanks,
> Siva.

Friday, March 23, 2012

Right outer join problem

Hi,

I need your help to resolve this problem. I have written a right outer
join query between 2 indipendent tables as follows.

select b.Account_desc, b.Account, a.CSPL_CSPL from Actual_data_final a
right outer join Actual_account_Tbl b on a.account_desc =
b.account_desc where (a.source_type = 'TY02' or a.source_type is
null) and (a.month = '2ND HALF' or a.month is null) and (a.year = 2004
or a.year is null) and (a.product = 'NP' or a.product is null) order
by b.Sno

But the problem is I have few records in table Actual_account_Tbl but
do not match the condition "a.account_desc = b.account_desc".

As per right outer join, I suppose to get those records as a result of
the above query with null values of a.CSPL_CSPL. But it is not
displaying.

Please help me to resolve this problem.

Regards,
OmavCould you post some code that actually reproduces the problem you describe
(DDL and sample data INSERTs). I tried the following which appears to work:

CREATE TABLE Actual_data_final (cspl_cspl INTEGER NOT NULL, month
VARCHAR(10) NOT NULL, year INTEGER NOT NULL, product CHAR(2) NOT NULL,
source_type VARCHAR(10) NOT NULL, account_desc VARCHAR(10) NOT NULL)

CREATE TABLE Actual_account_Tbl (account INTEGER NOT NULL, account_desc
VARCHAR(10) NOT NULL, sno INTEGER NOT NULL)

INSERT INTO Actual_account_Tbl VALUES (123,'ABC',0)

SELECT B.Account_desc, B.Account, A.cspl_cspl
FROM Actual_data_final AS A
RIGHT OUTER JOIN Actual_account_Tbl AS B
ON A.account_desc = B.account_desc
WHERE (A.source_type = 'TY02' OR A.source_type IS NULL)
AND (A.month = '2ND HALF' OR A.month IS NULL)
AND (A.year = 2004 OR A.year IS NULL)
AND (A.product = 'NP' OR A.product IS NULL)
ORDER BY B.sno

Result:

Account_desc Account cspl_cspl
---- ---- ----
ABC 123 NULL

Also, note that you can probably simplify the above query by putting your
WHERE criteria in the ON clause:

SELECT B.Account_desc, B.Account, A.cspl_cspl
FROM Actual_data_final AS A
RIGHT OUTER JOIN Actual_account_Tbl AS B
ON A.account_desc = B.account_desc
AND A.source_type = 'TY02'
AND A.month = '2ND HALF'
AND A.year = 2004
AND A.product = 'NP'
ORDER BY B.sno

--
David Portas
SQL Server MVP
--|||Hi,

Thanks for your help..

The following query has failed to return all the records.

SELECT b.Account_desc, b.Account,
IIf(a.source_type = 'LY01', a.CSPL_CSPL,0), IIf(a.source_type = 'LY01',
a.CSPL_CMS,0), IIf(a.source_type = 'LY01', a.CSPL_CMM,0),
IIf(a.source_type = 'LY01', a.CSPL_CMT,0) from Actual_data_final a right
outer join Actual_account_Tbl b on a.Account_desc = b.Account_desc
where a.source_type = 'LY01'

There are total 143 records in Actual_account_Tbl. But the above query
returned only 135 records i.e., only those records satisfy the condition
"a.Account_desc = b.Account_desc" are returned.

As per right outerjoin in the above statement I suppose to get all the
records from table 'b', and blank data from table 'a' if it doesn't
satisfy the condition.

Why it is not consistant?

Pls help me.

Thanks and Regards.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||That isn't a MS SQLServer query. TSQL doesn't have an IIF function.

The problem is that you have a WHERE clause referencing the outer table. Put
the WHERE condition in the ON clause:

...
OUTER JOIN Actual_account_Tbl b
ON a.Account_desc = b.Account_desc
AND a.source_type = 'LY01'

If you need more help then post to the correct group for the product you are
using (Access?).

--
David Portas
SQL Server MVP
--|||On 28 May 2004 06:46:19 GMT, k k wrote:

>Hi,
>Thanks for your help..
>The following query has failed to return all the records.
>SELECT b.Account_desc, b.Account,
>IIf(a.source_type = 'LY01', a.CSPL_CSPL,0), IIf(a.source_type = 'LY01',
>a.CSPL_CMS,0), IIf(a.source_type = 'LY01', a.CSPL_CMM,0),
>IIf(a.source_type = 'LY01', a.CSPL_CMT,0) from Actual_data_final a right
>outer join Actual_account_Tbl b on a.Account_desc = b.Account_desc
>where a.source_type = 'LY01'
>There are total 143 records in Actual_account_Tbl. But the above query
>returned only 135 records i.e., only those records satisfy the condition
>"a.Account_desc = b.Account_desc" are returned.
>As per right outerjoin in the above statement I suppose to get all the
>records from table 'b', and blank data from table 'a' if it doesn't
>satisfy the condition.
>Why it is not consistant?
>Pls help me.
>Thanks and Regards.

Hi k k,

For right (and left) joins, the place of condition matters. First, the
join is performed. Only the ON clause is checked for the join. Rows that
match are joined, rows from the second table (in case of a right join)
that don't match are added with NULLs as placeholders for the columns from
the first table.

Next, the WHERE clause is applied to the result of the join. Only the rows
in the result set that match the conditions are retained in the output.

In your case, you right joined on equality of account_desc (the
intermediate result had at least 143 rows, maybe more if there are rows in
b that match more than one row in a). Then the WHERE filter for
source_type 'LY01' discarded all rows from b without matching a, as the
outer join set source_type to NULL for these rows. The net result was the
same as an inner join would have been.

Trying to conclude what you wanted from reading your query, I think you'll
have the desired results if you just change "where" to "and". This will
make the LY01 requirement part of the join condition and you're left with
no where clause.

BTW, you're posting in a SQL Server newsgroup, but your use of IIf shows
that you're actually using MS Access. It's best to post to a newsgroup for
the product you're using, since subtle (and some less subtle) differences
between tools can cause big differences.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Right characters from ~

Hai,
Who can help me '
I'm making a query from a table.
In that table there is a column with the name NR_
The rows of that column give a result as
~2000252
~2003
~26578
What i want as result, the most right character from the ~character and as
result in the same column
200252
2003
26578
Who can help me ?There are several string related functions that will do what you want. Look
up documentation on right(), substring() and replace()
"Jaap" <Jaap@.discussions.microsoft.com> wrote in message
news:B0A0060F-EA08-4AE0-A516-67C4623564CA@.microsoft.com...
> Hai,
> Who can help me '
> I'm making a query from a table.
> In that table there is a column with the name NR_
> The rows of that column give a result as
> ~2000252
> ~2003
> ~26578
> What i want as result, the most right character from the ~character and as
> result in the same column
> 200252
> 2003
> 26578
> Who can help me ?|||Hi
Try something like:
SELECT RIGHT (col1,CHARINDEX('~',REVERSE(col1))-1)
FROM
( SELECT '~2000252' as col1
UNION ALL SELECT '~2003'
UNION ALL SELECT '~26578'
UNION ALL SELECT 'abc~def' ) A
John
"Jaap" <Jaap@.discussions.microsoft.com> wrote in message
news:B0A0060F-EA08-4AE0-A516-67C4623564CA@.microsoft.com...
> Hai,
> Who can help me '
> I'm making a query from a table.
> In that table there is a column with the name NR_
> The rows of that column give a result as
> ~2000252
> ~2003
> ~26578
> What i want as result, the most right character from the ~character and as
> result in the same column
> 200252
> 2003
> 26578
> Who can help me ?|||Hi,
Use the below query
select replace(column_name,'~','') as column from table_name
Thanks
Hari
SQL Server MVP
"Jaap" <Jaap@.discussions.microsoft.com> wrote in message
news:B0A0060F-EA08-4AE0-A516-67C4623564CA@.microsoft.com...
> Hai,
> Who can help me '
> I'm making a query from a table.
> In that table there is a column with the name NR_
> The rows of that column give a result as
> ~2000252
> ~2003
> ~26578
> What i want as result, the most right character from the ~character and as
> result in the same column
> 200252
> 2003
> 26578
> Who can help me ?|||yes, this is great it works
many thanks
"John Bell" wrote:

> Hi
> Try something like:
> SELECT RIGHT (col1,CHARINDEX('~',REVERSE(col1))-1)
> FROM
> ( SELECT '~2000252' as col1
> UNION ALL SELECT '~2003'
> UNION ALL SELECT '~26578'
> UNION ALL SELECT 'abc~def' ) A
> John
> "Jaap" <Jaap@.discussions.microsoft.com> wrote in message
> news:B0A0060F-EA08-4AE0-A516-67C4623564CA@.microsoft.com...
>
>sql

Right Alignment in SQL

Hi all,
By default all the values in the result set are left aligned. But
I want to align a column in a result set of a query to right side...
Is it possible in SQL?
Example:
Charges
11145.00
171.00
26.00
6.00
Result should be
Charges
11145.00
171.00
26.00
6.00
Please advise
Thanks,
Souraselect str(charges,10,2) from tb
--
-oj
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:AFB802C8-4C26-43CA-A1A4-3F9AF28EF8EB@.microsoft.com...
> Hi all,
> By default all the values in the result set are left aligned. But
> I want to align a column in a result set of a query to right side...
> Is it possible in SQL?
> Example:
> Charges
> 11145.00
> 171.00
> 26.00
> 6.00
> Result should be
> Charges
> 11145.00
> 171.00
> 26.00
> 6.00
> Please advise
> Thanks,
> Soura|||Hi
This is really dependent on the client tool, for example SQL Server
Management studio has an option to right align numeric values in the results
to text option settings.
John
"SouRa" wrote:
> Hi all,
> By default all the values in the result set are left aligned. But
> I want to align a column in a result set of a query to right side...
> Is it possible in SQL?
> Example:
> Charges
> 11145.00
> 171.00
> 26.00
> 6.00
> Result should be
> Charges
> 11145.00
> 171.00
> 26.00
> 6.00
> Please advise
> Thanks,
> Soura|||how do i enter a new question?
i click new and nothing happens
--
thank you
ms. soto
"SouRa" wrote:
> Hi all,
> By default all the values in the result set are left aligned. But
> I want to align a column in a result set of a query to right side...
> Is it possible in SQL?
> Example:
> Charges
> 11145.00
> 171.00
> 26.00
> 6.00
> Result should be
> Charges
> 11145.00
> 171.00
> 26.00
> 6.00
> Please advise
> Thanks,
> Soura|||Hi
That will depend on how you are using the newsgroup, for instance if you are
using the technet communities
http://www.microsoft.com/technet/community/newsgroups/server/sql.mspx you
will need to create a profile and use a passport to sign in before adding new
messages or replying to existing messages. As you can reply then it looks
like you have this, in which case you would need to select the new button
which is just beneath the "search for:" area. If you are using Outlook
Express choose the New Post button or File/New/News Message from the menu.
HTH
John
"ms. soto" wrote:
> how do i enter a new question?
> i click new and nothing happens
> --
> thank you
> ms. soto
>
> "SouRa" wrote:
> > Hi all,
> >
> > By default all the values in the result set are left aligned. But
> > I want to align a column in a result set of a query to right side...
> > Is it possible in SQL?
> >
> > Example:
> >
> > Charges
> > 11145.00
> > 171.00
> > 26.00
> > 6.00
> >
> > Result should be
> >
> > Charges
> > 11145.00
> > 171.00
> > 26.00
> > 6.00
> >
> > Please advise
> >
> > Thanks,
> > Soura

Wednesday, March 21, 2012

Rewriting left joins

Hello,

I am working on a query that has 11 left join statements, some are hitting against reference data that has a small amount of records, whereas others not so small. From a performance standpoint, should I look at rewriting this query, and how would I do so? What is an alternative to left joins; any examples anyone has?

Thanks.

The alternative to a join is a subquery. Google "Join or subquery" for information in which performs better. Loads of different opinions, but it would appear that only testingyourquery inyourenvironment will produce the right answer foryou.|||

bmains:
From a performance standpoint, should I look at rewriting this query

I would say that it depends on whether your query is performing poorly or not. I wouldn't touch it if it isn't broken

Rewriting a query causing lock escalation

All,
I've identified a query that is causing deadlocks in our database. The
SELECT portion of this query seems to be causing lock escalation - causing
the whole RECORDS table to lock - and causing other processes to deadlock.
I'd like to somehow rewrite this query to decrease the lock escalation. Does
anyone have any ideas as to how I can do this? This query is part of a
stored procedure that gets called via an insert trigger on a different
table. All fields are integers. 5 variables are inputs to this query -
@.P_TID, @.P_SID, @.T_TID, @.T_SID, @.E_ID.
I was thinking of breaking up the query into smaller versions ... but not
sure where to start.
I should add that we added option (maxdop 1) to force this query to only run
on one thread - it seems to reduce the deadlocks - but not eliminate them.
Thanks in advance ... SS
---
option (maxdop 1)
INSERT INTO RECORDS
(
P_TID
,P_SID
,T_TID
,T_SID
,E_ID
,PCOUNT
)
SELECT
p1.P_TID
,p1.P_SID
,p2.T_TID
,p2.T_SID
,@.E_ID
,p1.PCOUNT*p2.PCOUNT
FROM
(RECORDS p1 WITH (ROWLOCK)
INNER JOIN RECORDS p2 WITH (ROWLOCK)
ON p1.T_TID=@.P_TID
AND p1.T_SID=@.P_SID
AND p2.P_TID=@.T_TID
AND p2.P_SID=@.T_SID
) LEFT JOIN RECORDS p3 WITH (ROWLOCK)
ON p3.P_TID=p1.P_TID
AND p3.P_SID=p1.P_SID
AND p3.T_TID=p2.T_TID
AND p3.T_SID=p2.T_SID
AND p3.E_ID=@.E_ID
WHERE
p1.E_ID IN (0, @.E_ID)
AND p2.E_ID IN (0, @.E_ID)
AND p3.E_ID IS NULL
option (maxdop 1)Hi Steph
How many rows are in Records with E_ID IN (0, @.E_ID), you may want to move
them into a temporary table. Also/alternatively check the query execution
plan to see if adding index will help, you may want to try the Index tuning
wizard to see if it comes up with anything.
John
"Steph" wrote:
> All,
> I've identified a query that is causing deadlocks in our database. The
> SELECT portion of this query seems to be causing lock escalation - causing
> the whole RECORDS table to lock - and causing other processes to deadlock.
> I'd like to somehow rewrite this query to decrease the lock escalation. Does
> anyone have any ideas as to how I can do this? This query is part of a
> stored procedure that gets called via an insert trigger on a different
> table. All fields are integers. 5 variables are inputs to this query -
> @.P_TID, @.P_SID, @.T_TID, @.T_SID, @.E_ID.
> I was thinking of breaking up the query into smaller versions ... but not
> sure where to start.
> I should add that we added option (maxdop 1) to force this query to only run
> on one thread - it seems to reduce the deadlocks - but not eliminate them.
> Thanks in advance ... SS
> ---
> option (maxdop 1)
> INSERT INTO RECORDS
> (
> P_TID
> ,P_SID
> ,T_TID
> ,T_SID
> ,E_ID
> ,PCOUNT
> )
> SELECT
> p1.P_TID
> ,p1.P_SID
> ,p2.T_TID
> ,p2.T_SID
> ,@.E_ID
> ,p1.PCOUNT*p2.PCOUNT
> FROM
> (RECORDS p1 WITH (ROWLOCK)
> INNER JOIN RECORDS p2 WITH (ROWLOCK)
> ON p1.T_TID=@.P_TID
> AND p1.T_SID=@.P_SID
> AND p2.P_TID=@.T_TID
> AND p2.P_SID=@.T_SID
> ) LEFT JOIN RECORDS p3 WITH (ROWLOCK)
> ON p3.P_TID=p1.P_TID
> AND p3.P_SID=p1.P_SID
> AND p3.T_TID=p2.T_TID
> AND p3.T_SID=p2.T_SID
> AND p3.E_ID=@.E_ID
> WHERE
> p1.E_ID IN (0, @.E_ID)
> AND p2.E_ID IN (0, @.E_ID)
> AND p3.E_ID IS NULL
> option (maxdop 1)
>
>|||John - thanks for the suggestion. I did look at the execution plans - and
saw where some bottlenecks could be. The Index Tuning Wizard suggested
putting an index on a column that either has the values 0, 1, 2 - so not
sure how useful the index would be ...
I'm considering doing the select - putting the results into a table variable
then inserting the values from the table variable. Don't want to add any
temp tables now - don't want to add more locks on tempdb.
Thanks for suggestions ...
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:441B4D04-2EF2-4A7F-B131-05B04A63A63A@.microsoft.com...
> Hi Steph
> How many rows are in Records with E_ID IN (0, @.E_ID), you may want to move
> them into a temporary table. Also/alternatively check the query execution
> plan to see if adding index will help, you may want to try the Index
tuning
> wizard to see if it comes up with anything.
> John
>
> "Steph" wrote:
> > All,
> >
> > I've identified a query that is causing deadlocks in our database. The
> > SELECT portion of this query seems to be causing lock escalation -
causing
> > the whole RECORDS table to lock - and causing other processes to
deadlock.
> >
> > I'd like to somehow rewrite this query to decrease the lock escalation.
Does
> > anyone have any ideas as to how I can do this? This query is part of a
> > stored procedure that gets called via an insert trigger on a different
> > table. All fields are integers. 5 variables are inputs to this query -
> > @.P_TID, @.P_SID, @.T_TID, @.T_SID, @.E_ID.
> >
> > I was thinking of breaking up the query into smaller versions ... but
not
> > sure where to start.
> >
> > I should add that we added option (maxdop 1) to force this query to only
run
> > on one thread - it seems to reduce the deadlocks - but not eliminate
them.
> >
> > Thanks in advance ... SS
> >
> > ---
> >
> > option (maxdop 1)
> >
> > INSERT INTO RECORDS
> > (
> > P_TID
> > ,P_SID
> > ,T_TID
> > ,T_SID
> > ,E_ID
> > ,PCOUNT
> > )
> > SELECT
> > p1.P_TID
> > ,p1.P_SID
> > ,p2.T_TID
> > ,p2.T_SID
> > ,@.E_ID
> > ,p1.PCOUNT*p2.PCOUNT
> > FROM
> > (RECORDS p1 WITH (ROWLOCK)
> > INNER JOIN RECORDS p2 WITH (ROWLOCK)
> > ON p1.T_TID=@.P_TID
> > AND p1.T_SID=@.P_SID
> > AND p2.P_TID=@.T_TID
> > AND p2.P_SID=@.T_SID
> > ) LEFT JOIN RECORDS p3 WITH (ROWLOCK)
> > ON p3.P_TID=p1.P_TID
> > AND p3.P_SID=p1.P_SID
> > AND p3.T_TID=p2.T_TID
> > AND p3.T_SID=p2.T_SID
> > AND p3.E_ID=@.E_ID
> > WHERE
> > p1.E_ID IN (0, @.E_ID)
> > AND p2.E_ID IN (0, @.E_ID)
> > AND p3.E_ID IS NULL
> > option (maxdop 1)
> >
> >
> >|||Hi
If you have a significant number of rows look at using a temporary table
rather than a table variable.
John
"Steph" wrote:
> John - thanks for the suggestion. I did look at the execution plans - and
> saw where some bottlenecks could be. The Index Tuning Wizard suggested
> putting an index on a column that either has the values 0, 1, 2 - so not
> sure how useful the index would be ...
> I'm considering doing the select - putting the results into a table variable
> then inserting the values from the table variable. Don't want to add any
> temp tables now - don't want to add more locks on tempdb.
> Thanks for suggestions ...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:441B4D04-2EF2-4A7F-B131-05B04A63A63A@.microsoft.com...
> > Hi Steph
> >
> > How many rows are in Records with E_ID IN (0, @.E_ID), you may want to move
> > them into a temporary table. Also/alternatively check the query execution
> > plan to see if adding index will help, you may want to try the Index
> tuning
> > wizard to see if it comes up with anything.
> >
> > John
> >
> >
> > "Steph" wrote:
> >
> > > All,
> > >
> > > I've identified a query that is causing deadlocks in our database. The
> > > SELECT portion of this query seems to be causing lock escalation -
> causing
> > > the whole RECORDS table to lock - and causing other processes to
> deadlock.
> > >
> > > I'd like to somehow rewrite this query to decrease the lock escalation.
> Does
> > > anyone have any ideas as to how I can do this? This query is part of a
> > > stored procedure that gets called via an insert trigger on a different
> > > table. All fields are integers. 5 variables are inputs to this query -
> > > @.P_TID, @.P_SID, @.T_TID, @.T_SID, @.E_ID.
> > >
> > > I was thinking of breaking up the query into smaller versions ... but
> not
> > > sure where to start.
> > >
> > > I should add that we added option (maxdop 1) to force this query to only
> run
> > > on one thread - it seems to reduce the deadlocks - but not eliminate
> them.
> > >
> > > Thanks in advance ... SS
> > >
> > > ---
> > >
> > > option (maxdop 1)
> > >
> > > INSERT INTO RECORDS
> > > (
> > > P_TID
> > > ,P_SID
> > > ,T_TID
> > > ,T_SID
> > > ,E_ID
> > > ,PCOUNT
> > > )
> > > SELECT
> > > p1.P_TID
> > > ,p1.P_SID
> > > ,p2.T_TID
> > > ,p2.T_SID
> > > ,@.E_ID
> > > ,p1.PCOUNT*p2.PCOUNT
> > > FROM
> > > (RECORDS p1 WITH (ROWLOCK)
> > > INNER JOIN RECORDS p2 WITH (ROWLOCK)
> > > ON p1.T_TID=@.P_TID
> > > AND p1.T_SID=@.P_SID
> > > AND p2.P_TID=@.T_TID
> > > AND p2.P_SID=@.T_SID
> > > ) LEFT JOIN RECORDS p3 WITH (ROWLOCK)
> > > ON p3.P_TID=p1.P_TID
> > > AND p3.P_SID=p1.P_SID
> > > AND p3.T_TID=p2.T_TID
> > > AND p3.T_SID=p2.T_SID
> > > AND p3.E_ID=@.E_ID
> > > WHERE
> > > p1.E_ID IN (0, @.E_ID)
> > > AND p2.E_ID IN (0, @.E_ID)
> > > AND p3.E_ID IS NULL
> > > option (maxdop 1)
> > >
> > >
> > >
>
>|||Hi
If you are seeing contention on tempdb e.g. lock timeouts on database id 2
check out http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
John
"Steph" wrote:
> John - thanks for the suggestion. I did look at the execution plans - and
> saw where some bottlenecks could be. The Index Tuning Wizard suggested
> putting an index on a column that either has the values 0, 1, 2 - so not
> sure how useful the index would be ...
> I'm considering doing the select - putting the results into a table variable
> then inserting the values from the table variable. Don't want to add any
> temp tables now - don't want to add more locks on tempdb.
> Thanks for suggestions ...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:441B4D04-2EF2-4A7F-B131-05B04A63A63A@.microsoft.com...
> > Hi Steph
> >
> > How many rows are in Records with E_ID IN (0, @.E_ID), you may want to move
> > them into a temporary table. Also/alternatively check the query execution
> > plan to see if adding index will help, you may want to try the Index
> tuning
> > wizard to see if it comes up with anything.
> >
> > John
> >
> >
> > "Steph" wrote:
> >
> > > All,
> > >
> > > I've identified a query that is causing deadlocks in our database. The
> > > SELECT portion of this query seems to be causing lock escalation -
> causing
> > > the whole RECORDS table to lock - and causing other processes to
> deadlock.
> > >
> > > I'd like to somehow rewrite this query to decrease the lock escalation.
> Does
> > > anyone have any ideas as to how I can do this? This query is part of a
> > > stored procedure that gets called via an insert trigger on a different
> > > table. All fields are integers. 5 variables are inputs to this query -
> > > @.P_TID, @.P_SID, @.T_TID, @.T_SID, @.E_ID.
> > >
> > > I was thinking of breaking up the query into smaller versions ... but
> not
> > > sure where to start.
> > >
> > > I should add that we added option (maxdop 1) to force this query to only
> run
> > > on one thread - it seems to reduce the deadlocks - but not eliminate
> them.
> > >
> > > Thanks in advance ... SS
> > >
> > > ---
> > >
> > > option (maxdop 1)
> > >
> > > INSERT INTO RECORDS
> > > (
> > > P_TID
> > > ,P_SID
> > > ,T_TID
> > > ,T_SID
> > > ,E_ID
> > > ,PCOUNT
> > > )
> > > SELECT
> > > p1.P_TID
> > > ,p1.P_SID
> > > ,p2.T_TID
> > > ,p2.T_SID
> > > ,@.E_ID
> > > ,p1.PCOUNT*p2.PCOUNT
> > > FROM
> > > (RECORDS p1 WITH (ROWLOCK)
> > > INNER JOIN RECORDS p2 WITH (ROWLOCK)
> > > ON p1.T_TID=@.P_TID
> > > AND p1.T_SID=@.P_SID
> > > AND p2.P_TID=@.T_TID
> > > AND p2.P_SID=@.T_SID
> > > ) LEFT JOIN RECORDS p3 WITH (ROWLOCK)
> > > ON p3.P_TID=p1.P_TID
> > > AND p3.P_SID=p1.P_SID
> > > AND p3.T_TID=p2.T_TID
> > > AND p3.T_SID=p2.T_SID
> > > AND p3.E_ID=@.E_ID
> > > WHERE
> > > p1.E_ID IN (0, @.E_ID)
> > > AND p2.E_ID IN (0, @.E_ID)
> > > AND p3.E_ID IS NULL
> > > option (maxdop 1)
> > >
> > >
> > >
>
>

Rewriting a query causing lock escalation

Rewrite a query efficiently

Is there a efficient way to write this query?

SELECT CASE
WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END AS Population_Range,
COUNT(CASE WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END) AS [No. Of Countries]
FROM Country
GROUP BY
CASE WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' ENDThe CASE statement embedded in you COUNT function is unnecessary:
SELECT CASE
WHEN Population BETWEEN 0 AND 100 THEN '0-100'
WHEN Population BETWEEN 101 AND 1000 THEN '101-1000'
ELSE 'Greater than 1000' END AS Population_Range,
COUNT(*) AS [No. Of Countries]
FROM Country
GROUP BY CASE
WHEN Population BETWEEN 0 AND 100 THEN '0-100'
WHEN Population BETWEEN 101 AND 1000 THEN '101-1000'
ELSE 'Greater than 1000' END

Rewite PL/SQL query in SQL Server 2005

How do I rewrite the folloiwng query of PL/SQL in SQL Server ?

select 1 from sffnd_stdoper a,sfpl_plan_rev b,sfpl_oper_v c
where b.plan_id = c.plan_id
and b.plan_version = c.plan_version
and b.plan_revision = c.plan_revision
and b.plan_alterations = c.plan_alterations
and a.stdoper_object_id = c.stdoper_object_id
and (b.plan_id,b.plan_version,b.plan_revision)
in (select plan_id, plan_version, max(plan_revision)
from sfpl_plan_rev
where plan_id = c.plan_id
and c.plan_id != a.stdoper_plan_id
group by plan_id, plan_version)
);SQL Server 2005 does not have row constructors so you have to write something like:
with p1
as
(
select plan_id, plan_version, max(plan_revision) as maxrev
from sfpl_plan_rev
group by plan_id, plan_version
)
select 1 from sffnd_stdoper a,sfpl_plan_rev b,sfpl_oper_v c
where b.plan_id = c.plan_id
and b.plan_version = c.plan_version
and b.plan_revision = c.plan_revision
and b.plan_alterations = c.plan_alterations
and a.stdoper_object_id = c.stdoper_object_id
and exists(select *
from p1
where p1.plan_id = c.plan_id
and c.plan_id a.stdoper_plan_id
and p1.pla_id = b.plan_id
and p1.plan_version = b.plan_version
and p1.maxrev = b.plan_revision);
--or
select 1 from sffnd_stdoper a,sfpl_plan_rev b,sfpl_oper_v c
where b.plan_id = c.plan_id
and b.plan_version = c.plan_version
and b.plan_revision = c.plan_revision
and b.plan_alterations = c.plan_alterations
and a.stdoper_object_id = c.stdoper_object_id
and exists(select *
from
(
select plan_id, plan_version, max(plan_revision) as maxrev
from sfpl_plan_rev
group by plan_id, plan_version
) as p1
where p1.plan_id = c.plan_id
and c.plan_id a.stdoper_plan_id
and p1.pla_id = b.plan_id
and p1.plan_version = b.plan_version
and p1.maxrev = b.plan_revision);

How do I rewrite the folloiwng query of PL/SQL in SQL Server ?

Linksql

Tuesday, March 20, 2012

Revoke View any database from public in MSSQL 2005

Hello Everyone,
I am running into an issue when I run the following query to revoke access
to all the SQL 2005 users from other DB.s
use master
Revoke View any database from public
Go
When the users logs in with SMSE he/she does not see the db under “databas
es”.
They can only access via QUERY.
Thanks,
-CarlosDoesn't it make sense, after all, you removed (REVOKE) the ability to see
(VIEW) any (that means ALL) database to everyone (PUBLIC).
Perhaps you had a different outcome in mind, and you would like some help.
But you're going to have to be more specific, It's a waste of time to try to
guess what you want as a final outcome.
Your question/request is not clear.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Carlos Caneja" <Carlos Caneja@.discussions.microsoft.com> wrote in message
news:A446C59B-C7BA-4CBF-B158-D3F88BD24434@.microsoft.com...
> Hello Everyone,
> I am running into an issue when I run the following query to revoke access
> to all the SQL 2005 users from other DB.s
> use master
> Revoke View any database from public
> Go
> When the users logs in with SMSE he/she does not see the db under
> "databases".
> They can only access via QUERY.
> Thanks,
> -Carlos
>|||Arnie,
Sorry for not being so clear on this. I will try to explaing in a different
way.
We run a shared hosting enviroment and SQL 2005 out of the box let's users
view all the db's and logins on the server when using SMSE.
By revoking the view command the users should still be able to see their
DB's since their role to each is "DBO".
As I said on the post, they can run queriues against the db using SMSE but
the tool will not graphicaly show you the db as it shows Master and tempdb.
Perhaps this will also help you understand my issue a big better.
http://weblogs.asp.net/hosterposter.../17/443118.aspx
Thanks for the reply,
-Carlos
"Arnie Rowland" wrote:

> Doesn't it make sense, after all, you removed (REVOKE) the ability to see
> (VIEW) any (that means ALL) database to everyone (PUBLIC).
> Perhaps you had a different outcome in mind, and you would like some help.
> But you're going to have to be more specific, It's a waste of time to try
to
> guess what you want as a final outcome.
> Your question/request is not clear.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Carlos Caneja" <Carlos Caneja@.discussions.microsoft.com> wrote in message
> news:A446C59B-C7BA-4CBF-B158-D3F88BD24434@.microsoft.com...
>
>|||You mention they should see the databases "since their role
to each is "DBO".
The behavior will work as the article describes but I'm not
sure what you mean by their role to each is dbo. If you mean
adding the user to db_owner role, that won't do it. The user
needs to be mapped to dbo - as in be the owner of the
database. There is a difference.
Look at who the owner of the database is by executing
sp_helpdb 'YourDatabase'
or execute
sp_helpuser 'dbo'
-Sue
On Tue, 26 Sep 2006 13:36:02 -0700, Carlos Caneja
<CarlosCaneja@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Arnie,
>Sorry for not being so clear on this. I will try to explaing in a differen
t
>way.
>We run a shared hosting enviroment and SQL 2005 out of the box let's users
>view all the db's and logins on the server when using SMSE.
>By revoking the view command the users should still be able to see their
>DB's since their role to each is "DBO".
>As I said on the post, they can run queriues against the db using SMSE but
>the tool will not graphicaly show you the db as it shows Master and tempdb.
>Perhaps this will also help you understand my issue a big better.
>http://weblogs.asp.net/hosterposter.../17/443118.aspx
>Thanks for the reply,
>-Carlos
>"Arnie Rowland" wrote:
>|||Sue,
Each user is the dbo to each database.
-Carlos
"Sue Hoegemeier" wrote:

> You mention they should see the databases "since their role
> to each is "DBO".
> The behavior will work as the article describes but I'm not
> sure what you mean by their role to each is dbo. If you mean
> adding the user to db_owner role, that won't do it. The user
> needs to be mapped to dbo - as in be the owner of the
> database. There is a difference.
> Look at who the owner of the database is by executing
> sp_helpdb 'YourDatabase'
> or execute
> sp_helpuser 'dbo'
> -Sue
> On Tue, 26 Sep 2006 13:36:02 -0700, Carlos Caneja
> <CarlosCaneja@.discussions.microsoft.com> wrote:
>
>|||Lines: 1
X-Newsreader: Forte Agent 2.0/32.652
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: c-24-8-112-204.hsd1.co.comcast.net 24.8.112.204
Xref: leafnode.mcse.ms microsoft.public.sqlserver.security:1263
DBO gets misunderstood sometimes so that's why I'm wondering
if that's not the issue. I just did a few tests and it works
fine on two instances I just tested in on. As long as the
logins were database owners, they could see the database.
Anything else, and they could not see the database.
If SomeUser is the login and user in question, the results
for sp_helpuser 'dbo' in that database are:
UserName: dbo
GroupName: db_owner
LoginName: SomeUser
The results for sp_helpdb 'YourDatabase list the owner as
SomeUser.
If that's your results, try changing the database owner
using sp_changedbowner and try changing it back to the user
you need to have own the database.
-Sue
On Wed, 27 Sep 2006 11:01:02 -0700, Carlos Caneja
<CarlosCaneja@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Each user is the dbo to each database.
>-Carlos
>"Sue Hoegemeier" wrote:
>

Monday, March 12, 2012

Reverse equivalent to TOP

Is there anyway I can return only the last row of a query, like TOP does for the top most items?

I would like to return something like this:

SELECT BOTTOM 1 Column_C
FROM Table1
WHERE Column_A = something

Any help would be greatly appreciated. Thank you!

gerardkcohen:
Is there anyway I can return only the last row of a query, like TOP does for the top most items?
I would like to return something like this:
SELECT BOTTOM 1 Column_C
FROM Table1
WHERE Column_A = something
Any help would be greatly appreciated. Thank you!

gerardkcohen --

Use a subquery. Get IDs in the order you want using an "order by desc". Get the IDs you want using a "TOP". Get the data you want using a "select * ... where in". And so on.

Here is some sample code.

use northwind
go

--get all the rows, in order, to see what we are working with...
select * from Shippers order by ShipperID asc

/* output...

ShipperID CompanyName Phone
---- ------------ --------
1 Speedy Express (503) 555-9831
2 United Package (503) 555-3199
3 Federal Shipping (503) 555-9931

(3 row(s) affected)
*/

--get the top 2 rows
select * from Shippers where ShipperID in (select top 2 ShipperID from Shippers order by ShipperID asc)

/* output...

ShipperID CompanyName Phone
---- ------------ --------
1 Speedy Express (503) 555-9831
2 United Package (503) 555-3199

(2 row(s) affected)
*/

--get the bottom 2 rows
select * from Shippers where ShipperID in (select top 2 ShipperID from Shippers order by ShipperID desc)

/* output...

ShipperID CompanyName Phone
---- ------------ --------
3 Federal Shipping (503) 555-9931
2 United Package (503) 555-3199

(2 row(s) affected)
*/

HTH.

Thank you.

-- Mark Kamoski

|||

You can still use TOP 1 to get the bottom 1 by adding ORDER BY Column_C DESC

Like:

SELECT TOP 1 Column_C
FROM Table1
WHERE Column_A = something

ORDER BY Column_C DESC

--edited

|||

Yes remember to changeBOTTOM -> top

SELECTtop 1 Column_C
FROM Table1
WHERE Column_A = something

ORDER BY Column_C DESC

Reverse Bill Of Materials Query?

Hello!
We have a bill of materials table with a classic parent/child relationships.
What I need to be able to do is to take a specific part number and return
the highest level parent for that part number.
For instance, If part number A is used as a component in part number B, and
part number B is then used in another component called C, then the highest
level parent for part number A is C.
so the result of the query when ran against part number A would be C
How can I set up such a query?
Thanks
JoeBelow is a solution using a user-defined function. If this
is a frequent requirement, you may want to consider alternate
ways of modeling your hierarchy. If you search groups.google.co.uk
or www.google.com for hierarchy+itzik+sqlserver you'll find some nice
ideas.
-- Original thread at http://groups.google.co.uk/groups?q=A9B05D_C5E784
CREATE TABLE Employee (
pk int not null primary key,
parent int
)
go
INSERT INTO Employee VALUES (1,NULL)
INSERT INTO Employee VALUES (2,NULL)
INSERT INTO Employee VALUES (3,1)
INSERT INTO Employee VALUES (4,2)
INSERT INTO Employee VALUES (5,4)
go
CREATE FUNCTION rootPK(
@.pk INT
) RETURNS INT
AS
BEGIN
DECLARE @.parent INT, @.this INT
SET @.this = NULL
SET @.parent = @.pk
WHILE @.parent IS NOT NULL BEGIN
SELECT
@.this
= pk
, @.parent
= parent
FROM
Employee
WHERE
pk
= @.parent
END
RETURN @.this
END
go
ALTER TABLE Employee ADD rootPK as dbo.rootPK(pk)
go
SELECT * FROM Employee
go
-- drop table Employee
-- drop function dbo.rootPK
-- Steve Kass
-- Drew University
Joe Williams wrote:

> Hello!
> We have a bill of materials table with a classic parent/child relationship
s.
> What I need to be able to do is to take a specific part number and return
> the highest level parent for that part number.
> For instance, If part number A is used as a component in part number B, an
d
> part number B is then used in another component called C, then the highest
> level parent for part number A is C.
> so the result of the query when ran against part number A would be C
> How can I set up such a query?
> Thanks
> Joe
>|||There is also a good example at:
http://msdn.microsoft.com/library/d...r />
_5yk3.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Joe Williams" <Joe@.anywhere.com> schrieb im Newsbeitrag
news:%234YOY0jWFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hello!
> We have a bill of materials table with a classic parent/child
> relationships. What I need to be able to do is to take a specific part
> number and return the highest level parent for that part number.
> For instance, If part number A is used as a component in part number B,
> and part number B is then used in another component called C, then the
> highest level parent for part number A is C.
> so the result of the query when ran against part number A would be C
> How can I set up such a query?
> Thanks
> Joe
>

Reusing parameters in a subreport.

Is it possible to reuse parameters from a primary query to trigger the query in a subreport?

My primary report does totals sold grouped by salesman code, and now we need grand totals per salesmanin the report footer.

So what I did was create a subreport that gives me exactly what I need. Now for my problem. We use the visual basic ActiveX control for our users to query the database for the report.

It was fine before since all the user had to do was enter a from_date, to_date, and a state parameter and the report would do it's thing. Now with the subreport it asks for...

from_date
to_date
state
from_date (SalesManTotals)
to_date (SalesManTotals)
state (SalesManTotals)

So our users are forced to enter the same parameters twice. Once from the VB interface, and then a second time from a Crystal Reports box requesting the same parameters for the subreport.

How can I tell the report to use the first set of parameters for both reports without being requested to enter them a second time for the subreport?Try linking those parameter fields to the field in the subreport that contains that data

Re-using Identity achieved after INSERT query

Hi all,
I'm using an INSERT query to add records to a table with a defined
Identity column.
The query looks like this:
INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
9,1);
SELECT SCOPE_IDENTITY();
The return value is Text_ID, and I'm using the it for another insert
query:
INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,Text_ID,1);
SELECT SCOPE_IDENTITY();
Is there a way to combine the two queries into a single query ?
Something like this:
INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
9,1);
SELECT SCOPE_IDENTITY() = Text_ID;
INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,Text_ID,1);
SELECT SCOPE_IDENTITY();
Thanks to all.You can use variable for this.
Declare @.TextID int
INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',9,1);
SELECT @.TextID = SCOPE_IDENTITY();
INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,@.TextID ,1);
...
MC
"Avital" <avital.chissick@.gmail.com> wrote in message
news:1193566518.353471.43280@.v3g2000hsg.googlegroups.com...
> Hi all,
> I'm using an INSERT query to add records to a table with a defined
> Identity column.
> The query looks like this:
> INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
> 9,1);
> SELECT SCOPE_IDENTITY();
> The return value is Text_ID, and I'm using the it for another insert
> query:
> INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,Text_ID,1);
> SELECT SCOPE_IDENTITY();
> Is there a way to combine the two queries into a single query ?
> Something like this:
> INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
> 9,1);
> SELECT SCOPE_IDENTITY() = Text_ID;
> INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,Text_ID,1);
> SELECT SCOPE_IDENTITY();
> Thanks to all.
>|||I think you just need to use a variable:
DECLARE @.TextId INT
INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
9,1);
SELECT @.TextId = SCOPE_IDENTITY();
INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,@.Text_ID,1);
SELECT SCOPE_IDENTITY();
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Avital" <avital.chissick@.gmail.com> wrote in message
news:1193566518.353471.43280@.v3g2000hsg.googlegroups.com...
> Hi all,
> I'm using an INSERT query to add records to a table with a defined
> Identity column.
> The query looks like this:
> INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
> 9,1);
> SELECT SCOPE_IDENTITY();
> The return value is Text_ID, and I'm using the it for another insert
> query:
> INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,Text_ID,1);
> SELECT SCOPE_IDENTITY();
> Is there a way to combine the two queries into a single query ?
> Something like this:
> INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
> 9,1);
> SELECT SCOPE_IDENTITY() = Text_ID;
> INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,Text_ID,1);
> SELECT SCOPE_IDENTITY();
> Thanks to all.
>

Friday, March 9, 2012

reuse subquery results in where clause (newbie)

How can I reuse the result of a subquery in the same query where-clause
without having to duplicate the subquery itself? Say I have a query like:
select a,b
(select count(c) from MyOtherTable where MyOtherTable.d = a) as
someCount
from MyTable
order by someCount
and I want to filter the rows by someCount: if I add a clause like:
where someCount > 5
I get a syntax error (invalid column name). How can I refer to the
calculated field someCount without repeating the subquery as a whole?
Thanks!Dan
select <column lists> from
(
select a,b
(select count(c) from MyOtherTable where MyOtherTable.d = a) as
someCount
from MyTable
) as Der
where someCount >5
order by someCount
"Dan" <fusid@.iol.it> wrote in message
news:epI8jZRDFHA.2232@.TK2MSFTNGP14.phx.gbl...
> How can I reuse the result of a subquery in the same query where-clause
> without having to duplicate the subquery itself? Say I have a query like:
> select a,b
> (select count(c) from MyOtherTable where MyOtherTable.d = a) as
> someCount
> from MyTable
> order by someCount
> and I want to filter the rows by someCount: if I add a clause like:
> where someCount > 5
> I get a syntax error (invalid column name). How can I refer to the
> calculated field someCount without repeating the subquery as a whole?
> Thanks!
>|||Dan wrote:
> How can I reuse the result of a subquery in the same query
> where-clause without having to duplicate the subquery itself? Say I
> have a query like:
> select a,b
> (select count(c) from MyOtherTable where MyOtherTable.d = a) as
> someCount
> from MyTable
> order by someCount
> and I want to filter the rows by someCount: if I add a clause like:
> where someCount > 5
> I get a syntax error (invalid column name). How can I refer to the
> calculated field someCount without repeating the subquery as a whole?
>
Move the subquery to your FROM clause:
select a,b, someCount
from MyTable t inner join
(select d, count(c) someCount from MyOtherTable
where MyOtherTable GROUP BY d) q
ON t.a = q.d
order by someCount
You may find this enlightening:
http://groups-beta.google.com/group...09662
c8
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Reuse of Recursive Queries

I have a recursive query which I use to retrieve a set of identifiers. Something like:

WITH Set (Id)
AS (/* recursive code */)
SELECT Item FROM MyTable T
WHERE T.ItemId = ANY (SELECT Id FROM Set)

What I would like to achieve is to be able to reuse the code in bold in another procedure without duplicating the code (ie. reuse the query building up the set).

What I've done is to create a user-defined function which returns a TABLE parameter. So now, I have something like:

SELECT Item FROM MyTable T
WHERE T.ItemId = ANY(SELECT Id FROM MyFunction())

My question: is this the righ way of doing it? Does the use of a function incurs any relevant performance cost?

Thanks for advice.TVF would be fine. Another option is to convert it into a view.|||If you are making it as a TVF, then make sure it is inline otherwise you will have performance issues.

Friday, March 30, 2012

Rogue Cursor

Wednesday, March 28, 2012

ROBUST PLAN error

ROBUST PLAN error

Friday, March 23, 2012

Right outer join problem

Right characters from ~

Right Alignment in SQL

Right Alignment in SQL

Wednesday, March 21, 2012

Rewriting left joins

Rewriting a query causing lock escalation

Rewriting a query causing lock escalation

Rewriting a query causing lock escalation

Rewrite a query efficiently

Rewite PL/SQL query in SQL Server 2005

Tuesday, March 20, 2012

Revoke View any database from public in MSSQL 2005

Monday, March 12, 2012

Reverse equivalent to TOP

Reverse Bill Of Materials Query?

Reusing parameters in a subreport.

Re-using Identity achieved after INSERT query

Friday, March 9, 2012

reuse subquery results in where clause (newbie)

Reuse of Recursive Queries

Null values

Blog Archive

About Me