Joining 2 quieries problem - 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 2000 Forums

Transact-SQL (2000)

Joining 2 quieries problem

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2006-02-09 : 16:42:55

I have 2 queries that I need to combine into 1. Both queries group on the following Saturday of a datetime column in different tables. I need to join the queries on the grouped column. My following SQL gives me the following error, which is probably some sort of syntax problem:

Server: Msg 207, Level 16, State 3, Line 5
Invalid column name 'WQEndWeekDate'.


USE ChemWatch
DECLARE @CustNo int
SET @CustNo = 12186

SELECT  DateAdd(day, -2, DateAdd(week, DateDiff(week, 0,WeightQuery.BOF_Date)+1, 0)) AS WQEndWeekDate,
	SUM(WeightQuery.LF_WT_Target) AS Target_WT,
	SUM(WeightQuery.Actual_WT) AS Actual_WT,
	SUM(MeterQuery.CwebQty) AS CWebQty,
	SUM(MeterQuery.CustQty) AS CustQty
FROM Washrun AS WeightQuery
LEFT JOIN (
    SELECT DateAdd(day, -2, DateAdd(week, DateDiff(week, 0, [TimeStamp])+1, 0)) AS MDEndWeekDate,
	Meters.MeterType,
	Meters.SubType,
	MeterData.CustQty,
	MeterData.CWebQTY
	FROM MeterData
	INNER Join Meters ON Meters.Customer_No = MeterData.Customer_No AND
		Meters.MeterID = MeterData.MeterID
	WHERE MeterData.Customer_No = @CustNo
		AND MeterData.TimeStamp Between '10/30/2005' AND '12/04/2005'
		AND Meters.cWebReports = 1
    )AS MeterQuery
ON MeterQuery.MDEndWeekDate = WeightQuery.WQEndWeekDate
WHERE WeightQuery.Customer_No = @CustNo
	AND WeightQuery.BOF_DATE Between '10/30/2005' AND '12/04/2005' 
	AND (WeightQuery.EOF_Date > WeightQuery.BOF_Date OR WeightQuery.Actual_WT > 0)
GROUP BY DateAdd(day, -2, DateAdd(week, DateDiff(week, 0,WeightQuery.BOF_Date)+1, 0))
ORDER BY 1

If you know of a sample of how to do this in BOL, please let me know.

TIA,

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-09 : 17:24:56

You may or may not have other issues but this specific error is because you can't join on a columnAlias. You must use the expression itself.


--generates the error
select	x + y as colAlias
from	(select 1 as x, 1 as y) a
join	(select 2 as z) b
		on b.z = a.colAlias

--is ok
select	x + y as colAlias
from	(select 1 as x, 1 as y) a
join	(select 2 as z) b
		on b.z = x + y

EDIT:
added the color for clarification :)
Be One with the Optimizer
TG

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2006-02-13 : 08:43:08

Thanks TG, that was the problem!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources