Join Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Join Error

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-02-23 : 06:06:56

Hi

I Have 2 tables Incident and Task. All I want to do is pull the count of records equal to a specified team, I need to join because the OwnerTeam I want to read from is from the Task table not the Incident table where there is also an OwnerTeam field. So, here is what I have but getting error, I must be missing something silly here?

Msg 4104, Level 16, State 1, Procedure SDCalls, Line 4
The multi-part identifier "dbo.Recid" could not be bound.
Msg 4104, Level 16, State 1, Procedure SDCalls, Line 4
The multi-part identifier "dbo.ParentLink_RecID" could not be bound.

USE [Dev]
Go
/****** Object: View [dbo].[LastDigit] Script Date: 02/22/2012 14:59:13 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create View [dbo].[SDCalls]
AS
SELECT COUNT(Distinct IncidentNumber) As TotalCalls
FROM dbo.Incident
Join dbo.Task
ON dbo.Recid = dbo.ParentLink_RecID
Where Incident.Status In('Active','Waiting')
And Task.OwnerTeam = 'Sales'
GO

also tried this bet getting same error? do I need to juse alias's?

USE [Dev]
Go

/****** Object: View [dbo].[LastDigit] Script Date: 02/22/2012 14:59:13 ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Alter View [dbo].[SDCalls]
AS
SELECT COUNT(Distinct IncidentNumber) As TotalCalls
FROM dbo.Incident
Join dbo.Task
ON dbo.Recid = dbo.ParentLink_RecID
where Task.OwnerTeam = 'Sales'
Group by Incident.Status
having Incident.Status In('Active','Waiting')

GO

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 06:15:51

yeah -- you need aliases: Something like



USE [Dev]
Go
/****** Object: View [dbo].[LastDigit] Script Date: 02/22/2012 14:59:13 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create View [dbo].[SDCalls] 
AS
SELECT COUNT(DISTINCT i/t.[IncidentNumber]) As TotalCalls
FROM
	dbo.Incident AS i
	JOIN dbo.Task AS t ON i/t.[Recid] = t/i.[ParentLink_RecID]
WHERE
	i.[Status] IN ('Active','Waiting')
	AND t.[OwnerTeam] = 'Sales'
GO

I don't know which tables the columns are in though.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 06:16:35

and similar for your second query.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-02-23 : 06:37:38

I don't know which tables the columns are in though.

The IncidentNumber column is is the Incident table and the OwnerTeam is in the Task table (the incident table also has a OwnerTeam column but dont want this one).

I get this error when trying the alias additions:
Do I need to declare the alisas's?

Thanks

Msg 207, Level 16, State 1, Procedure SDCalls, Line 6
Invalid column name 'i'.
Msg 207, Level 16, State 1, Procedure SDCalls, Line 6
Invalid column name 't'.
Msg 207, Level 16, State 1, Procedure SDCalls, Line 3
Invalid column name 'i'.
Msg 207, Level 16, State 1, Procedure SDCalls, Line 3
Invalid column name 'IncidentNumber'.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 06:44:05

ok -- it's *your* database. Can you not find out which columns are in which tables?

The JOIN is the important thing in your code.

This part


FROM
	dbo.Incident AS i
	JOIN dbo.Task AS t ON i/t.[Recid] = t/i.[ParentLink_RecID]

is where the error was thrown originally.

You alias a table with the AS keyworkd example (dbo.Incident AS i) then you can refer to the table with the alias. (you can also skip the AS keyword but I think it's more readable).

Lets get this query to work first



SELECT COUNT(DISTINCT i.[IncidentNumber]) As TotalCalls
FROM
	dbo.Incident AS i
	JOIN dbo.Task AS t ON i/t.[Recid] = t/i.[ParentLink_RecID]
WHERE
	i.[Status] IN ('Active','Waiting')
	AND t.[OwnerTeam] = 'Sales'
GO

So we need to fix the join conditions -- pick either i or t for


ON i/t.[Recid] = t/i.[ParentLink_RecID]

You should be able to find out which table each of those columns is in.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-02-23 : 06:53:52

Aha...this executes ok!

Hows that look now?

i = Incident table for IncidentNumber column
t = Task table for OwnerTeam column

So,

USE [Dev]
Go
/****** Object: View [dbo].[LastDigit] Script Date: 02/22/2012 14:59:13 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Alter View [dbo].[SDCalls]
AS

SELECT COUNT(Distinct i.[IncidentNumber]) As TotalCalls
FROM
dbo.Incident AS i
Join dbo.Task AS t ON i.[Recid] = t.[ParentLink_RecID]
Where
i.[Status] IN ('Active','Waiting')
And t.[OwnerTeam] = 'Sales'

GO

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 07:15:10

looks good to me.

your second query should probably be:


SELECT
	i.[Status] AS [IncidentStatus]
	COUNT(DISTINCT i.[IncidentNumber]) AS [TotalCalls]
FROM
	dbo.Incident AS i
	JOIN dbo.Task AS t ON t.[ParentLink_RecID] = i.[RecID]
WHERE
	t.[OwnerTeam] = 'Sales'
	AND i.[Status] IN ('Active','Waiting')
GROUP BY
	i.[Status]

The HAVING clause is a bit meaningless because you aren't filtering on an aggregate result.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-02-23 : 07:20:37

Cool, thanks a lot Charlie
Much arrreciated, those alias's are really slick!

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-03-21 : 08:53:44

Hey

I have another issue with this query. I'm getting too many counts but this is because the t.[OwnerTeam] = 'Sales' can appear multiple times assignment wise. so, think I need another distinct or count only once when Sales record found in OwnerTeam NOT Multiple...
can someone advise?
Thanks

ALTER View [dbo].[STCalls]
AS

SELECT COUNT(Distinct i.[IncidentNumber]) As [Live ITSM Calls]
FROM
dbo.Incident AS i
Join dbo.Task AS t ON i.[Recid] = t.[ParentLink_RecID]
Where
i.[Status] IN ('Active','Waiting')
And t.[OwnerTeam] = 'Sales'

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources