Columns reference to outer table in Virtual Columns

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

SQL Server Development (2000)

Columns reference to outer table in Virtual Columns

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-30 : 09:23:11

Aviad Harell writes "Hi,
I have a select query with one calculated column in the select column
collection. When I change the select FROM clause from table name to a table
defined with select statement, I get error. The query is:

DECLARE @ProductsSet TABLE (
[PRODUCTS] VARCHAR(8000))

INSERT @ProductsSet
SELECT [PRODUCTS]
FROM [DREK_VIEW]
WHERE [PRODUCTS] = N'IES XXI JK'
OR [PRODUCTS] = N'Troy Sys 4'
OR [PRODUCTS] = N'Core Series 12'
GROUP BY [PRODUCTS]

SELECT [TIMELEVELTABLE].[DISTRIBUTION CENTER],
[TIMELEVELTABLE].[YEARCOLTIME],
(SUM(CAST([TIMELEVELTABLE].[SALES AMT] AS FLOAT))) AS [AGGREGATEDSALES AMT],
(SELECT AVG([SALES AMT])
FROM (SELECT (SUM(CAST([FORMULATIMELEVELTABLE].[SALES AMT] AS FLOAT))) AS [SALES AMT]
FROM (SELECT *,
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [DREK_VIEW]) AS [FORMULATIMELEVELTABLE]
INNER JOIN @ProductsSet AS [@PRODUCTSSET]
ON [@PRODUCTSSET].[PRODUCTS] = [FORMULATIMELEVELTABLE].[PRODUCTS]
WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] = [FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER]
AND [TIMELEVELTABLE].[YEARCOLTIME] = [FORMULATIMELEVELTABLE].[YEARCOLTIME]
GROUP BY [@PRODUCTSSET].[PRODUCTS]) AS [FUNCTIONTABLE]) AS [AGGREGATEDFORMULA0]
FROM (SELECT *,
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [DREK_VIEW]) AS [TIMELEVELTABLE]
GROUP BY [TIMELEVELTABLE].[DISTRIBUTION CENTER],
[TIMELEVELTABLE].[YEARCOLTIME]

The error i get is :
Server: Msg 207, Level 16, State 3, Line 12
Invalid column name 'DISTRIBUTION CENTER'.
Server: Msg 207, Level 16, State 1, Line 12
Invalid column name 'YEARCOLTIME'.

somehow it doesn’t "recognize" the columns:
[TIMELEVELTABLE].[DISTRIBUTION CENTER] AND [TIMELEVELTABLE].[YEARCOLTIME] in the row:
WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] = [FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER]
AND [TIMELEVELTABLE].[YEARCOLTIME] = [FORMULATIMELEVELTABLE].[YEARCOLTIME]
Which in the calculated column.

the DB script is:
object_id(N'[dbo].[DREK_VIEW]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DREK_VIEW]
GO

CREATE TABLE [dbo].[DREK_VIEW] (
[Products] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Distribution Center] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Sales Amt] [float] NULL ,
[Time] [datetime] NULL ,
) ON [PRIMARY]
GO

im using sql server 2000 sp4 and windows xp

Im trying to find an answer to the problem for a long time, please help!!!!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources