Hey everyone,
I have two tables, one is a large table (v_userviews) containing a list of all the servers and various information about those servers. The other table (l_printers) contains printer information for those servers. I am working on a view to consolidate the printer information in l_printers with the other server information in v_userviews.
I've been trying to get outer joins to work but I am getting this error:
"Server: Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation."
Here is my select statement:
select u.propid, u.address,
SUM((CASE u.Tree WHEN 'tree1' then 1 ELSE 0 END)) AS One,
SUM((CASE u.Tree WHEN 'tree2' then 1 ELSE 0 END)) AS Two,
SUM((CASE u.Tree WHEN 'tree3' then 1 ELSE 0 END)) AS Three,
SUM((CASE u.Tree WHEN 'tree4' then 1 ELSE 0 END)) AS Four,
SUM((CASE u.Tree WHEN 'tree5' then 1 ELSE 0 END)) AS Five,
SUM((CASE u.Tree WHEN 'tree6' then 1 ELSE 0 END)) AS Six,
SUM((CASE u.Tree WHEN 'tree7' then 1 ELSE 0 END)) AS Seven,
SUM((CASE u.Tree WHEN 'tree8' then 1 ELSE 0 END)) AS Eight,
SUM((CASE u.Tree WHEN 'tree9' then 1 ELSE 0 END)) AS Nine,
SUM((CASE u.Tree WHEN 'tree10' then 1 ELSE 0 END)) AS Ten,
SUM((CASE u.Tree WHEN 'tree11' then 1 ELSE 0 END)) AS Eleven,
SUM((CASE u.Tree WHEN 'tree12' then 1 ELSE 0 END)) AS Twelve,
SUM((CASE u.Tree WHEN 'tree13' then 1 ELSE 0 END)) AS Thirteen,
SUM((CASE u.Tree WHEN 'tree14' then 1 ELSE 0 END)) AS Fourteen,
count(u.server) as totalservers,
sum(cast(left(u.totalspace,len(u.totalspace)-2) as int)) as totalspace,
sum(cast(left(u.totalusedspace,len(u.totalusedspac e)-2) as int)) as totalusedspace,
count(p.printer) as numprinters
from serverops.dbo.v_userviews u LEFT OUTER JOIN novell_twr.dbo.l_printers p ON u.propid = p.propid
where u.os='netware'and u.state in ('ny', 'nj', 'fl')
group by u.propid, u.address
the l_printers table is in this format:
Printers Server Propid
nvarchar nvarchar varchar
Thanks for all your help. :beer:Try commenting out all the sums, and see what the count of total servers looks like, I am going to guess it is somewhat larger than you expect. If so, then you will have to look over how you are joining the two tables.|||Try commenting out all the sums, and see what the count of total servers looks like, I am going to guess it is somewhat larger than you expect. If so, then you will have to look over how you are joining the two tables.
You were right, the total servers was definitely larger than expected. Will I have to use another method other than outer joins to consolidate the two tables?|||How many servers do you have?|||How many servers do you have?
Around 4800|||what's defined as tiny int?
bigint
Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
int
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).
smallint
Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767).
tinyint
Integer data from 0 through 255.
post some ddl|||what's defined as tiny int?
post some ddl
Nothing is defined as tinyint
Here are some DDL
L_Printers:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[L_Printers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[L_Printers]
GO
CREATE TABLE [dbo].[L_Printers] (
[Printer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Server] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
R_Servers (v_userviews is actually just a distinct top 100 view of r_servers):
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[R_Servers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[R_Servers]
GO
CREATE TABLE [dbo].[R_Servers] (
[Server] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Type] [varchar] (51) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Classification] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPX Internal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Secondary IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DNS_IP1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DNS_IP2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DNS_RIBIP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Branch #] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OS] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OSVersion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InProduction] [bit] NULL ,
[NOTES] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tree] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NWContext] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NTDomain] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NTDomainRole] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTXEUpdate] [datetime] NULL ,
[MacAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CommonName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TFloors] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bank_Floors] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalEmp] [float] NULL ,
[Address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Street Address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Room] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Floor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPResponse] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ResponseOK] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TapeDrive] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TapeType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DriveCapacity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IP Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RIBIP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RIBPW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Remote PW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Console PW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeviceStatus] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RIBInstalled] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPack] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeviceOwner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ASource] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BackupDetails] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ServerID] [numeric](10, 0) NOT NULL ,
[Model] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Serial] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AssetNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Memory] [numeric](18, 0) NULL ,
[ROM] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CPU] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CPUSpeed] [decimal](18, 0) NULL ,
[TotalCPUs] [int] NULL ,
[Vendor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PODate] [datetime] NULL ,
[ReceivedDate] [smalldatetime] NULL ,
[ActivationDate] [smalldatetime] NULL ,
[RefreshDate] [smalldatetime] NULL ,
[WarrantyEndDate] [smalldatetime] NULL ,
[TowerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TowerID] [int] NULL ,
[DistrictID] [int] NULL ,
[Priority] [int] NULL ,
[Severity] [int] NULL ,
[SupportComment] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SiteContact] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SupportStaff] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PSRegion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DistrictManager] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DMName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SupportQueue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[District] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Domain] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[inProductionDate] [smalldatetime] NULL ,
[Region] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalSpace] [nvarchar] (257) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalUsedSpace] [nvarchar] (257) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RackID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RackPosition] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RackRow] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OSType] [int] NULL ,
[Project#] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClusterID] [numeric](18, 0) NULL ,
[RIBLicenseKey] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prop_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [datetime] NULL ,
[CheckSum] [int] NULL ,
[ManagmentServer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastAudit] [datetime] NULL ,
[RecordAuditor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimarySupport] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVM] [bit] NULL ,
[KVMType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[expanse] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOBOwner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimaryRecordOwner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreationDate] [datetime] NULL ,
[Strategic] [bit] NULL ,
[ServiceBillingCC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HardwareSupport] [bit] NULL ,
[Updated] [datetime] NOT NULL
) ON [PRIMARY]
GO
Showing posts with label joins. Show all posts
Showing posts with label joins. Show all posts
Thursday, February 16, 2012
Thursday, February 9, 2012
are old school joins slower
The Exec Plans from these two simple queries are identical. Of course theres
not much data here though. Are the old type of joins in query 1 typically
slower, or just not liked/ ANSI standard?
select a.*,titleauthor.title_id
from authors a,titleauthor
where a.au_id = titleauthor.au_id
select a.*,ta.title_id from authors a
inner join titleauthor ta on a.au_id = ta.au_id
TIA, ChrisRNo difference between them and infixed joins as far as performance
goes. But the old style is easier to read when you have n-ary
relationships or a Star schema. For fun try to re-write these as
infixed:
SELECT *
FROM A, B, C
WHERE A.x BETWEEN B.y AND C.z;
SELECT *
FROM A, B, C
WHERE A.x IN (B.y, C.z, 42)
AND B.y IN (A.xx, C.zz, 41);|||There should not be any difference though one is much clearer than the
other.
-oj
"ChrisR" <noemail@.bla.com> wrote in message
news:uke6cW6ZFHA.3712@.TK2MSFTNGP09.phx.gbl...
> The Exec Plans from these two simple queries are identical. Of course
> theres not much data here though. Are the old type of joins in query 1
> typically slower, or just not liked/ ANSI standard?
> select a.*,titleauthor.title_id
> from authors a,titleauthor
> where a.au_id = titleauthor.au_id
>
> select a.*,ta.title_id from authors a
> inner join titleauthor ta on a.au_id = ta.au_id
>
> TIA, ChrisR
>|||The two statements are equivalent and should result in the same execution
plan and performance. In my experience the optimizer usually treats the
INNER JOIN syntax in exactly the same way as the equivalent join implemented
in a WHERE clause. In fact your first example is still ANSI standard and
perfectly valid. It is only the old outer join *= notation that is
deprecated.
David Portas
SQL Server MVP
--|||"oj" <nospam_ojngo@.home.com> wrote in message
news:%23MEWSl6ZFHA.4068@.TK2MSFTNGP10.phx.gbl...
> There should not be any difference though one is much clearer than the
> other.
Hopefully it's clear which one you mean ;-)
David Portas
SQL Server MVP
--|||On 2 Jun 2005 12:26:20 -0700, --CELKO-- wrote:
> No difference between them and infixed joins as far as performance
> goes. But the old style is easier to read when you have n-ary
> relationships or a Star schema. For fun try to re-write these as
> infixed:
Ugh ... here goes, just for my own challenge amusement:
> SELECT *
> FROM A, B, C
> WHERE A.x BETWEEN B.y AND C.z;
SELECT *
FROM A
INNER JOIN B ON A.x >= B.y
INNER JOIN C ON A.x <= C.z
> SELECT *
> FROM A, B, C
> WHERE A.x IN (B.y, C.z, 42)
> AND B.y IN (A.xx, C.zz, 41);
This one is really pathological ... I can't seem to do it|||that one. can't you see it clearly. ;~)
since it's an inner join, i don't worry too much about it.
now if it's an outer join, i prefer ansi join style.
-oj
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:86qdndw9p8uXwgLfRVn-2g@.giganews.com...
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23MEWSl6ZFHA.4068@.TK2MSFTNGP10.phx.gbl...
> Hopefully it's clear which one you mean ;-)
> --
> David Portas
> SQL Server MVP
> --
>
not much data here though. Are the old type of joins in query 1 typically
slower, or just not liked/ ANSI standard?
select a.*,titleauthor.title_id
from authors a,titleauthor
where a.au_id = titleauthor.au_id
select a.*,ta.title_id from authors a
inner join titleauthor ta on a.au_id = ta.au_id
TIA, ChrisRNo difference between them and infixed joins as far as performance
goes. But the old style is easier to read when you have n-ary
relationships or a Star schema. For fun try to re-write these as
infixed:
SELECT *
FROM A, B, C
WHERE A.x BETWEEN B.y AND C.z;
SELECT *
FROM A, B, C
WHERE A.x IN (B.y, C.z, 42)
AND B.y IN (A.xx, C.zz, 41);|||There should not be any difference though one is much clearer than the
other.
-oj
"ChrisR" <noemail@.bla.com> wrote in message
news:uke6cW6ZFHA.3712@.TK2MSFTNGP09.phx.gbl...
> The Exec Plans from these two simple queries are identical. Of course
> theres not much data here though. Are the old type of joins in query 1
> typically slower, or just not liked/ ANSI standard?
> select a.*,titleauthor.title_id
> from authors a,titleauthor
> where a.au_id = titleauthor.au_id
>
> select a.*,ta.title_id from authors a
> inner join titleauthor ta on a.au_id = ta.au_id
>
> TIA, ChrisR
>|||The two statements are equivalent and should result in the same execution
plan and performance. In my experience the optimizer usually treats the
INNER JOIN syntax in exactly the same way as the equivalent join implemented
in a WHERE clause. In fact your first example is still ANSI standard and
perfectly valid. It is only the old outer join *= notation that is
deprecated.
David Portas
SQL Server MVP
--|||"oj" <nospam_ojngo@.home.com> wrote in message
news:%23MEWSl6ZFHA.4068@.TK2MSFTNGP10.phx.gbl...
> There should not be any difference though one is much clearer than the
> other.
Hopefully it's clear which one you mean ;-)
David Portas
SQL Server MVP
--|||On 2 Jun 2005 12:26:20 -0700, --CELKO-- wrote:
> No difference between them and infixed joins as far as performance
> goes. But the old style is easier to read when you have n-ary
> relationships or a Star schema. For fun try to re-write these as
> infixed:
Ugh ... here goes, just for my own challenge amusement:
> SELECT *
> FROM A, B, C
> WHERE A.x BETWEEN B.y AND C.z;
SELECT *
FROM A
INNER JOIN B ON A.x >= B.y
INNER JOIN C ON A.x <= C.z
> SELECT *
> FROM A, B, C
> WHERE A.x IN (B.y, C.z, 42)
> AND B.y IN (A.xx, C.zz, 41);
This one is really pathological ... I can't seem to do it|||that one. can't you see it clearly. ;~)
since it's an inner join, i don't worry too much about it.
now if it's an outer join, i prefer ansi join style.
-oj
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:86qdndw9p8uXwgLfRVn-2g@.giganews.com...
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23MEWSl6ZFHA.4068@.TK2MSFTNGP10.phx.gbl...
> Hopefully it's clear which one you mean ;-)
> --
> David Portas
> SQL Server MVP
> --
>
Are JOINs and WHEREs semantically equivalent?
Hi,
I have a query that uses a series of JOINs to produce a result set.
However, it needs to be generated by a custom SQL Builder, and it would be
easier to auto-generate if the JOINs were WHERE clauses instead.
Is this possible - i.e. can I do *everything* that a JOIN does in a WHERE,
and still get *exactly* the same result?
The query is below, if it helps to shed some light on the above question:
SELECT COUNT(DISTINCT(I1.ID)) AS CountOfIncidents,
OuterDepartment.CalculatedPath as Department
,OuterSource.CalculatedPath as OuterSource
FROM Incident I1
LEFT JOIN
yDepartment OuterDepartment ON I1.RaisedAgainstDepartmentID IN
(SELECT yDepartment.ID
FROM yDepartment
WHERE (yDepartment.CalculatedPath =
OuterDepartment.CalculatedPath) OR
(yDepartment.CalculatedPath
LIKE OuterDepartment.CalculatedPath + '\%'))
LEFT JOIN
yIncidentSourceType OuterSource ON I1.IncidentSourceTypeID IN
(SELECT yIncidentSourceType.ID
FROM yIncidentSourceType
WHERE (yIncidentSourceType.CalculatedPath =
OuterSource.CalculatedPath) OR
(yIncidentSourceType.CalculatedPath
LIKE OuterSource.CalculatedPath + '\%'))
WHERE (OuterDepartment.ParentID = 12) AND (OuterSource.ParentID is null)
AND
I1.[ID] IN (SELECT DISTINCT I1.[ID] FROM [Incident]I1)
GROUP BY OuterDepartment.CalculatedPath, OuterSource.CalculatedPath
[The query sums totals of all children of specified parent records,
presenting them in a Matrix format]
Thanks,
Duncanhttp://www.tek-tips.com/faqs.cfm?fid=5168
"Duncan M Gunn" <gunnd@.gaelqualityNOSPAM.co.uk> wrote in message
news:e0wBGgcQFHA.3816@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a query that uses a series of JOINs to produce a result set.
> However, it needs to be generated by a custom SQL Builder, and it would be
> easier to auto-generate if the JOINs were WHERE clauses instead.
> Is this possible - i.e. can I do *everything* that a JOIN does in a WHERE,
> and still get *exactly* the same result?
>
> The query is below, if it helps to shed some light on the above question:
>
> SELECT COUNT(DISTINCT(I1.ID)) AS CountOfIncidents,
> OuterDepartment.CalculatedPath as Department
> ,OuterSource.CalculatedPath as OuterSource
> FROM Incident I1
> LEFT JOIN
> yDepartment OuterDepartment ON I1.RaisedAgainstDepartmentID IN
> (SELECT yDepartment.ID
> FROM yDepartment
> WHERE (yDepartment.CalculatedPath =
> OuterDepartment.CalculatedPath) OR
> (yDepartment.CalculatedPath
> LIKE OuterDepartment.CalculatedPath + '\%'))
> LEFT JOIN
> yIncidentSourceType OuterSource ON I1.IncidentSourceTypeID IN
> (SELECT yIncidentSourceType.ID
> FROM yIncidentSourceType
> WHERE (yIncidentSourceType.CalculatedPath
> = OuterSource.CalculatedPath) OR
> (yIncidentSourceType.CalculatedPath LIKE OuterSource.CalculatedPath +
> '\%'))
> WHERE (OuterDepartment.ParentID = 12) AND (OuterSource.ParentID is
> null)
> AND
> I1.[ID] IN (SELECT DISTINCT I1.[ID] FROM [Incident]I1)
> GROUP BY OuterDepartment.CalculatedPath, OuterSource.CalculatedPath
>
> [The query sums totals of all children of specified parent records,
> presenting them in a Matrix format]
>
> Thanks,
> Duncan
>|||Duncan,
To put the join expression in the WHERE clause you have to use the old style
join and it is not compatible for outer joins. For example, when you want to
select all rows from t1 with no entries in t2 using a join condition.
select a.*
from t1 as a left join t2 as b on a.pk_col = b.pk_col
where b.pk_col is null
-- this statement will not yield same result as previous
select a.*
from t1 as a, t2 as b
where a.pk_col *= b.pk_col and b.pk_isnull
go
ANSI JOIN vs. OUTER JOIN
http://www.microsoft.com/sql/techin...ment/July23.asp
AMB
"Duncan M Gunn" wrote:
> Hi,
> I have a query that uses a series of JOINs to produce a result set.
> However, it needs to be generated by a custom SQL Builder, and it would be
> easier to auto-generate if the JOINs were WHERE clauses instead.
> Is this possible - i.e. can I do *everything* that a JOIN does in a WHERE,
> and still get *exactly* the same result?
>
> The query is below, if it helps to shed some light on the above question:
>
> SELECT COUNT(DISTINCT(I1.ID)) AS CountOfIncidents,
> OuterDepartment.CalculatedPath as Department
> ,OuterSource.CalculatedPath as OuterSource
> FROM Incident I1
> LEFT JOIN
> yDepartment OuterDepartment ON I1.RaisedAgainstDepartmentID IN
> (SELECT yDepartment.ID
> FROM yDepartment
> WHERE (yDepartment.CalculatedPath =
> OuterDepartment.CalculatedPath) OR
> (yDepartment.CalculatedPat
h
> LIKE OuterDepartment.CalculatedPath + '\%'))
> LEFT JOIN
> yIncidentSourceType OuterSource ON I1.IncidentSourceTypeID IN
> (SELECT yIncidentSourceType.ID
> FROM yIncidentSourceType
> WHERE (yIncidentSourceType.CalculatedPath
=
> OuterSource.CalculatedPath) OR
> (yIncidentSourceType.Calcu
latedPath
> LIKE OuterSource.CalculatedPath + '\%'))
> WHERE (OuterDepartment.ParentID = 12) AND (OuterSource.ParentID is nul
l)
> AND
> I1.[ID] IN (SELECT DISTINCT I1.[ID] FROM [Incident]I1)
> GROUP BY OuterDepartment.CalculatedPath, OuterSource.CalculatedPath
>
> [The query sums totals of all children of specified parent records,
> presenting them in a Matrix format]
>
> Thanks,
> Duncan
>
>|||>> However, it needs to be generated by a custom SQL Builder, and it
would be
easier to auto-generate if the JOINs were WHERE clauses instead. Is
this possible <<
No. The OUTER JOINs have to be done in a sequence and cannot be moved
to the WHERE clause. The old *= syntax is deprecated and will not be
supported in the future.
[sic], presenting them in a Matrix format <<
The path enumeration model for a hierarchy is good for searching, but
terrible for aggregation. Consider changing to a nested sets model and
you can do this in one simple query.
I have a query that uses a series of JOINs to produce a result set.
However, it needs to be generated by a custom SQL Builder, and it would be
easier to auto-generate if the JOINs were WHERE clauses instead.
Is this possible - i.e. can I do *everything* that a JOIN does in a WHERE,
and still get *exactly* the same result?
The query is below, if it helps to shed some light on the above question:
SELECT COUNT(DISTINCT(I1.ID)) AS CountOfIncidents,
OuterDepartment.CalculatedPath as Department
,OuterSource.CalculatedPath as OuterSource
FROM Incident I1
LEFT JOIN
yDepartment OuterDepartment ON I1.RaisedAgainstDepartmentID IN
(SELECT yDepartment.ID
FROM yDepartment
WHERE (yDepartment.CalculatedPath =
OuterDepartment.CalculatedPath) OR
(yDepartment.CalculatedPath
LIKE OuterDepartment.CalculatedPath + '\%'))
LEFT JOIN
yIncidentSourceType OuterSource ON I1.IncidentSourceTypeID IN
(SELECT yIncidentSourceType.ID
FROM yIncidentSourceType
WHERE (yIncidentSourceType.CalculatedPath =
OuterSource.CalculatedPath) OR
(yIncidentSourceType.CalculatedPath
LIKE OuterSource.CalculatedPath + '\%'))
WHERE (OuterDepartment.ParentID = 12) AND (OuterSource.ParentID is null)
AND
I1.[ID] IN (SELECT DISTINCT I1.[ID] FROM [Incident]I1)
GROUP BY OuterDepartment.CalculatedPath, OuterSource.CalculatedPath
[The query sums totals of all children of specified parent records,
presenting them in a Matrix format]
Thanks,
Duncanhttp://www.tek-tips.com/faqs.cfm?fid=5168
"Duncan M Gunn" <gunnd@.gaelqualityNOSPAM.co.uk> wrote in message
news:e0wBGgcQFHA.3816@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a query that uses a series of JOINs to produce a result set.
> However, it needs to be generated by a custom SQL Builder, and it would be
> easier to auto-generate if the JOINs were WHERE clauses instead.
> Is this possible - i.e. can I do *everything* that a JOIN does in a WHERE,
> and still get *exactly* the same result?
>
> The query is below, if it helps to shed some light on the above question:
>
> SELECT COUNT(DISTINCT(I1.ID)) AS CountOfIncidents,
> OuterDepartment.CalculatedPath as Department
> ,OuterSource.CalculatedPath as OuterSource
> FROM Incident I1
> LEFT JOIN
> yDepartment OuterDepartment ON I1.RaisedAgainstDepartmentID IN
> (SELECT yDepartment.ID
> FROM yDepartment
> WHERE (yDepartment.CalculatedPath =
> OuterDepartment.CalculatedPath) OR
> (yDepartment.CalculatedPath
> LIKE OuterDepartment.CalculatedPath + '\%'))
> LEFT JOIN
> yIncidentSourceType OuterSource ON I1.IncidentSourceTypeID IN
> (SELECT yIncidentSourceType.ID
> FROM yIncidentSourceType
> WHERE (yIncidentSourceType.CalculatedPath
> = OuterSource.CalculatedPath) OR
> (yIncidentSourceType.CalculatedPath LIKE OuterSource.CalculatedPath +
> '\%'))
> WHERE (OuterDepartment.ParentID = 12) AND (OuterSource.ParentID is
> null)
> AND
> I1.[ID] IN (SELECT DISTINCT I1.[ID] FROM [Incident]I1)
> GROUP BY OuterDepartment.CalculatedPath, OuterSource.CalculatedPath
>
> [The query sums totals of all children of specified parent records,
> presenting them in a Matrix format]
>
> Thanks,
> Duncan
>|||Duncan,
To put the join expression in the WHERE clause you have to use the old style
join and it is not compatible for outer joins. For example, when you want to
select all rows from t1 with no entries in t2 using a join condition.
select a.*
from t1 as a left join t2 as b on a.pk_col = b.pk_col
where b.pk_col is null
-- this statement will not yield same result as previous
select a.*
from t1 as a, t2 as b
where a.pk_col *= b.pk_col and b.pk_isnull
go
ANSI JOIN vs. OUTER JOIN
http://www.microsoft.com/sql/techin...ment/July23.asp
AMB
"Duncan M Gunn" wrote:
> Hi,
> I have a query that uses a series of JOINs to produce a result set.
> However, it needs to be generated by a custom SQL Builder, and it would be
> easier to auto-generate if the JOINs were WHERE clauses instead.
> Is this possible - i.e. can I do *everything* that a JOIN does in a WHERE,
> and still get *exactly* the same result?
>
> The query is below, if it helps to shed some light on the above question:
>
> SELECT COUNT(DISTINCT(I1.ID)) AS CountOfIncidents,
> OuterDepartment.CalculatedPath as Department
> ,OuterSource.CalculatedPath as OuterSource
> FROM Incident I1
> LEFT JOIN
> yDepartment OuterDepartment ON I1.RaisedAgainstDepartmentID IN
> (SELECT yDepartment.ID
> FROM yDepartment
> WHERE (yDepartment.CalculatedPath =
> OuterDepartment.CalculatedPath) OR
> (yDepartment.CalculatedPat
h
> LIKE OuterDepartment.CalculatedPath + '\%'))
> LEFT JOIN
> yIncidentSourceType OuterSource ON I1.IncidentSourceTypeID IN
> (SELECT yIncidentSourceType.ID
> FROM yIncidentSourceType
> WHERE (yIncidentSourceType.CalculatedPath
=
> OuterSource.CalculatedPath) OR
> (yIncidentSourceType.Calcu
latedPath
> LIKE OuterSource.CalculatedPath + '\%'))
> WHERE (OuterDepartment.ParentID = 12) AND (OuterSource.ParentID is nul
l)
> AND
> I1.[ID] IN (SELECT DISTINCT I1.[ID] FROM [Incident]I1)
> GROUP BY OuterDepartment.CalculatedPath, OuterSource.CalculatedPath
>
> [The query sums totals of all children of specified parent records,
> presenting them in a Matrix format]
>
> Thanks,
> Duncan
>
>|||>> However, it needs to be generated by a custom SQL Builder, and it
would be
easier to auto-generate if the JOINs were WHERE clauses instead. Is
this possible <<
No. The OUTER JOINs have to be done in a sequence and cannot be moved
to the WHERE clause. The old *= syntax is deprecated and will not be
supported in the future.
[sic], presenting them in a Matrix format <<
The path enumeration model for a hierarchy is good for searching, but
terrible for aggregation. Consider changing to a nested sets model and
you can do this in one simple query.
Subscribe to:
Posts (Atom)