Hi,
In 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 @.YearsSet TABLE (
[YEARCOLTIME] VARCHAR(8000))
INSERT @.YearsSet
SELECT [YEARCOLTIME]
FROM (SELECT *,
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [MY_TABLE]) AS [TIMELEVELTABLE]
WHERE [YEARCOLTIME] = N'2000'
OR [YEARCOLTIME] = N'2001'
GROUP BY [YEARCOLTIME]
DECLARE @.ProductsSet TABLE (
[PRODUCTS] VARCHAR(8000))
INSERT @.ProductsSet
SELECT [PRODUCTS]
FROM [MY_TABLE]
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],
[@.YEARSSET].[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 [MY_TABLE]) AS [FORMULATIMELEVELTABLE]
INNER JOIN @.ProductsSet AS [@.PRODUCTSSET]
ON [@.PRODUCTSSET].[PRODUCTS] =
[FORMULATIMELEVELTABLE].[PRODUCTS]
WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] =
[FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER]
AND [@.YEARSSET].[YEARCOLTIME] =
[FORMULATIMELEVELTABLE].[YEARCOLTIME]
GROUP BY [@.PRODUCTSSET].[PRODUCTS]) AS [FUNCTIONTABLE]) AS
[AGGREGATEDFORMULA0]
FROM (SELECT *,
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [MY_TABLE]) AS [TIMELEVELTABLE]
INNER JOIN @.YearsSet AS [@.YEARSSET]
ON [@.YEARSSET].[YEARCOLTIME] = [TIMELEVELTABLE].[YEARCOLTIME]
GROUP BY [TIMELEVELTABLE].[DISTRIBUTION CENTER],
[@.YEARSSET].[YEARCOLTIME]
(Please copy paste the query somewhere else, it’s much easier to read and
understand the problem)
The error I get is:
Server: Msg 207, Level 16, State 3, Line 24
Invalid column name 'DISTRIBUTION CENTER'.Can you replace the * with the column names and then try executing it and
repaste the query if it doesn't work (with the error message).
Thanks
Omnibuzz|||Hi Omnibuzz, thanks for the quick response, but its not working. Here is the
query again:
DECLARE @.YearsSet TABLE (
[YEARCOLTIME] VARCHAR(8000))
INSERT @.YearsSet
SELECT [YEARCOLTIME]
FROM (SELECT *,
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [MY_TABLE]) AS [TIMELEVELTABLE]
WHERE [YEARCOLTIME] = N'2000'
OR [YEARCOLTIME] = N'2001'
GROUP BY [YEARCOLTIME]
DECLARE @.ProductsSet TABLE (
[PRODUCTS] VARCHAR(8000))
INSERT @.ProductsSet
SELECT [PRODUCTS]
FROM [MY_TABLE]
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],
[@.YEARSSET].[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 [PRODUCTS],
[DISTRIBUTION CENTER],
[SALES AMT],
(CAST(YEAR([TIME]) AS VARCHAR)) AS
[YEARCOLTIME]
FROM [MY_TABLE]) AS [FORMULATIMELEVELTABLE]
INNER JOIN @.ProductsSet AS [@.PRODUCTSSET]
ON [@.PRODUCTSSET].[PRODUCTS] =
[FORMULATIMELEVELTABLE].[PRODUCTS]
WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] =
[FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER]
AND [@.YEARSSET].[YEARCOLTIME] =
[FORMULATIMELEVELTABLE].[YEARCOLTIME]
GROUP BY [@.PRODUCTSSET].[PRODUCTS]) AS [FUNCTIONTABLE]) AS
[AGGREGATEDFORMULA0]
FROM (SELECT [PRODUCTS],
[DISTRIBUTION CENTER],
[SALES AMT],
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [MY_TABLE]) AS [TIMELEVELTABLE]
INNER JOIN @.YearsSet AS [@.YEARSSET]
ON [@.YEARSSET].[YEARCOLTIME] = [TIMELEVELTABLE].[YEARCOLTIME]
GROUP BY [TIMELEVELTABLE].[DISTRIBUTION CENTER],
[@.YEARSSET].[YEARCOLTIME]
"Omnibuzz" wrote:
> Can you replace the * with the column names and then try executing it and
> repaste the query if it doesn't work (with the error message).
> Thanks
> Omnibuzz|||Sorry, this is the updated query:
DECLARE @.YearsSet TABLE (
[YEARCOLTIME] VARCHAR(8000))
INSERT @.YearsSet
SELECT [YEARCOLTIME]
FROM (SELECT [PRODUCTS],
[DISTRIBUTION CENTER],
[SALES AMT],
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [MY_TABLE]) AS [TIMELEVELTABLE]
WHERE [YEARCOLTIME] = N'2000'
OR [YEARCOLTIME] = N'2001'
GROUP BY [YEARCOLTIME]
DECLARE @.ProductsSet TABLE (
[PRODUCTS] VARCHAR(8000))
INSERT @.ProductsSet
SELECT [PRODUCTS]
FROM [MY_TABLE]
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],
[@.YEARSSET].[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 [PRODUCTS],
[DISTRIBUTION CENTER],
[SALES AMT],
(CAST(YEAR([TIME]) AS VARCHAR)) AS
[YEARCOLTIME]
FROM [MY_TABLE]) AS [FORMULATIMELEVELTABLE]
INNER JOIN @.ProductsSet AS [@.PRODUCTSSET]
ON [@.PRODUCTSSET].[PRODUCTS] =
[FORMULATIMELEVELTABLE].[PRODUCTS]
WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] =
[FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER]
AND [@.YEARSSET].[YEARCOLTIME] =
[FORMULATIMELEVELTABLE].[YEARCOLTIME]
GROUP BY [@.PRODUCTSSET].[PRODUCTS]) AS [FUNCTIONTABLE]) AS
[AGGREGATEDFORMULA0]
FROM (SELECT [PRODUCTS],
[DISTRIBUTION CENTER],
[SALES AMT],
(CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
FROM [MY_TABLE]) AS [TIMELEVELTABLE]
INNER JOIN @.YearsSet AS [@.YEARSSET]
ON [@.YEARSSET].[YEARCOLTIME] = [TIMELEVELTABLE].[YEARCOLTIME]
GROUP BY [TIMELEVELTABLE].[DISTRIBUTION CENTER],
[@.YEARSSET].[YEARCOLTIME]
"Omnibuzz" wrote:
> Can you replace the * with the column names and then try executing it and
> repaste the query if it doesn't work (with the error message).
> Thanks
> Omnibuzz|||Can you post the create script for my_table.
"Aviad" wrote:
> Hi Omnibuzz, thanks for the quick response, but its not working. Here is t
he
> query again:
> DECLARE @.YearsSet TABLE (
> [YEARCOLTIME] VARCHAR(8000))
> INSERT @.YearsSet
> SELECT [YEARCOLTIME]
> FROM (SELECT *,
> (CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
> FROM [MY_TABLE]) AS [TIMELEVELTABLE]
> WHERE [YEARCOLTIME] = N'2000'
> OR [YEARCOLTIME] = N'2001'
> GROUP BY [YEARCOLTIME]
> DECLARE @.ProductsSet TABLE (
> [PRODUCTS] VARCHAR(8000))
> INSERT @.ProductsSet
> SELECT [PRODUCTS]
> FROM [MY_TABLE]
> 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],
> [@.YEARSSET].[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 [PRODUCTS],
> [DISTRIBUTION CENTER],
> [SALES AMT],
> (CAST(YEAR([TIME]) AS VARCHAR)) AS
> [YEARCOLTIME]
> FROM [MY_TABLE]) AS [FORMULATIMELEVELTABLE]
> INNER JOIN @.ProductsSet AS [@.PRODUCTSSET]
> ON [@.PRODUCTSSET].[PRODUCTS] =
> [FORMULATIMELEVELTABLE].[PRODUCTS]
> WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] =
> [FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER]
> AND [@.YEARSSET].[YEARCOLTIME] =
> [FORMULATIMELEVELTABLE].[YEARCOLTIME]
> GROUP BY [@.PRODUCTSSET].[PRODUCTS]) AS [FUNCTIONTABLE]) AS
> [AGGREGATEDFORMULA0]
> FROM (SELECT [PRODUCTS],
> [DISTRIBUTION CENTER],
> [SALES AMT],
> (CAST(YEAR([TIME]) AS VARCHAR)) AS [YEARCOLTIME]
> FROM [MY_TABLE]) AS [TIMELEVELTABLE]
> INNER JOIN @.YearsSet AS [@.YEARSSET]
> ON [@.YEARSSET].[YEARCOLTIME] = [TIMELEVELTABLE].[YEARCOLTIME]
> GROUP BY [TIMELEVELTABLE].[DISTRIBUTION CENTER],
> [@.YEARSSET].[YEARCOLTIME]
>
> "Omnibuzz" wrote:
>|||Here it is:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[My_Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[My_Table]
GO
CREATE TABLE [dbo].[My_Table] (
[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 ,
[Col100] [int] NOT NULL
) ON [PRIMARY]
GO
"Omnibuzz" wrote:
> Can you post the create script for my_table.
>
> "Aviad" wrote:
>|||Hi Aviad,
The create table script was having a syntax error. Fixed it.
But here it seems to work fine in my machine :)
try removing the distribution center column from the select and the group by
of the final query and try..|||Hi,
Its not working here, somehow it doesn’t "recognize" the column:
[TIMELEVELTABLE].[DISTRIBUTION CENTER] in the row:
WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] =
[FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER].
Which in the calculated column.
"Omnibuzz" wrote:
> Hi Aviad,
> The create table script was having a syntax error. Fixed it.
> But here it seems to work fine in my machine :)
> try removing the distribution center column from the select and the group
by
> of the final query and try..
>|||and the fixed script:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[My_Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[My_Table]
GO
CREATE TABLE [dbo].[My_Table] (
[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
sorry :->
"Omnibuzz" wrote:
> Hi Aviad,
> The create table script was having a syntax error. Fixed it.
> But here it seems to work fine in my machine :)
> try removing the distribution center column from the select and the group
by
> of the final query and try..
>|||I remember someone posting something like this...
the query analyzer was taking the next line of the comment as commented or
something similar.
I am not able to see what the problem might be :(
few last resorts..
If MY_TABLE is really your table then rename the column with an "_" in
between and try to get rid of this square brackets.
try it in a new QA window (Never knew I could get down to this level :)
use it as "DISTRIBUTION CENTER" instead of sq bracks..
"Aviad" wrote:
> Hi,
> Its not working here, somehow it doesn’t "recognize" the column:
> [TIMELEVELTABLE].[DISTRIBUTION CENTER] in the row:
> WHERE [TIMELEVELTABLE].[DISTRIBUTION CENTER] =
> [FORMULATIMELEVELTABLE].[DISTRIBUTION CENTER].
> Which in the calculated column.
> "Omnibuzz" wrote:
>
end