JOIN with select statement causes Server: Msg 207

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 with select statement causes Server: Msg 207

Author

Topic

ewolbers
Starting Member

9 Posts

Posted - 2010-03-31 : 10:02:52

Hi, I query from MSSQL2008 Express using SYNONYMS to remote SQL2000 server with the following query. The query parts (al select statements) work perfectly on their own. If I skip the last JOIN or skip the JOIN before the last one, there is no problem... I can't figure out whats wrong.... The original query works perfectly on MSSQL2000.

The error I get is:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Col1102'.

The query:

DECLARE @project CHAR(5)
SET @project = '12345'

SELECT Ordergroup = ProjectGroup.ordergroup + ': ' + RTRIM(ProjectGroup.[description]),
Projectnr = Project.ordernumber,
[Description] = Project.[description],
Coordinator = Employee.fullname,
Customer = Customer.customer,
BudgetGeneral = CONVERT( MONEY, IbisBudget.BudgetGeneral ),
BookedGeneral = CONVERT( MONEY, IbisBooked.BookedGeneral )
---------------------------------------------------------------------
FROM ibisOrders Project
JOIN ibisEmployees Employee ON Employee.employee = Project.coordinator
JOIN ibisCustomers Customer ON Customer.[id] = Project.customerid
JOIN ibisBHS_Ordergroups ProjectGroup ON ProjectGroup.ordergroup = LEFT( Project.ordernumber, 2 )
---------------------------------------------------------------------
JOIN (
SELECT Project = Project.ordernumber,
BookedGeneral = SUM( CASE Tasks.costtypeid WHEN 1 THEN BookedHours.tarif * BookedHours.total / 60 ELSE 0 END )
FROM ibisOrders Project
LEFT JOIN ibisTasks Tasks ON Tasks.orderid = Project.[id]
LEFT JOIN ibisBHS_Weekreport BookedHours ON BookedHours.taskid = Tasks.[id]
WHERE BookedHours.tarif > 0
AND BookedHours.total > 0
AND BookedHours.weekinput BETWEEN '0926' AND '2000'
AND Project.ordernumber = @project
GROUP BY Project.ordernumber
) IbisBooked ON IbisBooked.Project = Project.ordernumber
---------------------------------------------------------------------
JOIN (
SELECT Project = ProjectInfo.Ordernumber,
BudgetGeneral = SUM ( ProjectBudget.General_Budget )
FROM ibisProject_Information ProjectInfo
JOIN ibisProject_Budget ProjectBudget ON ProjectBudget.Project_Information_ID = ProjectInfo.[ID]
WHERE ProjectBudget.[ID] = (
SELECT MAX( BudgetMax.[ID] )
FROM SCRABBY.ibis.dbo.Project_Budget BudgetMax
WHERE BudgetMax.Budget_Action_ID IN ( 1, 2 )
AND BudgetMax.Project_Information_ID = ProjectInfo.[ID]
GROUP BY BudgetMax.Project_Information_ID
)
AND ProjectInfo.Ordernumber = @project
AND ProjectInfo.Fase_Start_Week BETWEEN '0926' AND '2000'
GROUP BY ProjectInfo.Ordernumber
) IbisBudget ON IbisBudget.Project = Project.ordernumber

Help appreciated.

Erik

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 10:11:07

Have you pasted the full code? I don't see column Col1102 in the query.

Harsh Athalye
http://www.letsgeek.net/

ewolbers
Starting Member

9 Posts

Posted - 2010-03-31 : 10:13:36

YEP! This is the complete query.

That's the part I'm confused about.......

ewolbers
Starting Member

9 Posts

Posted - 2010-04-06 : 03:10:13

No ideas anyone?

Erik

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-06 : 03:30:19

Your code is horrible formatted. I can't figure out which JOIN you are talking about.
You mention that there are synonyms involved. Recheck them and see if they are pointing to the desired target.

N 56°04'39.26"
E 12°55'05.63"

ewolbers
Starting Member

9 Posts

Posted - 2010-04-06 : 04:32:16

There is no problem in the synonyms.

Which join statement?
Exactly as I describe: if I skip the very last join part, I don't get the error.
But, if I keep that last join statement and I skip the join statement before the last join, I also do NOT get errors.

I have succesfully tested every single select statement inside the last two joins and they do what they are supposed to do.

Even better: the complete query works perfect directly on a sql2000 db. So, my guess is a difference in coding when using sql2008?

The error I get does NOT help me, because it is mentioning a column with no name from the query and refers to line number 1.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-06 : 05:14:21

I've formatted your code a little


DECLARE @project CHAR(5)
SET @project = '12345'

SELECT
	Ordergroup = ProjectGroup.ordergroup + ': ' + RTRIM(ProjectGroup.[description])
	, Projectnr = Project.ordernumber
	, [Description] = Project.[description]
	, Coordinator = Employee.fullname
	, Customer = Customer.customer
	, BudgetGeneral = CONVERT( MONEY, IbisBudget.BudgetGeneral )
	, BookedGeneral = CONVERT( MONEY, IbisBooked.BookedGeneral )
FROM
	ibisOrders Project
	JOIN ibisEmployees Employee ON Employee.employee = Project.coordinator
	JOIN ibisCustomers Customer ON Customer.[id] = Project.customerid
	JOIN ibisBHS_Ordergroups ProjectGroup ON ProjectGroup.ordergroup = LEFT( Project.ordernumber, 2 )

	JOIN (
		SELECT
			Project = Project.ordernumber
			, BookedGeneral = SUM( CASE Tasks.costtypeid WHEN 1 THEN BookedHours.tarif * BookedHours.total / 60 ELSE 0 END )
		FROM
			ibisOrders Project
			LEFT JOIN ibisTasks Tasks ON Tasks.orderid = Project.[id]
			LEFT JOIN ibisBHS_Weekreport BookedHours ON BookedHours.taskid = Tasks.[id]
		WHERE
			BookedHours.tarif > 0 
			AND BookedHours.total > 0
			AND BookedHours.weekinput BETWEEN '0926' AND '2000'
			AND Project.ordernumber = @project
		GROUP BY
			Project.ordernumber 
		)
		IbisBooked ON IbisBooked.Project = Project.ordernumber

	JOIN (
		SELECT
			Project = ProjectInfo.Ordernumber
			, BudgetGeneral = SUM ( ProjectBudget.General_Budget )
		FROM
			ibisProject_Information ProjectInfo
			JOIN ibisProject_Budget ProjectBudget ON ProjectBudget.Project_Information_ID = ProjectInfo.[ID]
		WHERE
			ProjectBudget.[ID] = (
				SELECT MAX( BudgetMax.[ID] )
				FROM SCRABBY.ibis.dbo.Project_Budget BudgetMax
				WHERE
					BudgetMax.Budget_Action_ID IN ( 1, 2 )
					AND BudgetMax.Project_Information_ID = ProjectInfo.[ID]
				GROUP BY
					BudgetMax.Project_Information_ID
				)
			AND ProjectInfo.Ordernumber = @project
			AND ProjectInfo.Fase_Start_Week BETWEEN '0926' AND '2000'
		GROUP BY ProjectInfo.Ordernumber
		)
		IbisBudget ON IbisBudget.Project = Project.ordernumber

Are any of the objects referenced actually views?

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

ewolbers
Starting Member

9 Posts

Posted - 2010-04-06 : 05:25:10

Nice formatting (I have actually almost the same formatting but this went wrong when copy/paste to this forum). I will check in the future....

There are no views involved in this query.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-06 : 05:35:35

What happens if you use the same FROM clause but change the SELECT to a SELECT *

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

ewolbers
Starting Member

9 Posts

Posted - 2010-04-06 : 06:16:34

If I change the select in the last join as requested, I get the following (expected) errors:

Server: Msg 207, Level 16, State 1, Line 57
Invalid column name 'Project'.
Server: Msg 207, Level 16, State 1, Line 10
Invalid column name 'BudgetGeneral'.
Server: Msg 8120, Level 16, State 1, Line 4
Column 'ibisProject_Information.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8156, Level 16, State 1, Line 57
The column 'ID' was specified multiple times for 'IbisBudget'.

I just noticed that when I select an @project with no data to be returned, I do NOT get any errors using the original query. The error occurs if the query wants to return actual data.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-06 : 06:35:05

Oh sorry -- I meant change the top SELECT to a select *.

I was wondering if because of the synonyms virtual columns are returned with those names. (col1, col2 etc).

what happens if you replace the synonyms with regular 4 part dot notation for the table names? (in the last join)

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

ewolbers
Starting Member

9 Posts

Posted - 2010-04-06 : 07:12:27

Ok, with the other select changed:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Col1181'.
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.

Last join without use synonyms:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Col1102'.
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-06 : 07:21:07

Sorry dude. I'm out of ideas. I've never had to do what you are trying to do and I can't see anything wrong with the statement.

My guess is that an internal worktable is created but I've got no idea why the column names aren't working.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-06 : 07:34:33

Can you post thw execution plan?

N 56°04'39.26"
E 12°55'05.63"

ewolbers
Starting Member

9 Posts

Posted - 2010-04-06 : 08:25:28

Ah, good question.
I found the part in the "remote" query which refers Col1102.
It looks a bit horrible:

SELECT
"Tbl1018"."General_Budget" "Col1100"
FROM
"ibis"."dbo"."Project_Budget" "Tbl1018"
,(SELECT
"Tbl1016"."ID" "Col1102"
,"Tbl1016"."Ordernumber" "Col1103"
,"Tbl1016"."Fase_Start_Week" "Col1104"
FROM
"ibis"."dbo"."Project_Information" "Tbl1016"
WHERE
"Tbl1016"."Ordernumber"=?
AND
"Tbl1016"."Ordernumber"=?
AND
"Tbl1016"."Fase_Start_Week">=N'0926'
AND
"Tbl1016"."Fase_Start_Week"<=N'2000') Qry1105
WHERE
"Col1102"="Tbl1018"."Project_Information_ID"
AND
"Tbl1018"."ID"=(SELECT "Expr1022"
FROM
(SELECT
"Col1108","Expr1021","Expr1021" "Expr1022"
FROM
(SELECT
"Tbl1020"."Project_Information_ID" "Col1108"
,MAX("Tbl1020"."ID") "Expr1021"
FROM
"ibis"."dbo"."Project_Budget" "Tbl1020"
WHERE
("Tbl1020"."Budget_Action_ID"=(2)
OR
"Tbl1020"."Budget_Action_ID"=(1))
AND
"Tbl1020"."Project_Information_ID"="Col1102"

GROUP BY "Tbl1020"."Project_Information_ID") Qry1110) "Subquery_Source_Tbl" )

I will check against the query, but maybe you're much faster....

ewolbers
Starting Member

9 Posts

Posted - 2010-04-06 : 08:31:29

damn, formatting screwed again...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-06 : 08:33:22

eclose the tags code and <backslash> code inside square brackets:


SELECT 
  "Tbl1018"."General_Budget" "Col1100" 
FROM 
  "ibis"."dbo"."Project_Budget" "Tbl1018"
  ,(SELECT 
      "Tbl1016"."ID" "Col1102"
      ,"Tbl1016"."Ordernumber" "Col1103"
      ,"Tbl1016"."Fase_Start_Week" "Col1104" 
    FROM 
      "ibis"."dbo"."Project_Information" "Tbl1016" 
    WHERE 
      "Tbl1016"."Ordernumber"=? 
    AND 
      "Tbl1016"."Ordernumber"=? 
    AND 
      "Tbl1016"."Fase_Start_Week">=N'0926' 
    AND 
      "Tbl1016"."Fase_Start_Week"<=N'2000') Qry1105 
WHERE 
  "Col1102"="Tbl1018"."Project_Information_ID" 
AND 
  "Tbl1018"."ID"=(SELECT "Expr1022" 
                  FROM 
                    (SELECT 
                      "Col1108","Expr1021","Expr1021" "Expr1022" 
                    FROM 
                      (SELECT 
                         "Tbl1020"."Project_Information_ID" "Col1108"
                         ,MAX("Tbl1020"."ID") "Expr1021"    
                       FROM 
                         "ibis"."dbo"."Project_Budget" "Tbl1020" 
                       WHERE 
                         ("Tbl1020"."Budget_Action_ID"=(2)
                           OR 
                           "Tbl1020"."Budget_Action_ID"=(1)) 
                       AND 
                         "Tbl1020"."Project_Information_ID"="Col1102"
 
GROUP BY "Tbl1020"."Project_Information_ID") Qry1110) "Subquery_Source_Tbl" )

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources