Query resulting in a SQL error...

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)

Query resulting in a SQL error...

Author

Topic

bbondi
Starting Member

20 Posts

Posted - 2005-04-14 : 17:36:22

Here is fragment of my query that is getting an error that occurs at Line: 5, the SELECT statement:
select CommDemo.Demo_Id,
((sum(CommDemoHist.DL1_Sales)+ min(CommDemo.DL1_Sales))-(sum(CommDemoHist.DL1_APO_Sales) + min(CommDemo.DL1_APO_Sales)))
/((sum(CommDemoHist.DL1_Total_With_Pending)+ min(CommDemo.DL1_Total_With_Pending))
- (sum(CommDemoHist.DL1_APO_Total)+ min(CommDemo.DL1_APO_Total)))
from CommDemo
join CommDemoHist on CommDemo.Demo_Id = CommDemoHist.Demo_Id and
histdate >= @BeginYearDate and histdate < @Start
join countryconfig on countrycode = @CountryCode
=================================================================
Server: Msg 107, Level 16, State 2, Line 5
The column prefix 'CommDemoHist.DL1' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 5
The column prefix 'CommDemo.DL1' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 5
The column prefix 'CommDemo.DL1' does not match with a table name or alias name used in the query.

===================================================================
CommDemo is a table containing a column named DL1_Sales.
CommDemoHist is a table containing a column named DL1_Sales.
CommDemo also has a column named DL1_APO_Sales.

I can not understand why the errors occur, am I missing a comma or something?

Thanks for your time and comments,
Bob

P.S. Why do some references to SQL precede it with 'an' rather than 'a', i.e. "an SQL error has occured", rather than, "a SQL error has occured?" I always thought 'an' was used before consonants and not before vowels! Was I sleeping in part of the English class?

Do the Social Engineering teams need QA? Naw, let 'em have fun!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-14 : 17:42:39

Some people say a sequel server, others say an S-Q-L server. I say sequel.

Anyway, use aliases to help readability of your query:

SELECT t1.Column1, t2.Column3
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.Column1 = t2.Column1

Seeing as though you've only given us a fragment, the problem may not be in the part that you posted.

Tara

bbondi
Starting Member

20 Posts

Posted - 2005-04-14 : 17:55:22

Hi,
I'm working in SQA and the tables are being reorg'ed, so I'm looking at the old queries and trying to capture some data to use as a baseline once they are ready to roll-out the new tables. The query in question does not actually have the Declares and Set statments at the top, I put them there because these are set by the calling SP and to test just this quesry I had to setup things first.

But, here is the entire query if it helps to locate the error.
===========================================================
Declare @BeginYearDate datetime; Declare @Start datetime;
Declare @CountryCode char; Declare @NumMonths int;
Set @BeginYearDate = '1988-09-30 00:00:00.000'; Set @Start = '1988-09-30 00:00:00.000';
Set @CountryCode = 'US';
select CommDemo.Demo_Id,
((sum(CommDemoHist.DL1_Sales)+ min(CommDemo.DL1_Sales))-(sum(CommDemoHist.DL1_APO_Sales) + min(CommDemo.DL1_APO_Sales)))
/((sum(CommDemoHist.DL1_Total_With_Pending)+ min(CommDemo.DL1_Total_With_Pending))
- (sum(CommDemoHist.DL1_APO_Total)+ min(CommDemo.DL1_APO_Total)))
from CommDemo
join CommDemoHist on CommDemo.Demo_Id = CommDemoHist.Demo_Id and
histdate >= @BeginYearDate and histdate < @Start
join countryconfig on countrycode = @CountryCode
where CommDemo.status not in ('C','D')
group by CommDemo.Demo_Id
having (sum(isnull(CommDemoHist.DL1_Total_With_Pending, 0) -
isnull(CommDemoHist.DL1_APO_Total, 0)) +
min(isnull(CommDemo.DL1_Total_With_Pending, 0) -
isnull(CommDemo.DL1_APO_Total, 0))) /
@NumMonths >= min(DL1YTDAvgSalesMinDemo)
and sum(isnull(CommDemoHist.DL1_Sales, 0) -
isnull(CommDemoHist.DL1_APO_Sales, 0)) +
min(isnull(CommDemo.DL1.Sales, 0 ) -
isnull(CommDemoHist.DL1_APO_Sales, 0)) >=
min(DL1YTDAvgSalesMinSales) and
(sum(isnull(CommDemoHist.DL1_Total_With_Pending, 0)) +
min(isnull(CommDemo.DL1_Total_With_Pending, 0))) -
(sum(isnull(CommDemoHist.DL1_APO_Total, 0)) +
min(isnull(CommDemo.DL1_APO_Total, 0 ))) > 0
order by ((sum(isnull(CommDemoHist.DL1.Sales, 0)) +
min(isnull(CommDemo.DL1.Sales, 0 ))) -
(sum(isnull(CommDemoHist.DL1_APO_Sales, 0)) +
min(isnull(CommDemo.DL1_APO_Sales, 0)))) /
((sum(isnull(CommDemoHist.DL1_Total_With_Pending, 0)) +
min(isnull(CommDemoHist.DL1_APO_Total, 0)) +
min(isnull(CommDemo.DL1_APO_Total, 0)))) desc,
min(CommDemo.YTD_Personal_Sales) desc

Do the Social Engineering teams need QA? Naw, let 'em have fun!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-14 : 17:58:24

You've got typos towards the bottom of the having:

CommDemo.DL1.Sales
CommDemoHist.DL1.Sales

Tara

bbondi
Starting Member

20 Posts

Posted - 2005-04-14 : 18:18:35

No way! Way!!
Wish I didn't embarrass myself like that, hehe.

Domo,
Bob

Do the Social Engineering teams need QA? Naw, let 'em have fun!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources