Hi.
I'm using MS SQLServer and I have noticed that when I use aggregate
functions on queries that use outer joins, the results varies depending
on the syntax that I use. If I use the syntax:
from table1 left join table2 on table1.a = table2.a
it doesn't seem to work properly, but if I use the syntax:
from table1, table2 where table1.a *= table2.a
it seems to work fine.
I would like to know if there's something that I'm doing wrong, if this
is some bug for which there is some solution, or if just the way it is.
In order to be more clear, I will show you some simple code that reflect
that.
Suppose we have the following 2 tables whith the corresponding data:
create table p1 ( id int primary key, s_name char(40) )
insert p1 values (1, 'Rick')
insert p1 values (2, 'Mary')
insert p1 values (3, 'Peter')
create table p2 (id int, region int, sales money, primary key (id,
region))
insert p2 values (1, 1, 2000)
insert p2 values (1, 2, 1000)
insert p2 values (1, 3, 1500)
insert p2 values (3, 1, 750)
insert p2 values (3, 2, 1000)
If I use a query without aggregate functions, both syntax return the
same result:
select p1.s_name, p2.sales
from p1 left outer join p2 on p1.id = p2.id
select p1.s_name, p2.sales
from p1, p2
where p1.id *= p2.id
Both of them return:
s_namesales
Rick2000.00
Rick1000.00
Rick1500.00
MaryNull
Peter750.00
Peter1000.00
But if I use Sum(), the results are different:
select p1.s_name, sum(p2.sales) as sales
from p1 left outer join p2 on p1.id = p2.id
group by p1.s_name
s_namesales
Rick4500.00
Peter1750.00
select p1.s_name, sum(p2.sales) as sales
from p1, p2
where p1.id *= p2.id
group by p1.s_name
s_namesales
Rick4500.00
MaryNull
Peter1750.00
In my opinion, the correct result is the last one, but the syntax that
Microsoft recommends to use is the other one.
Can someone explain that to me?
Thanks in advance.
Donald
*** Sent via Developersdex http://www.codecomments.com ***
You can get the result you want with:
SELECT s_name, SUM(sales) as sales FROM (
select p1.s_name, p2.sales
from p1 left outer join p2 on p1.id = p2.id
) a
group by s_name
-- or
SELECT p1.s_name, a.sales
FROM p1
LEFT OUTER JOIN (
SELECT id, SUM(sales) AS sales
FROM p2
GROUP BY id) a
ON p1.id = a.id
-- or the simplest (but somewhat worse performing on large sets), GROUP BY
ALL:
select p1.s_name, SUM(p2.sales) AS sales
from p1 left outer join p2 on p1.id = p2.id
GROUP BY ALL p1.s_name
Joe Celko give a rundown of the disadvantages of *= here:
http://groups.google.co.uk/groups?hl...phx.gbl&rnum=7
Jacco Schalkwijk
SQL Server MVP
"Donald" <don382w@.hotmail.com> wrote in message
news:%23$ZyTiKSFHA.2132@.TK2MSFTNGP14.phx.gbl...
> Hi.
> I'm using MS SQLServer and I have noticed that when I use aggregate
> functions on queries that use outer joins, the results varies depending
> on the syntax that I use. If I use the syntax:
> from table1 left join table2 on table1.a = table2.a
> it doesn't seem to work properly, but if I use the syntax:
> from table1, table2 where table1.a *= table2.a
> it seems to work fine.
> I would like to know if there's something that I'm doing wrong, if this
> is some bug for which there is some solution, or if just the way it is.
> In order to be more clear, I will show you some simple code that reflect
> that.
> Suppose we have the following 2 tables whith the corresponding data:
> create table p1 ( id int primary key, s_name char(40) )
> insert p1 values (1, 'Rick')
> insert p1 values (2, 'Mary')
> insert p1 values (3, 'Peter')
> create table p2 (id int, region int, sales money, primary key (id,
> region))
> insert p2 values (1, 1, 2000)
> insert p2 values (1, 2, 1000)
> insert p2 values (1, 3, 1500)
> insert p2 values (3, 1, 750)
> insert p2 values (3, 2, 1000)
> If I use a query without aggregate functions, both syntax return the
> same result:
> select p1.s_name, p2.sales
> from p1 left outer join p2 on p1.id = p2.id
> select p1.s_name, p2.sales
> from p1, p2
> where p1.id *= p2.id
> Both of them return:
> s_name sales
> -- --
> Rick 2000.00
> Rick 1000.00
> Rick 1500.00
> Mary Null
> Peter 750.00
> Peter 1000.00
> But if I use Sum(), the results are different:
> select p1.s_name, sum(p2.sales) as sales
> from p1 left outer join p2 on p1.id = p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Peter 1750.00
> select p1.s_name, sum(p2.sales) as sales
> from p1, p2
> where p1.id *= p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Mary Null
> Peter 1750.00
> In my opinion, the correct result is the last one, but the syntax that
> Microsoft recommends to use is the other one.
> Can someone explain that to me?
> Thanks in advance.
> Donald
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Yes, I am running SQL-Server 2000. I will check which service pack, if
any, is installed. I already downloaded the service pack 3a to install.
Thanks for everything,
Donald
*** Sent via Developersdex http://www.codecomments.com ***
Showing posts with label joins. Show all posts
Showing posts with label joins. Show all posts
Friday, March 30, 2012
MS SQLServer: Left/Right Join doesn't work when using aggregate functions
Hi.
I'm using MS SQLServer and I have noticed that when I use aggregate
functions on queries that use outer joins, the results varies depending
on the syntax that I use. If I use the syntax:
from table1 left join table2 on table1.a = table2.a
it doesn't seem to work properly, but if I use the syntax:
from table1, table2 where table1.a *= table2.a
it seems to work fine.
I would like to know if there's something that I'm doing wrong, if this
is some bug for which there is some solution, or if just the way it is.
In order to be more clear, I will show you some simple code that reflect
that.
Suppose we have the following 2 tables whith the corresponding data:
create table p1 ( id int primary key, s_name char(40) )
insert p1 values (1, 'Rick')
insert p1 values (2, 'Mary')
insert p1 values (3, 'Peter')
create table p2 (id int, region int, sales money, primary key (id,
region))
insert p2 values (1, 1, 2000)
insert p2 values (1, 2, 1000)
insert p2 values (1, 3, 1500)
insert p2 values (3, 1, 750)
insert p2 values (3, 2, 1000)
If I use a query without aggregate functions, both syntax return the
same result:
select p1.s_name, p2.sales
from p1 left outer join p2 on p1.id = p2.id
select p1.s_name, p2.sales
from p1, p2
where p1.id *= p2.id
Both of them return:
s_name sales
-- --
Rick 2000.00
Rick 1000.00
Rick 1500.00
Mary Null
Peter 750.00
Peter 1000.00
But if I use Sum(), the results are different:
select p1.s_name, sum(p2.sales) as sales
from p1 left outer join p2 on p1.id = p2.id
group by p1.s_name
s_name sales
-- --
Rick 4500.00
Peter 1750.00
select p1.s_name, sum(p2.sales) as sales
from p1, p2
where p1.id *= p2.id
group by p1.s_name
s_name sales
-- --
Rick 4500.00
Mary Null
Peter 1750.00
In my opinion, the correct result is the last one, but the syntax that
Microsoft recommends to use is the other one.
Can someone explain that to me?
Thanks in advance.
Donald
*** Sent via Developersdex http://www.developersdex.com ***You can get the result you want with:
SELECT s_name, SUM(sales) as sales FROM (
select p1.s_name, p2.sales
from p1 left outer join p2 on p1.id = p2.id
) a
group by s_name
-- or
SELECT p1.s_name, a.sales
FROM p1
LEFT OUTER JOIN (
SELECT id, SUM(sales) AS sales
FROM p2
GROUP BY id) a
ON p1.id = a.id
-- or the simplest (but somewhat worse performing on large sets), GROUP BY
ALL:
select p1.s_name, SUM(p2.sales) AS sales
from p1 left outer join p2 on p1.id = p2.id
GROUP BY ALL p1.s_name
Joe Celko give a rundown of the disadvantages of *= here:
http://groups.google.co.uk/groups?hl=en&lr=&selm=%23Uogl7zREHA.628%40TK2MSFTNGP11.phx.gbl&rnum=7
--
Jacco Schalkwijk
SQL Server MVP
"Donald" <don382w@.hotmail.com> wrote in message
news:%23$ZyTiKSFHA.2132@.TK2MSFTNGP14.phx.gbl...
> Hi.
> I'm using MS SQLServer and I have noticed that when I use aggregate
> functions on queries that use outer joins, the results varies depending
> on the syntax that I use. If I use the syntax:
> from table1 left join table2 on table1.a = table2.a
> it doesn't seem to work properly, but if I use the syntax:
> from table1, table2 where table1.a *= table2.a
> it seems to work fine.
> I would like to know if there's something that I'm doing wrong, if this
> is some bug for which there is some solution, or if just the way it is.
> In order to be more clear, I will show you some simple code that reflect
> that.
> Suppose we have the following 2 tables whith the corresponding data:
> create table p1 ( id int primary key, s_name char(40) )
> insert p1 values (1, 'Rick')
> insert p1 values (2, 'Mary')
> insert p1 values (3, 'Peter')
> create table p2 (id int, region int, sales money, primary key (id,
> region))
> insert p2 values (1, 1, 2000)
> insert p2 values (1, 2, 1000)
> insert p2 values (1, 3, 1500)
> insert p2 values (3, 1, 750)
> insert p2 values (3, 2, 1000)
> If I use a query without aggregate functions, both syntax return the
> same result:
> select p1.s_name, p2.sales
> from p1 left outer join p2 on p1.id = p2.id
> select p1.s_name, p2.sales
> from p1, p2
> where p1.id *= p2.id
> Both of them return:
> s_name sales
> -- --
> Rick 2000.00
> Rick 1000.00
> Rick 1500.00
> Mary Null
> Peter 750.00
> Peter 1000.00
> But if I use Sum(), the results are different:
> select p1.s_name, sum(p2.sales) as sales
> from p1 left outer join p2 on p1.id = p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Peter 1750.00
> select p1.s_name, sum(p2.sales) as sales
> from p1, p2
> where p1.id *= p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Mary Null
> Peter 1750.00
> In my opinion, the correct result is the last one, but the syntax that
> Microsoft recommends to use is the other one.
> Can someone explain that to me?
> Thanks in advance.
> Donald
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Donald,
Are you running SQL-Server 2000? Make sure you have the latest service
pack installed (SP3a).
If I run the query
select p1.s_name, sum(p2.sales) as sales
from p1 left outer join p2 on p1.id = p2.id
group by p1.s_name
it correctly returns
s_name sales
--- --
Mary NULL
Peter 1750.0000
Rick 4500.0000
(3 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET
operation.
HTH,
Gert-Jan
Donald wrote:
> Hi.
> I'm using MS SQLServer and I have noticed that when I use aggregate
> functions on queries that use outer joins, the results varies depending
> on the syntax that I use. If I use the syntax:
> from table1 left join table2 on table1.a = table2.a
> it doesn't seem to work properly, but if I use the syntax:
> from table1, table2 where table1.a *= table2.a
> it seems to work fine.
> I would like to know if there's something that I'm doing wrong, if this
> is some bug for which there is some solution, or if just the way it is.
> In order to be more clear, I will show you some simple code that reflect
> that.
> Suppose we have the following 2 tables whith the corresponding data:
> create table p1 ( id int primary key, s_name char(40) )
> insert p1 values (1, 'Rick')
> insert p1 values (2, 'Mary')
> insert p1 values (3, 'Peter')
> create table p2 (id int, region int, sales money, primary key (id,
> region))
> insert p2 values (1, 1, 2000)
> insert p2 values (1, 2, 1000)
> insert p2 values (1, 3, 1500)
> insert p2 values (3, 1, 750)
> insert p2 values (3, 2, 1000)
> If I use a query without aggregate functions, both syntax return the
> same result:
> select p1.s_name, p2.sales
> from p1 left outer join p2 on p1.id = p2.id
> select p1.s_name, p2.sales
> from p1, p2
> where p1.id *= p2.id
> Both of them return:
> s_name sales
> -- --
> Rick 2000.00
> Rick 1000.00
> Rick 1500.00
> Mary Null
> Peter 750.00
> Peter 1000.00
> But if I use Sum(), the results are different:
> select p1.s_name, sum(p2.sales) as sales
> from p1 left outer join p2 on p1.id = p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Peter 1750.00
> select p1.s_name, sum(p2.sales) as sales
> from p1, p2
> where p1.id *= p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Mary Null
> Peter 1750.00
> In my opinion, the correct result is the last one, but the syntax that
> Microsoft recommends to use is the other one.
> Can someone explain that to me?
> Thanks in advance.
> Donald
> *** Sent via Developersdex http://www.developersdex.com ***
I'm using MS SQLServer and I have noticed that when I use aggregate
functions on queries that use outer joins, the results varies depending
on the syntax that I use. If I use the syntax:
from table1 left join table2 on table1.a = table2.a
it doesn't seem to work properly, but if I use the syntax:
from table1, table2 where table1.a *= table2.a
it seems to work fine.
I would like to know if there's something that I'm doing wrong, if this
is some bug for which there is some solution, or if just the way it is.
In order to be more clear, I will show you some simple code that reflect
that.
Suppose we have the following 2 tables whith the corresponding data:
create table p1 ( id int primary key, s_name char(40) )
insert p1 values (1, 'Rick')
insert p1 values (2, 'Mary')
insert p1 values (3, 'Peter')
create table p2 (id int, region int, sales money, primary key (id,
region))
insert p2 values (1, 1, 2000)
insert p2 values (1, 2, 1000)
insert p2 values (1, 3, 1500)
insert p2 values (3, 1, 750)
insert p2 values (3, 2, 1000)
If I use a query without aggregate functions, both syntax return the
same result:
select p1.s_name, p2.sales
from p1 left outer join p2 on p1.id = p2.id
select p1.s_name, p2.sales
from p1, p2
where p1.id *= p2.id
Both of them return:
s_name sales
-- --
Rick 2000.00
Rick 1000.00
Rick 1500.00
Mary Null
Peter 750.00
Peter 1000.00
But if I use Sum(), the results are different:
select p1.s_name, sum(p2.sales) as sales
from p1 left outer join p2 on p1.id = p2.id
group by p1.s_name
s_name sales
-- --
Rick 4500.00
Peter 1750.00
select p1.s_name, sum(p2.sales) as sales
from p1, p2
where p1.id *= p2.id
group by p1.s_name
s_name sales
-- --
Rick 4500.00
Mary Null
Peter 1750.00
In my opinion, the correct result is the last one, but the syntax that
Microsoft recommends to use is the other one.
Can someone explain that to me?
Thanks in advance.
Donald
*** Sent via Developersdex http://www.developersdex.com ***You can get the result you want with:
SELECT s_name, SUM(sales) as sales FROM (
select p1.s_name, p2.sales
from p1 left outer join p2 on p1.id = p2.id
) a
group by s_name
-- or
SELECT p1.s_name, a.sales
FROM p1
LEFT OUTER JOIN (
SELECT id, SUM(sales) AS sales
FROM p2
GROUP BY id) a
ON p1.id = a.id
-- or the simplest (but somewhat worse performing on large sets), GROUP BY
ALL:
select p1.s_name, SUM(p2.sales) AS sales
from p1 left outer join p2 on p1.id = p2.id
GROUP BY ALL p1.s_name
Joe Celko give a rundown of the disadvantages of *= here:
http://groups.google.co.uk/groups?hl=en&lr=&selm=%23Uogl7zREHA.628%40TK2MSFTNGP11.phx.gbl&rnum=7
--
Jacco Schalkwijk
SQL Server MVP
"Donald" <don382w@.hotmail.com> wrote in message
news:%23$ZyTiKSFHA.2132@.TK2MSFTNGP14.phx.gbl...
> Hi.
> I'm using MS SQLServer and I have noticed that when I use aggregate
> functions on queries that use outer joins, the results varies depending
> on the syntax that I use. If I use the syntax:
> from table1 left join table2 on table1.a = table2.a
> it doesn't seem to work properly, but if I use the syntax:
> from table1, table2 where table1.a *= table2.a
> it seems to work fine.
> I would like to know if there's something that I'm doing wrong, if this
> is some bug for which there is some solution, or if just the way it is.
> In order to be more clear, I will show you some simple code that reflect
> that.
> Suppose we have the following 2 tables whith the corresponding data:
> create table p1 ( id int primary key, s_name char(40) )
> insert p1 values (1, 'Rick')
> insert p1 values (2, 'Mary')
> insert p1 values (3, 'Peter')
> create table p2 (id int, region int, sales money, primary key (id,
> region))
> insert p2 values (1, 1, 2000)
> insert p2 values (1, 2, 1000)
> insert p2 values (1, 3, 1500)
> insert p2 values (3, 1, 750)
> insert p2 values (3, 2, 1000)
> If I use a query without aggregate functions, both syntax return the
> same result:
> select p1.s_name, p2.sales
> from p1 left outer join p2 on p1.id = p2.id
> select p1.s_name, p2.sales
> from p1, p2
> where p1.id *= p2.id
> Both of them return:
> s_name sales
> -- --
> Rick 2000.00
> Rick 1000.00
> Rick 1500.00
> Mary Null
> Peter 750.00
> Peter 1000.00
> But if I use Sum(), the results are different:
> select p1.s_name, sum(p2.sales) as sales
> from p1 left outer join p2 on p1.id = p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Peter 1750.00
> select p1.s_name, sum(p2.sales) as sales
> from p1, p2
> where p1.id *= p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Mary Null
> Peter 1750.00
> In my opinion, the correct result is the last one, but the syntax that
> Microsoft recommends to use is the other one.
> Can someone explain that to me?
> Thanks in advance.
> Donald
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Donald,
Are you running SQL-Server 2000? Make sure you have the latest service
pack installed (SP3a).
If I run the query
select p1.s_name, sum(p2.sales) as sales
from p1 left outer join p2 on p1.id = p2.id
group by p1.s_name
it correctly returns
s_name sales
--- --
Mary NULL
Peter 1750.0000
Rick 4500.0000
(3 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET
operation.
HTH,
Gert-Jan
Donald wrote:
> Hi.
> I'm using MS SQLServer and I have noticed that when I use aggregate
> functions on queries that use outer joins, the results varies depending
> on the syntax that I use. If I use the syntax:
> from table1 left join table2 on table1.a = table2.a
> it doesn't seem to work properly, but if I use the syntax:
> from table1, table2 where table1.a *= table2.a
> it seems to work fine.
> I would like to know if there's something that I'm doing wrong, if this
> is some bug for which there is some solution, or if just the way it is.
> In order to be more clear, I will show you some simple code that reflect
> that.
> Suppose we have the following 2 tables whith the corresponding data:
> create table p1 ( id int primary key, s_name char(40) )
> insert p1 values (1, 'Rick')
> insert p1 values (2, 'Mary')
> insert p1 values (3, 'Peter')
> create table p2 (id int, region int, sales money, primary key (id,
> region))
> insert p2 values (1, 1, 2000)
> insert p2 values (1, 2, 1000)
> insert p2 values (1, 3, 1500)
> insert p2 values (3, 1, 750)
> insert p2 values (3, 2, 1000)
> If I use a query without aggregate functions, both syntax return the
> same result:
> select p1.s_name, p2.sales
> from p1 left outer join p2 on p1.id = p2.id
> select p1.s_name, p2.sales
> from p1, p2
> where p1.id *= p2.id
> Both of them return:
> s_name sales
> -- --
> Rick 2000.00
> Rick 1000.00
> Rick 1500.00
> Mary Null
> Peter 750.00
> Peter 1000.00
> But if I use Sum(), the results are different:
> select p1.s_name, sum(p2.sales) as sales
> from p1 left outer join p2 on p1.id = p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Peter 1750.00
> select p1.s_name, sum(p2.sales) as sales
> from p1, p2
> where p1.id *= p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Mary Null
> Peter 1750.00
> In my opinion, the correct result is the last one, but the syntax that
> Microsoft recommends to use is the other one.
> Can someone explain that to me?
> Thanks in advance.
> Donald
> *** Sent via Developersdex http://www.developersdex.com ***
MS SQLServer: Left/Right Join doesn't work when using aggregate functions
Hi.
I'm using MS SQLServer and I have noticed that when I use aggregate
functions on queries that use outer joins, the results varies depending
on the syntax that I use. If I use the syntax:
from table1 left join table2 on table1.a = table2.a
it doesn't seem to work properly, but if I use the syntax:
from table1, table2 where table1.a *= table2.a
it seems to work fine.
I would like to know if there's something that I'm doing wrong, if this
is some bug for which there is some solution, or if just the way it is.
In order to be more clear, I will show you some simple code that reflect
that.
Suppose we have the following 2 tables whith the corresponding data:
create table p1 ( id int primary key, s_name char(40) )
insert p1 values (1, 'Rick')
insert p1 values (2, 'Mary')
insert p1 values (3, 'Peter')
create table p2 (id int, region int, sales money, primary key (id,
region))
insert p2 values (1, 1, 2000)
insert p2 values (1, 2, 1000)
insert p2 values (1, 3, 1500)
insert p2 values (3, 1, 750)
insert p2 values (3, 2, 1000)
If I use a query without aggregate functions, both syntax return the
same result:
select p1.s_name, p2.sales
from p1 left outer join p2 on p1.id = p2.id
select p1.s_name, p2.sales
from p1, p2
where p1.id *= p2.id
Both of them return:
s_name sales
-- --
Rick 2000.00
Rick 1000.00
Rick 1500.00
Mary Null
Peter 750.00
Peter 1000.00
But if I use Sum(), the results are different:
select p1.s_name, sum(p2.sales) as sales
from p1 left outer join p2 on p1.id = p2.id
group by p1.s_name
s_name sales
-- --
Rick 4500.00
Peter 1750.00
select p1.s_name, sum(p2.sales) as sales
from p1, p2
where p1.id *= p2.id
group by p1.s_name
s_name sales
-- --
Rick 4500.00
Mary Null
Peter 1750.00
In my opinion, the correct result is the last one, but the syntax that
Microsoft recommends to use is the other one.
Can someone explain that to me?
Thanks in advance.
Donald
*** Sent via Developersdex http://www.codecomments.com ***You can get the result you want with:
SELECT s_name, SUM(sales) as sales FROM (
select p1.s_name, p2.sales
from p1 left outer join p2 on p1.id = p2.id
) a
group by s_name
-- or
SELECT p1.s_name, a.sales
FROM p1
LEFT OUTER JOIN (
SELECT id, SUM(sales) AS sales
FROM p2
GROUP BY id) a
ON p1.id = a.id
-- or the simplest (but somewhat worse performing on large sets), GROUP BY
ALL:
select p1.s_name, SUM(p2.sales) AS sales
from p1 left outer join p2 on p1.id = p2.id
GROUP BY ALL p1.s_name
Joe Celko give a rundown of the disadvantages of *= here:
phx.gbl&rnum=7" target="_blank">http://groups.google.co.uk/groups?h...
phx.gbl&rnum=7
Jacco Schalkwijk
SQL Server MVP
"Donald" <don382w@.hotmail.com> wrote in message
news:%23$ZyTiKSFHA.2132@.TK2MSFTNGP14.phx.gbl...
> Hi.
> I'm using MS SQLServer and I have noticed that when I use aggregate
> functions on queries that use outer joins, the results varies depending
> on the syntax that I use. If I use the syntax:
> from table1 left join table2 on table1.a = table2.a
> it doesn't seem to work properly, but if I use the syntax:
> from table1, table2 where table1.a *= table2.a
> it seems to work fine.
> I would like to know if there's something that I'm doing wrong, if this
> is some bug for which there is some solution, or if just the way it is.
> In order to be more clear, I will show you some simple code that reflect
> that.
> Suppose we have the following 2 tables whith the corresponding data:
> create table p1 ( id int primary key, s_name char(40) )
> insert p1 values (1, 'Rick')
> insert p1 values (2, 'Mary')
> insert p1 values (3, 'Peter')
> create table p2 (id int, region int, sales money, primary key (id,
> region))
> insert p2 values (1, 1, 2000)
> insert p2 values (1, 2, 1000)
> insert p2 values (1, 3, 1500)
> insert p2 values (3, 1, 750)
> insert p2 values (3, 2, 1000)
> If I use a query without aggregate functions, both syntax return the
> same result:
> select p1.s_name, p2.sales
> from p1 left outer join p2 on p1.id = p2.id
> select p1.s_name, p2.sales
> from p1, p2
> where p1.id *= p2.id
> Both of them return:
> s_name sales
> -- --
> Rick 2000.00
> Rick 1000.00
> Rick 1500.00
> Mary Null
> Peter 750.00
> Peter 1000.00
> But if I use Sum(), the results are different:
> select p1.s_name, sum(p2.sales) as sales
> from p1 left outer join p2 on p1.id = p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Peter 1750.00
> select p1.s_name, sum(p2.sales) as sales
> from p1, p2
> where p1.id *= p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Mary Null
> Peter 1750.00
> In my opinion, the correct result is the last one, but the syntax that
> Microsoft recommends to use is the other one.
> Can someone explain that to me?
> Thanks in advance.
> Donald
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Yes, I am running SQL-Server 2000. I will check which service pack, if
any, is installed. I already downloaded the service pack 3a to install.
Thanks for everything,
Donald
*** Sent via Developersdex http://www.codecomments.com ***
I'm using MS SQLServer and I have noticed that when I use aggregate
functions on queries that use outer joins, the results varies depending
on the syntax that I use. If I use the syntax:
from table1 left join table2 on table1.a = table2.a
it doesn't seem to work properly, but if I use the syntax:
from table1, table2 where table1.a *= table2.a
it seems to work fine.
I would like to know if there's something that I'm doing wrong, if this
is some bug for which there is some solution, or if just the way it is.
In order to be more clear, I will show you some simple code that reflect
that.
Suppose we have the following 2 tables whith the corresponding data:
create table p1 ( id int primary key, s_name char(40) )
insert p1 values (1, 'Rick')
insert p1 values (2, 'Mary')
insert p1 values (3, 'Peter')
create table p2 (id int, region int, sales money, primary key (id,
region))
insert p2 values (1, 1, 2000)
insert p2 values (1, 2, 1000)
insert p2 values (1, 3, 1500)
insert p2 values (3, 1, 750)
insert p2 values (3, 2, 1000)
If I use a query without aggregate functions, both syntax return the
same result:
select p1.s_name, p2.sales
from p1 left outer join p2 on p1.id = p2.id
select p1.s_name, p2.sales
from p1, p2
where p1.id *= p2.id
Both of them return:
s_name sales
-- --
Rick 2000.00
Rick 1000.00
Rick 1500.00
Mary Null
Peter 750.00
Peter 1000.00
But if I use Sum(), the results are different:
select p1.s_name, sum(p2.sales) as sales
from p1 left outer join p2 on p1.id = p2.id
group by p1.s_name
s_name sales
-- --
Rick 4500.00
Peter 1750.00
select p1.s_name, sum(p2.sales) as sales
from p1, p2
where p1.id *= p2.id
group by p1.s_name
s_name sales
-- --
Rick 4500.00
Mary Null
Peter 1750.00
In my opinion, the correct result is the last one, but the syntax that
Microsoft recommends to use is the other one.
Can someone explain that to me?
Thanks in advance.
Donald
*** Sent via Developersdex http://www.codecomments.com ***You can get the result you want with:
SELECT s_name, SUM(sales) as sales FROM (
select p1.s_name, p2.sales
from p1 left outer join p2 on p1.id = p2.id
) a
group by s_name
-- or
SELECT p1.s_name, a.sales
FROM p1
LEFT OUTER JOIN (
SELECT id, SUM(sales) AS sales
FROM p2
GROUP BY id) a
ON p1.id = a.id
-- or the simplest (but somewhat worse performing on large sets), GROUP BY
ALL:
select p1.s_name, SUM(p2.sales) AS sales
from p1 left outer join p2 on p1.id = p2.id
GROUP BY ALL p1.s_name
Joe Celko give a rundown of the disadvantages of *= here:
phx.gbl&rnum=7" target="_blank">http://groups.google.co.uk/groups?h...
phx.gbl&rnum=7
Jacco Schalkwijk
SQL Server MVP
"Donald" <don382w@.hotmail.com> wrote in message
news:%23$ZyTiKSFHA.2132@.TK2MSFTNGP14.phx.gbl...
> Hi.
> I'm using MS SQLServer and I have noticed that when I use aggregate
> functions on queries that use outer joins, the results varies depending
> on the syntax that I use. If I use the syntax:
> from table1 left join table2 on table1.a = table2.a
> it doesn't seem to work properly, but if I use the syntax:
> from table1, table2 where table1.a *= table2.a
> it seems to work fine.
> I would like to know if there's something that I'm doing wrong, if this
> is some bug for which there is some solution, or if just the way it is.
> In order to be more clear, I will show you some simple code that reflect
> that.
> Suppose we have the following 2 tables whith the corresponding data:
> create table p1 ( id int primary key, s_name char(40) )
> insert p1 values (1, 'Rick')
> insert p1 values (2, 'Mary')
> insert p1 values (3, 'Peter')
> create table p2 (id int, region int, sales money, primary key (id,
> region))
> insert p2 values (1, 1, 2000)
> insert p2 values (1, 2, 1000)
> insert p2 values (1, 3, 1500)
> insert p2 values (3, 1, 750)
> insert p2 values (3, 2, 1000)
> If I use a query without aggregate functions, both syntax return the
> same result:
> select p1.s_name, p2.sales
> from p1 left outer join p2 on p1.id = p2.id
> select p1.s_name, p2.sales
> from p1, p2
> where p1.id *= p2.id
> Both of them return:
> s_name sales
> -- --
> Rick 2000.00
> Rick 1000.00
> Rick 1500.00
> Mary Null
> Peter 750.00
> Peter 1000.00
> But if I use Sum(), the results are different:
> select p1.s_name, sum(p2.sales) as sales
> from p1 left outer join p2 on p1.id = p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Peter 1750.00
> select p1.s_name, sum(p2.sales) as sales
> from p1, p2
> where p1.id *= p2.id
> group by p1.s_name
> s_name sales
> -- --
> Rick 4500.00
> Mary Null
> Peter 1750.00
> In my opinion, the correct result is the last one, but the syntax that
> Microsoft recommends to use is the other one.
> Can someone explain that to me?
> Thanks in advance.
> Donald
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Yes, I am running SQL-Server 2000. I will check which service pack, if
any, is installed. I already downloaded the service pack 3a to install.
Thanks for everything,
Donald
*** Sent via Developersdex http://www.codecomments.com ***
Monday, March 12, 2012
MS SQL Joins
Hi Folks,
I writing a store procedure, the first three parts work pretty well. The last select statement has about 8 outer joins in it. every time I run the store procedure, I get an error message for the last part. Below are the error message and the store procedure:
Store Procedure:
--Create Procedure dbo.IMS_Donation
--AS
Select Distinct D_VST_ID as 'DRWLOC_ID', D_VST_INSTID as 'DRWLOC_INSTID'
Into Donor_Visit1
From DNR_VST_DB_REC
Where D_VST_DATE Between 20010101 AND 20040512
AND D_VST_DONTYP in ('AP', 'WB', 'RP', 'E2', 'E1')
AND D_VST_STATUS = 'DN'
ORDER BY D_VST_ID
GO
SELECT DRWLOC_ID as 'COUNT_ID', DRWLOC_INSTID as 'COUNT_INSTID',
count(*) as 'COUNT_VISITS'
INTO Donor_Visit2
FROM DNR_VST_DB_REC, Donor_Visit1
Where D_VST_ID = DRWLOC_ID
AND NOT EXISTS (Select R_DCC_ID
From REC_DCC_DB_REC
Where R_DCC_ID = DRWLOC_ID
AND R_DCC_INSTID = DRWLOC_INSTID
AND R_DCC_CALLCD = 'DC')
GROUP BY DRWLOC_ID, DRWLOC_INSTID
GO
SELECT DVT1.DRWLOC_ID as'COMP_ID', CMP.l_CMP_UNITNO as 'COMP_UNITID',
CMP.L_CMP_INSTID as 'COMP_INSTID', count(*) as 'COMP_COMPTOT'
INTO Donor_Visit3
FROM LAB_CMP_DB_REC CMP, Donor_Visit1 DVT1, DNR_VST_DB_REC VST, CMP_VST_Jct CVT
WHERE CMP.L_CMP_INSTID = DVT1.DRWLOC_INSTID
AND VST.D_VST_ID = DVT1.DRWLOC_ID
AND VST.D_VST_UNITNO = CVT.L_CMP_UNITNO
AND CMP.L_CMP_UNITNO = CVT.L_CMP_UNITNO
AND CMP.L_CMP_STATCD != 'MOD'
AND CMP.L_CMP_CMPCD NOT IN ('INC', 'EMTY')
AND VST.D_VST_DATE BETWEEN 20010101 AND 20040512
AND VST.D_VST_STATUS = 'DN'
GROUP BY DVT1.DRWLOC_ID, CMP.L_CMP_UNITNO, CMP.L_CMP_INSTID
GO
SELECT DISTINCT
NAM.N_NAM_ID AS 'ID1',
NAM.N_NAM_INSTID AS 'INSTID1',
NAM.N_NAM_FNAME AS 'FNAME1',
NAM.N_NAM_MINITIAL AS 'MINITIAL1',
NAM.N_NAM_LNAME AS 'LNAME1',
PER.N_PER_BIRTH AS 'BIRTH1',
ADR.N_ADR_ADDR1 AS 'ADDR1',
ADR.N_ADR_ADDR2 AS 'ADDR2',
ADR.N_ADR_CITY AS 'CITY1',
ADR.N_ADR_STATE AS 'STATE1',
SUBSTRING(ADR.N_ADR_ZIP, 1,5) AS 'ZIP1',
PER.N_PER_EMAIL AS 'EMAIL1',
PER.N_PER_GENDER AS 'GENDER1',
PHNA.N_PHN_AREACD AS 'AREAD1',
PHNA.N_PHN_PREFIX AS 'PREFIXD1',
PHNA.N_PHN_NUMBER AS 'NBRD1',
PHNA.N_PHN_EXTENTN AS 'EXTD1',
PHNB.N_PHN_AREACD AS 'AREAD2',
PHNB.N_PHN_PREFIX AS 'PREFIXD2',
PHNB.N_PHN_NUMBER AS 'NBRE2',
PHNB.N_PHN_EXTENTN AS 'EXTD2',
BTY.D_BTY_ABO AS 'ABO1',
BTY.D_BTY_RHESUS AS 'RHI',
VST.D_VST_DATE AS 'FIRST1',
DV2.COUNT_VISITS AS 'COUNT',
SUM(DTS.D_DTS_DONSUM) AS 'AWARD',
ELG.D_ELG_RWBDTE AS 'ELIG1'
--INTO Donor_Visit4
From Donor_Visit2 DV2
RIGHT OUTER JOIN DNR_DTS_DB_REC DTS
ON DV2.COUNT_INSTID = DTS.D_DTS_INSTID
RIGHT OUTER JOIN NAT_PER_DB_REC PER
ON DV2.COUNT_INSTID = PER.N_PER_INSTID
RIGHT OUTER JOIN DNR_BTY_DB_REC BTY
ON DV2.COUNT_INSTID = BTY.D_BTY_INSTID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNA
ON DV2.COUNT_INSTID = PHNA.N_PHN_INSTID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB
ON DV2.COUNT_INSTID = PHNB.N_PHN_INSTID
RIGHT OUTER JOIN DNR_DTS_DB_REC DNT
ON DV2.COUNT_ID = DNT.D_DTS_ID
RIGHT OUTER JOIN NAT_PER_DB_REC PER1
ON DV2.COUNT_ID = PER1.N_PER_ID
RIGHT OUTER JOIN DNR_BTY_DB_REC BTY1
ON DV2.COUNT_ID = BTY1.D_BTY_ID
LEFT OUTER JOIN NAT_PHN_DB_REC PHNA1
ON DV2.COUNT_ID = PHNA1.N_PHN_ID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB1
ON DV2.COUNT_ID = PHNB1.N_PHN_ID
LEFT OUTER JOIN NAT_PHN_DB_REC PHNA2
ON PHNA2.N_PHN_PHTYP = 'D'
LEFT OUTER JOIN NAT_PHN_DB_REC PHNB2
ON PHNB2.N_PHN_PHTYP = 'E',
--LEFT OUTER JOIN DNR_DTS_DB_REC DTS1
--DTS1.D_DTS_CNTTYP <> 'N',
DNR_ELG_DB_REC ELG, NAT_NAM_DB_REC NAM, NAT_ADR_DB_REC ADR, DNR_VST_DB_REC VST
WHERE DV2.COUNT_INSTID = VST.D_VST_INSTID
AND DV2.COUNT_INSTID = ELG.D_ELG_INSTID
AND DV2.COUNT_INSTID = N_NAM_INSTID
AND DV2.COUNT_INSTID = N_ADR_INSTID
AND DV2.COUNT_INSTID = VST.D_VST_INSTID
--AND DV2.COUNT_INSTID = ELG.D_ELG_ID
AND NAM.N_NAM_SEQNO = 0
AND VST.D_VST_DATE = (SELECT MIN(VSTB.D_VST_DATE)
FROM DNR_VST_DB_REC VSTB
WHERE VST.D_VST_INSTID = VSTB.D_VST_INSTID
AND VSTB.D_VST_STATUS = 'DN'
AND VST.D_VST_ID = VSTB.D_VST_ID)
AND NOT EXISTS (SELECT R_DRC_ID
FROM REC_DRC_DB_REC
WHERE R_DRC_ID = COUNT_ID
AND R_DRC_INSTID = COUNT_INSTID
AND R_DRC_RESPCD = '15')
GROUP BY
NAM.N_NAM_ID,
NAM.N_NAM_INSTID,
NAM.N_NAM_FNAME,
NAM.N_NAM_MINITIAL,
NAM.N_NAM_LNAME,
PER.N_PER_BIRTH,
ADR.N_ADR_ADDR1,
ADR.N_ADR_ADDR2,
ADR.N_ADR_CITY,
ADR.N_ADR_STATE,
ADR.N_ADR_ZIP,
PER.N_PER_EMAIL,
PER.N_PER_GENDER,
PHNA.N_PHN_AREACD,
PHNA.N_PHN_PREFIX,
PHNA.N_PHN_NUMBER,
PHNA.N_PHN_EXTENTN,
PHNB.N_PHN_AREACD,
PHNB.N_PHN_PREFIX,
PHNB.N_PHN_NUMBER,
PHNB.N_PHN_EXTENTN,
BTY.D_BTY_ABO,
BTY.D_BTY_RHESUS,
VST.D_VST_DATE,
DV2.COUNT_VISITS,
DTS.D_DTS_DONSUM,
ELG.D_ELG_RWBDTE
Error Message:
(845 row(s) affected)
(844 row(s) affected)
(396 row(s) affected)
Server: Msg 9002, Level 17, State 6, Line 2
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.
Server: Msg 1105, Level 17, State 1, Line 2
Could not allocate space for object '(SYSTEM table id: -109901351)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.Ok, its a hog.
First, see if you can "blow out" tempdb using DBCC SHRINKDATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_3pd1.asp).
If that doesn't help enough, see if you can create an index that the GROUP BY expression can use... It is often enough to get the first three or four columns covered, since that can buy you an enormous reduction in staging space.
If that doesn't help, buy more disk!
-PatP|||My God, I mean Oh Codd, you have 27 GROUP BY's!!!!... You realize that your tempdb would be the bottleneck throughout the life of your app! Are you sure you need all 27?.. Click on estimated execution plan icon in QA and see what you get there.
I writing a store procedure, the first three parts work pretty well. The last select statement has about 8 outer joins in it. every time I run the store procedure, I get an error message for the last part. Below are the error message and the store procedure:
Store Procedure:
--Create Procedure dbo.IMS_Donation
--AS
Select Distinct D_VST_ID as 'DRWLOC_ID', D_VST_INSTID as 'DRWLOC_INSTID'
Into Donor_Visit1
From DNR_VST_DB_REC
Where D_VST_DATE Between 20010101 AND 20040512
AND D_VST_DONTYP in ('AP', 'WB', 'RP', 'E2', 'E1')
AND D_VST_STATUS = 'DN'
ORDER BY D_VST_ID
GO
SELECT DRWLOC_ID as 'COUNT_ID', DRWLOC_INSTID as 'COUNT_INSTID',
count(*) as 'COUNT_VISITS'
INTO Donor_Visit2
FROM DNR_VST_DB_REC, Donor_Visit1
Where D_VST_ID = DRWLOC_ID
AND NOT EXISTS (Select R_DCC_ID
From REC_DCC_DB_REC
Where R_DCC_ID = DRWLOC_ID
AND R_DCC_INSTID = DRWLOC_INSTID
AND R_DCC_CALLCD = 'DC')
GROUP BY DRWLOC_ID, DRWLOC_INSTID
GO
SELECT DVT1.DRWLOC_ID as'COMP_ID', CMP.l_CMP_UNITNO as 'COMP_UNITID',
CMP.L_CMP_INSTID as 'COMP_INSTID', count(*) as 'COMP_COMPTOT'
INTO Donor_Visit3
FROM LAB_CMP_DB_REC CMP, Donor_Visit1 DVT1, DNR_VST_DB_REC VST, CMP_VST_Jct CVT
WHERE CMP.L_CMP_INSTID = DVT1.DRWLOC_INSTID
AND VST.D_VST_ID = DVT1.DRWLOC_ID
AND VST.D_VST_UNITNO = CVT.L_CMP_UNITNO
AND CMP.L_CMP_UNITNO = CVT.L_CMP_UNITNO
AND CMP.L_CMP_STATCD != 'MOD'
AND CMP.L_CMP_CMPCD NOT IN ('INC', 'EMTY')
AND VST.D_VST_DATE BETWEEN 20010101 AND 20040512
AND VST.D_VST_STATUS = 'DN'
GROUP BY DVT1.DRWLOC_ID, CMP.L_CMP_UNITNO, CMP.L_CMP_INSTID
GO
SELECT DISTINCT
NAM.N_NAM_ID AS 'ID1',
NAM.N_NAM_INSTID AS 'INSTID1',
NAM.N_NAM_FNAME AS 'FNAME1',
NAM.N_NAM_MINITIAL AS 'MINITIAL1',
NAM.N_NAM_LNAME AS 'LNAME1',
PER.N_PER_BIRTH AS 'BIRTH1',
ADR.N_ADR_ADDR1 AS 'ADDR1',
ADR.N_ADR_ADDR2 AS 'ADDR2',
ADR.N_ADR_CITY AS 'CITY1',
ADR.N_ADR_STATE AS 'STATE1',
SUBSTRING(ADR.N_ADR_ZIP, 1,5) AS 'ZIP1',
PER.N_PER_EMAIL AS 'EMAIL1',
PER.N_PER_GENDER AS 'GENDER1',
PHNA.N_PHN_AREACD AS 'AREAD1',
PHNA.N_PHN_PREFIX AS 'PREFIXD1',
PHNA.N_PHN_NUMBER AS 'NBRD1',
PHNA.N_PHN_EXTENTN AS 'EXTD1',
PHNB.N_PHN_AREACD AS 'AREAD2',
PHNB.N_PHN_PREFIX AS 'PREFIXD2',
PHNB.N_PHN_NUMBER AS 'NBRE2',
PHNB.N_PHN_EXTENTN AS 'EXTD2',
BTY.D_BTY_ABO AS 'ABO1',
BTY.D_BTY_RHESUS AS 'RHI',
VST.D_VST_DATE AS 'FIRST1',
DV2.COUNT_VISITS AS 'COUNT',
SUM(DTS.D_DTS_DONSUM) AS 'AWARD',
ELG.D_ELG_RWBDTE AS 'ELIG1'
--INTO Donor_Visit4
From Donor_Visit2 DV2
RIGHT OUTER JOIN DNR_DTS_DB_REC DTS
ON DV2.COUNT_INSTID = DTS.D_DTS_INSTID
RIGHT OUTER JOIN NAT_PER_DB_REC PER
ON DV2.COUNT_INSTID = PER.N_PER_INSTID
RIGHT OUTER JOIN DNR_BTY_DB_REC BTY
ON DV2.COUNT_INSTID = BTY.D_BTY_INSTID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNA
ON DV2.COUNT_INSTID = PHNA.N_PHN_INSTID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB
ON DV2.COUNT_INSTID = PHNB.N_PHN_INSTID
RIGHT OUTER JOIN DNR_DTS_DB_REC DNT
ON DV2.COUNT_ID = DNT.D_DTS_ID
RIGHT OUTER JOIN NAT_PER_DB_REC PER1
ON DV2.COUNT_ID = PER1.N_PER_ID
RIGHT OUTER JOIN DNR_BTY_DB_REC BTY1
ON DV2.COUNT_ID = BTY1.D_BTY_ID
LEFT OUTER JOIN NAT_PHN_DB_REC PHNA1
ON DV2.COUNT_ID = PHNA1.N_PHN_ID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB1
ON DV2.COUNT_ID = PHNB1.N_PHN_ID
LEFT OUTER JOIN NAT_PHN_DB_REC PHNA2
ON PHNA2.N_PHN_PHTYP = 'D'
LEFT OUTER JOIN NAT_PHN_DB_REC PHNB2
ON PHNB2.N_PHN_PHTYP = 'E',
--LEFT OUTER JOIN DNR_DTS_DB_REC DTS1
--DTS1.D_DTS_CNTTYP <> 'N',
DNR_ELG_DB_REC ELG, NAT_NAM_DB_REC NAM, NAT_ADR_DB_REC ADR, DNR_VST_DB_REC VST
WHERE DV2.COUNT_INSTID = VST.D_VST_INSTID
AND DV2.COUNT_INSTID = ELG.D_ELG_INSTID
AND DV2.COUNT_INSTID = N_NAM_INSTID
AND DV2.COUNT_INSTID = N_ADR_INSTID
AND DV2.COUNT_INSTID = VST.D_VST_INSTID
--AND DV2.COUNT_INSTID = ELG.D_ELG_ID
AND NAM.N_NAM_SEQNO = 0
AND VST.D_VST_DATE = (SELECT MIN(VSTB.D_VST_DATE)
FROM DNR_VST_DB_REC VSTB
WHERE VST.D_VST_INSTID = VSTB.D_VST_INSTID
AND VSTB.D_VST_STATUS = 'DN'
AND VST.D_VST_ID = VSTB.D_VST_ID)
AND NOT EXISTS (SELECT R_DRC_ID
FROM REC_DRC_DB_REC
WHERE R_DRC_ID = COUNT_ID
AND R_DRC_INSTID = COUNT_INSTID
AND R_DRC_RESPCD = '15')
GROUP BY
NAM.N_NAM_ID,
NAM.N_NAM_INSTID,
NAM.N_NAM_FNAME,
NAM.N_NAM_MINITIAL,
NAM.N_NAM_LNAME,
PER.N_PER_BIRTH,
ADR.N_ADR_ADDR1,
ADR.N_ADR_ADDR2,
ADR.N_ADR_CITY,
ADR.N_ADR_STATE,
ADR.N_ADR_ZIP,
PER.N_PER_EMAIL,
PER.N_PER_GENDER,
PHNA.N_PHN_AREACD,
PHNA.N_PHN_PREFIX,
PHNA.N_PHN_NUMBER,
PHNA.N_PHN_EXTENTN,
PHNB.N_PHN_AREACD,
PHNB.N_PHN_PREFIX,
PHNB.N_PHN_NUMBER,
PHNB.N_PHN_EXTENTN,
BTY.D_BTY_ABO,
BTY.D_BTY_RHESUS,
VST.D_VST_DATE,
DV2.COUNT_VISITS,
DTS.D_DTS_DONSUM,
ELG.D_ELG_RWBDTE
Error Message:
(845 row(s) affected)
(844 row(s) affected)
(396 row(s) affected)
Server: Msg 9002, Level 17, State 6, Line 2
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.
Server: Msg 1105, Level 17, State 1, Line 2
Could not allocate space for object '(SYSTEM table id: -109901351)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.Ok, its a hog.
First, see if you can "blow out" tempdb using DBCC SHRINKDATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_3pd1.asp).
If that doesn't help enough, see if you can create an index that the GROUP BY expression can use... It is often enough to get the first three or four columns covered, since that can buy you an enormous reduction in staging space.
If that doesn't help, buy more disk!
-PatP|||My God, I mean Oh Codd, you have 27 GROUP BY's!!!!... You realize that your tempdb would be the bottleneck throughout the life of your app! Are you sure you need all 27?.. Click on estimated execution plan icon in QA and see what you get there.
Subscribe to:
Posts (Atom)