column prefix does not match - 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

column prefix does not match

Author

Topic

foxgolfer
Starting Member

2 Posts

Posted - 2006-10-02 : 20:04:52

SQL 2000
I am testing a query for use in Crystal Reports. It was copied from an existing query with the necessary adjustments. The first part of it works correctly;

SELECT NA.*
into #cl_temp
FROM OLT.dbo.NACBTR NA
WHERE NA.CourseCode in ('RGF00001','RGF00002','RGF00005','RGF00006', 'RGF00038','RGF00039','RGF00040','RGF00041','RGF00042','RGF00043') And
NA.completedDate >= '01/01/2006' and
NOT EXISTS (SELECT * FROM hrdw.dbo.E_View EV WHERE NA.ssn = EV.ssn)

but when I add the second line;

select #cl_temp.*,
ISNULL((select 1 from #cl_temp where #cl_temp.coursecode = 'RGF00001'),0) as fire_yes into #oshasafety_temp

I receive the error message:
The column prefix '#cl_temp' does not match with a table name or alias name used in the query.

Any ideas?
Thanks,
Tom
btw, sql newbie.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-02 : 20:11:07

quote:
Originally posted by foxgolfer
...
select #cl_temp.*,
ISNULL((select 1 from #cl_temp where #cl_temp.coursecode = 'RGF00001'),0) as fire_yes into #oshasafety_temp

I receive the error message:
The column prefix '#cl_temp' does not match with a table name or alias name used in the query.
...

The error message says it all. Table #cl_temp is not used in your main select statement; in fact, the main select doesn't even have a from clause. Table #cl_temp is only named in the subquery.

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-02 : 20:49:45

Can you explain what you are doing with this?

select #cl_temp.*,
ISNULL((select 1 from #cl_temp where #cl_temp.coursecode = 'RGF00001'),0) as fire_yes into #oshasafety_temp

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 00:56:54

You can't select from a table that you are creating, within the same statement.

Peter Larsson
Helsingborg, Sweden

foxgolfer
Starting Member

2 Posts

Posted - 2006-10-03 : 09:41:45

I mistyped the second line; it should read:

select #cl_temp.*,
ISNULL((select 1 from #cl_temp where #cl_temp.coursecode = 'RGF00001'),0) as fire_yes into #oshasafety_temp from #cl_temp

but it still returns the same error. The purpose it id the non-employees that have taken certain osha safety courses. The second line is to add a new field, fire_yes with a value of 1 if the person has taken the specific course, RGF00001. This field can then be summed with other courses in a CR report.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-03 : 09:46:13

select #cl_temp.*,
ISNULL((select 1 where NA.coursecode = 'RGF00001'),0) as fire_yes into #oshasafety_temp from #cl_temp

Something like that, I think. PITA to wade through the messy handwriting.

Jay White

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources