Output 10 rows

Hi! I am a newbie, need to make a report with only 10 rows, and not the whole result. How do I actually specify the output as 10 rows?
Thank you so much in advance!

Depends on your exact circumstances.

If you want show 10 per page then you need to

put a list control in the body|||Thank you som much!

SELECT TOP 10 SUM(cd.price) as Sales, ... gave me a list of the most sold CD's + sales figures. How if I want a list including numbers (1 Elton John, 2 Madonna 3, 4, 5, 6, 7,8,9,10etc)
How do I make a list and a sequence of numbers on a report?

Moreover I wonder how to define sales per age-groups? I have a file "age" of customers, need to create groups(<16, 16-20, 21-30 etc) and select sales per group. How do I do this?
|||

curiousss wrote:

Moreover I wonder how to define sales per age-groups? I have a file "age" of customers, need to create groups(<16, 16-20, 21-30 etc) and select sales per group. How do I do this?

You can use a nested IIF within the group criteria to do this.

=IIF(Age < 16, "<16",IIF(Age <21,"16-20),IIF(Age<31,"21-30",">30")))

Note matching the parens can be a pain, but I am sure you will figure it out.

|||

Further to this, to create a sequence of numbers just use the RowNumber function.

=CStr(RowNumber(Nothing)) + " " + Fields!Album.Value

Output 10 rows

Hi! I am a newbie, need to make a report with only 10 rows, and not the whole result. How do I actually specify the output as 10 rows?
Thank you so much in advance!

Depends on your exact circumstances.

If you want show 10 per page then you need to

put a list control in the body|||Thank you som much!

SELECT TOP 10 SUM(cd.price) as Sales, ... gave me a list of the most sold CD's + sales figures. How if I want a list including numbers (1 Elton John, 2 Madonna 3, 4, 5, 6, 7,8,9,10etc)
How do I make a list and a sequence of numbers on a report?

Moreover I wonder how to define sales per age-groups? I have a file "age" of customers, need to create groups(<16, 16-20, 21-30 etc) and select sales per group. How do I do this?
|||

curiousss wrote:

Moreover I wonder how to define sales per age-groups? I have a file "age" of customers, need to create groups(<16, 16-20, 21-30 etc) and select sales per group. How do I do this?

You can use a nested IIF within the group criteria to do this.

=IIF(Age < 16, "<16",IIF(Age <21,"16-20),IIF(Age<31,"21-30",">30")))

Note matching the parens can be a pain, but I am sure you will figure it out.

|||

Further to this, to create a sequence of numbers just use the RowNumber function.

=CStr(RowNumber(Nothing)) + " " + Fields!Album.Value

Outer join runs differently on SQL Server and Oracle

Oracle 9i:
create table t(i integer)
Table created
insert into t values(1)
1 row inserted
select t1.i i1, t2.i i2
from t t1 left join t t2 on 1=0
0 rows selected
-- I beleive this is wrong
drop table t
Table dropped
the same query against MS SQL Server 2000:
create table t(i integer)
insert into t values(1)
select t1.i i1, t2.i i2
from t t1 left join t t2 on 1=0
i1 i2
-- --
1 NULL
-- I think this is correct
(1 row(s) affected)
drop table t
What do you thinkAK wrote:
> Oracle 9i:
> create table t(i integer)
> Table created
> insert into t values(1)
> 1 row inserted
>
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> 0 rows selected
> -- I beleive this is wrong
> drop table t
> Table dropped
> the same query against MS SQL Server 2000:
> create table t(i integer)
> insert into t values(1)
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> i1 i2
> -- --
> 1 NULL
> -- I think this is correct
> (1 row(s) affected)
> drop table t
> What do you think
I think you're using an unpatched release of Oracle 9i, whichever
release that may be (9i says NOTHING about which release or patch level
you're using):
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> create table t(i integer);
Table created.
SQL>
SQL> insert into t values(1);
1 row created.
SQL>
SQL>
SQL> select t1.i i1, t2.i i2
2 from t t1 left join t t2 on 1=0;
I1 I2
-- --
1
SQL>
The results look exactly the same to me between the two. Care to try
again, this time with a patched release of Oracle?
David Fitzjarrell|||AK schrieb:
> Oracle 9i:
> create table t(i integer)
> Table created
> insert into t values(1)
> 1 row inserted
>
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> 0 rows selected
> -- I beleive this is wrong
> drop table t
> Table dropped
> the same query against MS SQL Server 2000:
> create table t(i integer)
> insert into t values(1)
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> i1 i2
> -- --
> 1 NULL
> -- I think this is correct
> (1 row(s) affected)
> drop table t
> What do you think
>
Oracle ansi join implementation had some bugs ( much of them fixed in
recent versions/patches ), but in your case i definitely can't reproduce
your behaviour ( both on 9iR2 and 10gR1/R2)
oracle@.col-fc1-02:~/sql >sqlplus scott/tiger
SQL*Plus: Release 9.2.0.6.0 - Production on Thu Aug 11 19:19:29 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> create table t(i integer);
Table created.
SQL> insert into t values(1);
1 row created.
SQL> select t1.i i1,t2.i i2
2 from t t1 left join t t2 on 1=0;
I1 I2
-- --
1
SQL>
Here is output for 10gR1
oracle@.wks01:~> sqlplus scott/tiger
SQL*Plus: Release 10.1.0.3.0 - Production on Do Aug 11 18:05:22 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table t(i integer);
Table created.
SQL> insert into t values(1);
1 row created.
SQL> select t1.i i1,t2.i i2
2 from t t1 left join t t2 on 1=0
3 /
I1 I2
-- --
1
SQL>
Best regards
Maxim|||yep, one more reason to patch up:
Connected to:
Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production
Thanks for the feedback

Outer Join qry help!

The first query returns 46 rows (correct result set) while the second one
returns all of the records from app_type table. The second query is similar
(atleast thats what I'm trying to accomplish) to the first one, difference
being non-ansii statement.
Can someone help me figure out whats wrong with the second query?
Thanks.
qry1
select distinct ap.type,ap.subsystem
from app_type ap left outer join app_code ac on ap.type=ac.type
where ac.type is null
qry2
select distinct ap.type, ap.subsystem
from app_type ap , app_code ac
where ap.type*=ac.type
and ac.type is null
Arul,
you just found out why you should not use the depricated outer join
syntax.
Check out this posting, and see if that helps:
http://groups.google.nl/groups?selm=...utput=g plain
Gert-Jan
Arul wrote:
> The first query returns 46 rows (correct result set) while the second one
> returns all of the records from app_type table. The second query is similar
> (atleast thats what I'm trying to accomplish) to the first one, difference
> being non-ansii statement.
> Can someone help me figure out whats wrong with the second query?
> Thanks.
> qry1
> select distinct ap.type,ap.subsystem
> from app_type ap left outer join app_code ac on ap.type=ac.type
> where ac.type is null
> qry2
> select distinct ap.type, ap.subsystem
> from app_type ap , app_code ac
> where ap.type*=ac.type
> and ac.type is null

Outer Join qry help!

The first query returns 46 rows (correct result set) while the second one
returns all of the records from app_type table. The second query is similar
(atleast thats what I'm trying to accomplish) to the first one, difference
being non-ansii statement.
Can someone help me figure out whats wrong with the second query?
Thanks.
qry1
select distinct ap.type,ap.subsystem
from app_type ap left outer join app_code ac on ap.type=ac.type
where ac.type is null
qry2
select distinct ap.type, ap.subsystem
from app_type ap , app_code ac
where ap.type*=ac.type
and ac.type is nullArul,
you just found out why you should not use the depricated outer join
syntax.
Check out this posting, and see if that helps:
[url]http://groups.google.nl/groups?selm=eAtctS7DBHA.968%40tkmsftngp07&output=gplain[/u
rl]
Gert-Jan
Arul wrote:
> The first query returns 46 rows (correct result set) while the second one
> returns all of the records from app_type table. The second query is simil
ar
> (atleast thats what I'm trying to accomplish) to the first one, difference
> being non-ansii statement.
> Can someone help me figure out whats wrong with the second query?
> Thanks.
> qry1
> select distinct ap.type,ap.subsystem
> from app_type ap left outer join app_code ac on ap.type=ac.type
> where ac.type is null
> qry2
> select distinct ap.type, ap.subsystem
> from app_type ap , app_code ac
> where ap.type*=ac.type
> and ac.type is null

Outer Join qry help!

The first query returns 46 rows (correct result set) while the second one
returns all of the records from app_type table. The second query is similar
(atleast thats what I'm trying to accomplish) to the first one, difference
being non-ansii statement.
Can someone help me figure out whats wrong with the second query?
Thanks.
qry1
select distinct ap.type,ap.subsystem
from app_type ap left outer join app_code ac on ap.type=ac.type
where ac.type is null
qry2
select distinct ap.type, ap.subsystem
from app_type ap , app_code ac
where ap.type*=ac.type
and ac.type is nullArul,
you just found out why you should not use the depricated outer join
syntax.
Check out this posting, and see if that helps:
http://groups.google.nl/groups?selm=eAtctS7DBHA.968%40tkmsftngp07&output=gplain
Gert-Jan
Arul wrote:
> The first query returns 46 rows (correct result set) while the second one
> returns all of the records from app_type table. The second query is similar
> (atleast thats what I'm trying to accomplish) to the first one, difference
> being non-ansii statement.
> Can someone help me figure out whats wrong with the second query?
> Thanks.
> qry1
> select distinct ap.type,ap.subsystem
> from app_type ap left outer join app_code ac on ap.type=ac.type
> where ac.type is null
> qry2
> select distinct ap.type, ap.subsystem
> from app_type ap , app_code ac
> where ap.type*=ac.type
> and ac.type is nullsql

Outer join problem

Hi.
I mean left outer join must return all rows in the left table.
Why?
SELECT DISTINCT a.cliente, a.Expositor
FROM prm_Exp_x_PV a
LEFT OUTER JOIN prm_paneles_x_pv c on a.id_expv = c.id_expv
LEFT OUTER JOIN prompaneles d on c.panel = d.codigo
WHERE (a.cliente = '4306500007')
Returns 22 Rows. and
SELECT DISTINCT a.cliente, a.Expositor
FROM prm_Exp_x_PV a
LEFT OUTER JOIN prm_paneles_x_pv c on a.id_expv = c.id_expv
LEFT OUTER JOIN prompaneles d on c.panel = d.codigo
WHERE (a.cliente = '4306500007')
and d.grupo is null
Returns only 9 rows
I need a query that returns all rows in the prm_Exp_x_PV table and only the
rows with null grupo in the prm_paneles_x_pv.
Table a and b have a master detail relation with cero or more rows in the
detail table.
Any help?
Thanks.
Pau.Having not seen your data (and DDL for that matter) I cannot see anything
unexpected in those queries.
And if you want rows where prm_paneles_x_pv.grupo is null then use the
appropriate alias - c instead of d.
For better help post DDL and sample data.
ML|||You keep mentioning table "b". Where is it? It's not referenced by any query
.
I don't know what happened to the DDL/sample data, but I can't see it.
Please read more on this here:
http://www.aspfaq.com/etiquette.asp?id=5006
ML|||Pau, if I'm correctly guessing what you want, you need to apply the
condition in the join condition rather than the where clause ...
...
FROM
a
LEFT JOIN b ON a.x = b.x AND b.y IS NULL
...
"Pau Dom_nguez" wrote:

> Ok. here it is the DDL and data.
> The field grupo is a field of table d used to filter the matching rows in
> table b.
> I need a query that returns all rows in the prm_Exp_x_PV table and only th
e
> rows from the prm_paneles_x_pv that have null grupo in prompaneles.
> Table a and b have a master detail relation with cero or more rows in the
> detail table.
> SELECT DISTINCT a.cliente, a.Expositor
> FROM prm_Exp_x_PV a
> LEFT OUTER JOIN prm_paneles_x_pv c on a.id_expv = c.id_ex
pv
> LEFT OUTER JOIN prompaneles d on c.panel = d.codigo
> WHERE a.cliente = '4306500007'
> and d.grupo is null
> What is wrong in this select?
> Thanks ML.
> Pau.
> "ML" <ML@.discussions.microsoft.com> escribió en el mensaje
> news:54B5E0ED-DD74-4A75-B4BD-F95FE6915DF5@.microsoft.com...
>
>|||Thanks you very much KH.
Now it works fine.
Pau.
"KH" <KH@.discussions.microsoft.com> escribi en el mensaje
news:3989ADA9-5111-4927-AD1A-64192018DCC9@.microsoft.com...
> Pau, if I'm correctly guessing what you want, you need to apply the
> condition in the join condition rather than the where clause ...
> ...
> FROM
> a
> LEFT JOIN b ON a.x = b.x AND b.y IS NULL
> ...
>
>
> "Pau Domnguez" wrote:
>|||Thank you for nothing ML.
You are burned, this work is not for you.
Pau.
"ML" <ML@.discussions.microsoft.com> escribi en el mensaje
news:93A4AF26-5094-4C10-863D-AC5C7E39A597@.microsoft.com...
> You keep mentioning table "b". Where is it? It's not referenced by any
> query.
> I don't know what happened to the DDL/sample data, but I can't see it.
> Please read more on this here:
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> ML

Outer Join not working

"Steve Kass" <skass@.drew.edu> wrote in message
news:evEOZswGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Only two rows of the OUTER JOIN satisfy the WHERE clause of
> the query, (where JobID = 3720). If you want all the rows of the
> outer join, including those with JobID values other than 3720,
> don't include a WHERE clause to restrict what you get to rows with
> JobID = 3720. You may want that condition in the ON clause
> of the outer join, but since you only tell us what you don't want,
> not what you do want, I won't speculate.
I thought I did tell you what I wanted.

> In short, a SELECT query's WHERE clause is always enforced.
> The ON clause of an OUTER JOIN is the one that may not be
> true for all the result set rows.
That was what I was looking for. I always have trouble with Outer Joins.
What worked was:
select S.PositionID,s.QuestionUnique,Question,QuestionType,
Weight,AnswerTime,jobID
from SkillsTest as S
left outer join ApplicantTest j
on (j.questionUnique = s.questionUnique and JobID = 3720)
where PositionID = 25
If I leave out the where clause, it almost works, but I also get all the
rows with any PositionID, so I needed to add that.
I am still not sure why I had to move the JobID test into the "on" clause,
but it worked. I will have to study it more to understand it.
Thanks,
Tom
> SK
>
> tshad wrote:
>
,
,
53
12
from
all
(as
would
s.questionUnique)Might interest you:
http://support.microsoft.com/defaul...kb;en-us;176480
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"tshad" <tfs@.dslextreme.com> wrote in message
news:ur$XX5wGFHA.3316@.TK2MSFTNGP14.phx.gbl...
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:evEOZswGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> I thought I did tell you what I wanted.
>
> That was what I was looking for. I always have trouble with Outer Joins.
> What worked was:
> select S.PositionID,s.QuestionUnique,Question,QuestionType,
> Weight,AnswerTime,jobID
> from SkillsTest as S
> left outer join ApplicantTest j
> on (j.questionUnique = s.questionUnique and JobID = 3720)
> where PositionID = 25
> If I leave out the where clause, it almost works, but I also get all the
> rows with any PositionID, so I needed to add that.
> I am still not sure why I had to move the JobID test into the "on" clause,
> but it worked. I will have to study it more to understand it.
> Thanks,
> Tom
>
first.
NULL
> ,
NULL
> ,
2
7
> 53
8
1
1
8
0
0
> 12
2
Answer
> from
want
> all
> (as
> would
> s.questionUnique)
>|||On Thu, 24 Feb 2005 23:49:52 -0800, tshad wrote:
(snip)
>I am still not sure why I had to move the JobID test into the "on" clause,
>but it worked. I will have to study it more to understand it.
Hi Tom,
That's because of the order in which a SELECT statement is evaluated.
The order of evaluation whould, in theory (most DBMS's will reshuffle to
optimize, as long as it doesn't change the end result), be as follows:
1. Evaluate FROM clause. Make a temporary result set that holds all
columns from all tables in the FROM clause, with all the rows that
satisfy the ON conditions.
Rows that don't match the ON condition are not included if it's an INNER
JOIN; for OUTER JOINS, the row from the outer table will be retained
with NULL values substituted for the columns from the inner table.
2. Evaluate WHERE clause. Check each row in the temporary result set
from the previous step against the WHERE condition. The row is retained
only if the WHERE condition evaluates to TRUE. If it evaluates to FALSE
or UNKNOWN, the row is removed from the result set.
3. After this, the GROUP BY, HAVING, SELECT and ORDER BY are processed
(in that order) - but these are not relevant for your question.
In your original question, you had a WHERE clause
where JobID = 3720
where the JobID column is from the inner table of an outer join. For all
rows retained because of the outer join, JobID is NULL. By definition,
the expression NULL = 3270 evaluates to FALSE. Therefor, all rows
retained by the outer join are subsequently removed by the where clause,
effectively changing your query to an inner join.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

OUTER JOIN issue

Hi all,
I have two tables (for example, table1, table2) where table1 holds thesame data as table2 but also has other rows that are no contained intable2.
Now if I performed the following query...
SELECT table1.name
FROM table1
LEFT OUTER JOIN table2 ON (RTRIM(LOWER(table1..table_name)) = RTRIM(LOWER(table2.table_name)))
... I expect to get the rows that are not contained in both tables. Butfor some reason I don't get this. I get all the rows from tabel1.
Is my thinking of what theLEFT OUTER JOIN query wrong, or is the query wrong?
To get around my problem, I had to do the following
SELECT table1.name
FROM table1
WHERE table1.table_name not IN (SELECT table_name FROM table2)
I would have preferred to have solved this with theLEFT OUTER JOIN though
Thanks
Tryst
I think you are a little confused about outer joins...
Left/Right Outer joins return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions
Say I have
Table1
Id Name Age
-----
1 a 20
2 b 30
3 c 22
Table2
Id Sex
---
1 M
2 F
Say I want id,name,age,sex(if available) then I would use left join
Select id,name,age,sex from table1 left join table2 on table1.id = table2.id
This will return
Id name age sex
------
1 a 20 M
2 b 30 F
3 c 22 <null>
|||You need to add a WHERE condition to return the results you want from the LEFT OUTER JOIN:

SELECT table1.name
FROM table1
LEFT OUTER JOIN table2 ON (RTRIM(LOWER(table1..table_name)) = RTRIM(LOWER(table2.table_name)))
WHERE table2.table_name IS NULL

What would probably perform better for you is this:

SELECT table1.name
FROM table1
WHERE NOT EXISTS (SELECT NULL FROM table2 WHERE table1.table_name = table2)

Check both execution plans/execution time in Query Analyzer to see which one performs better.

|||Ok, thanks guys.
Why is the inner SELECT query selecting NULL from table2? Whats it use?
Tryst
|||

Tryst wrote:

Why is the inner SELECT query selecting NULL from table2? Whats it use?

The list of column names is immaterial for the EXISTS. What isimportant is the WHERE condition. I have a habit of using NULL,partly because I read somewhere that it uses less resources than a * ora specific column (I am not sure whether or not that is true).

Outer Join Help!

The first query below returns 46 rows (correct result set) while the second
one
returns all of the records from app_type table. The second query is similar
(atleast thats what I'm trying to accomplish) to the first one, difference
being non-ansii statement.
Can someone help me figure out whats wrong with the second query?
Thanks.
qry1
select distinct ap.type,ap.subsystem
from app_type ap left outer join app_code ac on ap.type=ac.type
where ac.type is null
qry2
select distinct ap.type, ap.subsystem
from app_type ap , app_code ac
where ap.type*=ac.type
and ac.type is nullPlease do not post the same question independently to multiple newsgroups.
Your original post has already been answered in .server.
"Arul" <Arul@.discussions.microsoft.com> wrote in message
news:68D344E0-2384-4C56-95EE-EDF80DB5869A@.microsoft.com...
> The first query below returns 46 rows (correct result set) while the
second
> one
> returns all of the records from app_type table. The second query is
similar
> (atleast thats what I'm trying to accomplish) to the first one, difference
> being non-ansii statement.
> Can someone help me figure out whats wrong with the second query?
> Thanks.
> qry1
> select distinct ap.type,ap.subsystem
> from app_type ap left outer join app_code ac on ap.type=ac.type
> where ac.type is null
> qry2
> select distinct ap.type, ap.subsystem
> from app_type ap , app_code ac
> where ap.type*=ac.type
> and ac.type is null
>|||Arul,
The second query is not the equivalent of the first. It should be:
http://www.microsoft.com/sql/techin...ment/July23.asp
AMB
"Arul" wrote:

> The first query below returns 46 rows (correct result set) while the secon
d
> one
> returns all of the records from app_type table. The second query is simil
ar
> (atleast thats what I'm trying to accomplish) to the first one, difference
> being non-ansii statement.
> Can someone help me figure out whats wrong with the second query?
> Thanks.
> qry1
> select distinct ap.type,ap.subsystem
> from app_type ap left outer join app_code ac on ap.type=ac.type
> where ac.type is null
> qry2
> select distinct ap.type, ap.subsystem
> from app_type ap , app_code ac
> where ap.type*=ac.type
> and ac.type is null
>|||>> Can someone help me figure out whats wrong with the second query? <<
It is not SQL! There is no such operator as *=. And when it was a
dialect, it never worked right anyway. Why are you using it or are you
trtying migrate old code?

outer join and indexes

Hello,
I have query joining several tables, the last table is joined with LEFT
JOIN. The last table
has more then million rows and execution plan shows table scan on it. I have
indexed columns
on which the join is made. If I replace LEFT JOIN with INNER JOIN, index is
used and execution
takes few seconds but with LEFT JOIN there is a table scan , so the
execution
takes several minutes. Does using outer joins turn off indexes? Missed I
something?
What is the reason for such behavior?
(I use SQL Server 2000 Developer edition SP3)
Any suggestion appretiated
eXavierIf your using a LEFT JOIN then you just told SQL Server to return every row
from the table on the left regardless of if it has a match on the right or
not. In that case it makes no sense to use the index when it must read all
the rows anyway.
--
Andrew J. Kelly
SQL Server MVP
"eXavier" <hurta@.email.cz> wrote in message
news:biv35p$1ri3$1@.ns.felk.cvut.cz...
> Hello,
> I have query joining several tables, the last table is joined with LEFT
> JOIN. The last table
> has more then million rows and execution plan shows table scan on it. I
have
> indexed columns
> on which the join is made. If I replace LEFT JOIN with INNER JOIN, index
is
> used and execution
> takes few seconds but with LEFT JOIN there is a table scan , so the
> execution
> takes several minutes. Does using outer joins turn off indexes? Missed I
> something?
> What is the reason for such behavior?
> (I use SQL Server 2000 Developer edition SP3)
> Any suggestion appretiated
> eXavier
>|||[posted and mailed, please reply in news]
eXavier (hurta@.email.cz) writes:
> I have query joining several tables, the last table is joined with LEFT
> JOIN. The last table has more then million rows and execution plan shows
> table scan on it. I have indexed columns on which the join is made. If I
> replace LEFT JOIN with INNER JOIN, index is used and execution takes few
> seconds but with LEFT JOIN there is a table scan , so the execution
> takes several minutes. Does using outer joins turn off indexes? Missed I
> something?
I think Andrew misunderstood your question. But maybe I misunderstood
Andrew. I believe this query is akin to the one you have problem with:
select *
from Customers c
left JOIN Orders o ON o.CustomerID = c.CustomerID
where c.PostalCode = '75012'
If you run this in Query Analyzer, with Show Execution Plan on, you will
see that SQL Server uses an Index Seek on Orders.
Really why the optimizer switches to table scan when you use a left
join, I cannot answer, as I don't know the tables and the indexes.
However, it is worth to keep in mind that is not always the best
strategy to use an index. Retrieval by non-clustered index, requires
SQL Server to do bookmark lookups to get the data, and this is only
effecient if you read a smaller portion of the table. At some level
table scan becomes cheaper.
When you are joining this also applies to clustered indexes. SQL
Server can use the clustered index for a nested loop join, but that
is akin to a bookmark lookup, so merge join or a hash when you scan
the table once may be better.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||It's possible I misunderstood his question but he did not provide an actual
query and he mentioned nothing of a WHERE clause. So if he uses an INNER
JOIN against a table on the right that is very small it may indeed use an
index. If he uses a LEFT JOIN with no WHERE clause it doesn't matter how big
or small the right table is, it will do a table scan unless there is an
index that is covering.
--
Andrew J. Kelly
SQL Server MVP
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93E9E4870C84FYazorman@.127.0.0.1...
> [posted and mailed, please reply in news]
> eXavier (hurta@.email.cz) writes:
> > I have query joining several tables, the last table is joined with LEFT
> > JOIN. The last table has more then million rows and execution plan shows
> > table scan on it. I have indexed columns on which the join is made. If I
> > replace LEFT JOIN with INNER JOIN, index is used and execution takes few
> > seconds but with LEFT JOIN there is a table scan , so the execution
> > takes several minutes. Does using outer joins turn off indexes? Missed I
> > something?
> I think Andrew misunderstood your question. But maybe I misunderstood
> Andrew. I believe this query is akin to the one you have problem with:
> select *
> from Customers c
> left JOIN Orders o ON o.CustomerID = c.CustomerID
> where c.PostalCode = '75012'
> If you run this in Query Analyzer, with Show Execution Plan on, you will
> see that SQL Server uses an Index Seek on Orders.
> Really why the optimizer switches to table scan when you use a left
> join, I cannot answer, as I don't know the tables and the indexes.
> However, it is worth to keep in mind that is not always the best
> strategy to use an index. Retrieval by non-clustered index, requires
> SQL Server to do bookmark lookups to get the data, and this is only
> effecient if you read a smaller portion of the table. At some level
> table scan becomes cheaper.
> When you are joining this also applies to clustered indexes. SQL
> Server can use the clustered index for a nested loop join, but that
> is akin to a bookmark lookup, so merge join or a hash when you scan
> the table once may be better.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Andrew J. Kelly (sqlmvpnooospam@.shadhawk.com) writes:
> It's possible I misunderstood his question but he did not provide an
> actual query and he mentioned nothing of a WHERE clause. So if he uses
> an INNER JOIN against a table on the right that is very small it may
> indeed use an index. If he uses a LEFT JOIN with no WHERE clause it
> doesn't matter how big or small the right table is, it will do a table
> scan unless there is an index that is covering.
Yes, this will scan:
SELECT *
FROM bigtbl b
LEFT JOIN smalltbl s ON b.col = s.col
But I understood his question as that he had:
SELECT *
FROM unknowntbl u1
JOIN unknowntbl u2 ON ...
LEFT JOIN bigtbl b ON ...
And this could well use an index on bigtbl. (With some luck.)
But you are right that the question did not give much information, and
also he did in fact say that he had one small table, but that he had
several tables of unknown size. All we know is that the last one was big.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.aspsql

outer join and indexes

Hello,
I have query joining several tables, the last table is joined with LEFT
JOIN. The last table
has more then million rows and execution plan shows table scan on it. I have
indexed columns
on which the join is made. If I replace LEFT JOIN with INNER JOIN, index is
used and execution
takes few seconds but with LEFT JOIN there is a table scan , so the
execution
takes several minutes. Does using outer joins turn off indexes? Missed I
something?
What is the reason for such behavior?
(I use SQL Server 2000 Developer edition SP3)

Any suggestion appretiated

eXavierIf your using a LEFT JOIN then you just told SQL Server to return every row
from the table on the left regardless of if it has a match on the right or
not. In that case it makes no sense to use the index when it must read all
the rows anyway.

--

Andrew J. Kelly
SQL Server MVP

"eXavier" <hurta@.email.cz> wrote in message
news:biv35p$1ri3$1@.ns.felk.cvut.cz...
> Hello,
> I have query joining several tables, the last table is joined with LEFT
> JOIN. The last table
> has more then million rows and execution plan shows table scan on it. I
have
> indexed columns
> on which the join is made. If I replace LEFT JOIN with INNER JOIN, index
is
> used and execution
> takes few seconds but with LEFT JOIN there is a table scan , so the
> execution
> takes several minutes. Does using outer joins turn off indexes? Missed I
> something?
> What is the reason for such behavior?
> (I use SQL Server 2000 Developer edition SP3)
> Any suggestion appretiated
> eXavier|||[posted and mailed, please reply in news]

eXavier (hurta@.email.cz) writes:
> I have query joining several tables, the last table is joined with LEFT
> JOIN. The last table has more then million rows and execution plan shows
> table scan on it. I have indexed columns on which the join is made. If I
> replace LEFT JOIN with INNER JOIN, index is used and execution takes few
> seconds but with LEFT JOIN there is a table scan , so the execution
> takes several minutes. Does using outer joins turn off indexes? Missed I
> something?

I think Andrew misunderstood your question. But maybe I misunderstood
Andrew. I believe this query is akin to the one you have problem with:

select *
from Customers c
left JOIN Orders o ON o.CustomerID = c.CustomerID
where c.PostalCode = '75012'

If you run this in Query Analyzer, with Show Execution Plan on, you will
see that SQL Server uses an Index Seek on Orders.

Really why the optimizer switches to table scan when you use a left
join, I cannot answer, as I don't know the tables and the indexes.

However, it is worth to keep in mind that is not always the best
strategy to use an index. Retrieval by non-clustered index, requires
SQL Server to do bookmark lookups to get the data, and this is only
effecient if you read a smaller portion of the table. At some level
table scan becomes cheaper.

When you are joining this also applies to clustered indexes. SQL
Server can use the clustered index for a nested loop join, but that
is akin to a bookmark lookup, so merge join or a hash when you scan
the table once may be better.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||It's possible I misunderstood his question but he did not provide an actual
query and he mentioned nothing of a WHERE clause. So if he uses an INNER
JOIN against a table on the right that is very small it may indeed use an
index. If he uses a LEFT JOIN with no WHERE clause it doesn't matter how big
or small the right table is, it will do a table scan unless there is an
index that is covering.

--

Andrew J. Kelly
SQL Server MVP

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93E9E4870C84FYazorman@.127.0.0.1...
> [posted and mailed, please reply in news]
> eXavier (hurta@.email.cz) writes:
> > I have query joining several tables, the last table is joined with LEFT
> > JOIN. The last table has more then million rows and execution plan shows
> > table scan on it. I have indexed columns on which the join is made. If I
> > replace LEFT JOIN with INNER JOIN, index is used and execution takes few
> > seconds but with LEFT JOIN there is a table scan , so the execution
> > takes several minutes. Does using outer joins turn off indexes? Missed I
> > something?
> I think Andrew misunderstood your question. But maybe I misunderstood
> Andrew. I believe this query is akin to the one you have problem with:
> select *
> from Customers c
> left JOIN Orders o ON o.CustomerID = c.CustomerID
> where c.PostalCode = '75012'
> If you run this in Query Analyzer, with Show Execution Plan on, you will
> see that SQL Server uses an Index Seek on Orders.
> Really why the optimizer switches to table scan when you use a left
> join, I cannot answer, as I don't know the tables and the indexes.
> However, it is worth to keep in mind that is not always the best
> strategy to use an index. Retrieval by non-clustered index, requires
> SQL Server to do bookmark lookups to get the data, and this is only
> effecient if you read a smaller portion of the table. At some level
> table scan becomes cheaper.
> When you are joining this also applies to clustered indexes. SQL
> Server can use the clustered index for a nested loop join, but that
> is akin to a bookmark lookup, so merge join or a hash when you scan
> the table once may be better.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Andrew J. Kelly (sqlmvpnooospam@.shadhawk.com) writes:
> It's possible I misunderstood his question but he did not provide an
> actual query and he mentioned nothing of a WHERE clause. So if he uses
> an INNER JOIN against a table on the right that is very small it may
> indeed use an index. If he uses a LEFT JOIN with no WHERE clause it
> doesn't matter how big or small the right table is, it will do a table
> scan unless there is an index that is covering.

Yes, this will scan:

SELECT *
FROM bigtbl b
LEFT JOIN smalltbl s ON b.col = s.col

But I understood his question as that he had:

SELECT *
FROM unknowntbl u1
JOIN unknowntbl u2 ON ...
LEFT JOIN bigtbl b ON ...

And this could well use an index on bigtbl. (With some luck.)

But you are right that the question did not give much information, and
also he did in fact say that he had one small table, but that he had
several tables of unknown size. All we know is that the last one was big.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Out of Line Bindings for ProcessUpdate

Hello,

Is it possible to ProcessUpdate a dimension from spcecific rows in my rational DB ?

I saw (very few examples) the use of ProcessAdd with "Out of Line Bindings" for dimensions, but no one talks about ProcessUpdate. Why is that ? Is it possible ?

I haven't try it myself, but don't see why this should be a problem. Out of line binding is a property of the Process command and should work with any type of processing (there are some exceptions such as proactive caching, Linked and Rolap dimensions).

osql timeout

Hi,
I have several big tables with rows more than 25 mil rows
and to update/delete/insert data in these tables,
it can take minutes.
I use BULK Insert/DELETE/Update with osql.
While I run one of these updates,
if I try to select, it seems like both read and write get locked.
Shouldn't SQL resolve this kind of locking?

I left these to see if it gets resolved but both never returned.
So I need to kill these processes.
Does anyone have any scripts to find how long queries are running?

Also I need to make osql timeout and tried -t but it didn't work.
I used -t 1200 with DELETE in osql but it was running for more than 40
minutes. So I killed it and ran DBCC DBREINDEX on the table and re-ran
it and it worked.
Shouldn't the query get killed after 10 minutes?
What is exactly -t option for ?

thanks,(second714@.hotmail.com) writes:
> I have several big tables with rows more than 25 mil rows
> and to update/delete/insert data in these tables,
> it can take minutes.
> I use BULK Insert/DELETE/Update with osql.
> While I run one of these updates,
> if I try to select, it seems like both read and write get locked.
> Shouldn't SQL resolve this kind of locking?

If they are deadlocked, that is waiting for each other, SQL Server should
indeed detect this situation, and select one of them as a deadlock
victim.

But it may be the case that the first process is blocked by something
else, and when you try to select, the SELECT statement is blocked by
the update.

I have a tool on my web site, aba_lockinfo, which is good for looking
at locking chains. You can get it at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

> Does anyone have any scripts to find how long queries are running?

You cannot really get the time how long a certain query has been
running, but you can see when a process last submitted a batch. The
latter is included in the information returned by aba_lockinfo.

> Also I need to make osql timeout and tried -t but it didn't work.
> I used -t 1200 with DELETE in osql but it was running for more than 40
> minutes. So I killed it and ran DBCC DBREINDEX on the table and re-ran
> it and it worked.
> Shouldn't the query get killed after 10 minutes?
> What is exactly -t option for ?

I didn't have the patience to try -t 1200, but I did try this:

E:\temp>osql -E -t 10
1> WAITFOR DELAY '00:00:20'
2> go
Timeout expired

So it does seem to work. I can't say why your DELETE did not timeout, but
it might be that it has to rollback the deletion that far, and rolling
back takes longer time than executing the command.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the info!
I'll try aba_lockinfo and test timeout more with select.

thanks again,