Error 'Invalid Column Name' - 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

General SQL Server Forums

New to SQL Server Programming

Error 'Invalid Column Name'

Author

Topic

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-10-02 : 11:12:06

The reference to QReceived below in the QUsageQty line gives me an error: Invalid Column Name 'QReceived'. Is there a way to reference that field?

SELECT
	MEND.ProductID,
	MEND.MEPeriod,
	MEND.OpeningQty,
	QOpenCost = MEND.OpeningDols / MEND.OpeningQty, 
	(SELECT Sum(UsageQty) FROM tblShipmentHdr SHPH WHERE MEND.ProductID = LEFT(SHPH.ProductID,7) And 
		DATEADD(mm, DATEDIFF(mm,0,SHPH.ReceivedDate), 0) = MEND.MEPeriod GROUP BY LEFT(SHPH.ProductID,7)) AS QReceived, 
	QUsageQty = MEND.OpeningQty + QReceived - MEND.ClosingQty,
	PROD.ProductName
FROM tblMonthend MEND
LEFT OUTER JOIN dbo.tblProducts as PROD ON MEND.ProductID = PROD.ProductID
WHERE (MEND.MEPeriod =''' + convert(varchar(40),@XFromDate,121) + ''')

nr
SQLTeam MVY

12543 Posts

Posted - 2005-10-02 : 12:52:11

Because QReceived isn't available as an identifier until the resultset is produced i.e. can't be used to buildthe resultset


select ProductID
	ProductID,
	MEPeriod,
	OpeningQty,
	QOpenCost ,
	QUsageQty = OpeningQty + QReceived - ClosingQty,
	ProductName
from
(
	SELECT
		MEND.ProductID,
		MEND.MEPeriod,
		MEND.OpeningQty,
		QOpenCost = MEND.OpeningDols / MEND.OpeningQty, 
		QReceived = (SELECT Sum(UsageQty) FROM tblShipmentHdr SHPH WHERE MEND.ProductID = LEFT(SHPH.ProductID,7) And 
			DATEADD(mm, DATEDIFF(mm,0,SHPH.ReceivedDate), 0) = MEND.MEPeriod GROUP BY LEFT(SHPH.ProductID,7)) , 
		PROD.ProductName ,
		MEND.ClosingQty
	FROM tblMonthend MEND
	LEFT OUTER JOIN dbo.tblProducts as PROD ON MEND.ProductID = PROD.ProductID
	WHERE (MEND.MEPeriod =''' + convert(varchar(40),@XFromDate,121) + ''')
) a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-10-02 : 15:44:55

I have done what you suggested, which does work except that I use the 'Exec @SQL' line in my stored procedure and the following line is now giving me problems:

DATEADD(mm, DATEDIFF(mm,0,SHPH.ReceivedDate), 0) = MEND.MEPeriod

Invalid SQL Statement

Is there a rule that does not allow functions in a nested SELECT stmt when using 'Exec'?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-10-02 : 23:53:51

Nope - why are you using dynamic sql?
Try displaying the string to see what's executing. Could be the string isn't long enough or something else.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-10-04 : 12:48:22

I'm using dynamic SQL because I pass a FILTER string (WHERE clause) to the procedure. It can be a complex string. I split the procedure into 2 sections, using a temp file and got it to work that way.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources