Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL: Invalid Column Name

Posted on 2005-04-12
14
Medium Priority
?
807 Views
Last Modified: 2008-02-01
Hi,

im currently trying to do some work in sql that was started in access but now needs to be finished in sql as a bug in access means i cant get my rankings tow ork, but when i put the query in sql and run it after ensuring the sytax is ok i get this message:

Mircosoft[ODBC SQL Server Driver]: Invalid Column Name Extract1

but in access i was able to name my column extract1 with no problems, in fact ive tried changed the column alias in case extract was reserved by sql but again whatever alias name i use it will not except yet in access it's fine....ive used the sql syntax:

SELECT     dbo.GCSE.SetId, dbo.GCSE.StudentId, dbo.GCSE.ExamId, dbo.GCSE.ModuleId, dbo.GCSE.CriteriaData,
                      REPLACE(dbo.GCSE.CriteriaData, CHAR(10), '') AS EXTRACT1, REPLACE(EXTRACT1, '1', '') AS EXTRACT2, REPLACE(EXTRACT2, 2, '') AS GCSE,
                      dbo.qxStuds.CourseYear AS YEARGROUP
FROM         dbo.GCSE INNER JOIN
                      dbo.qxStuds ON dbo.GCSE.StudentId = dbo.qxStuds.StudentId
GROUP BY dbo.GCSE.SetId, dbo.GCSE.StudentId, dbo.GCSE.ExamId, dbo.GCSE.ModuleId, dbo.GCSE.CriteriaData, REPLACE([ CRITERIADATA ], CHAR(10), ''),
                      dbo.qxStuds.CourseYear
HAVING      (dbo.GCSE.SetId = [Current Ye]) AND (dbo.GCSE.ExamId = GCSE) AND (dbo.qxStuds.CourseYear = 12)
ORDER BY dbo.GCSE.StudentId

as you can predict my other column extract2 is also not liked.

what is going on? i thought by using sql i could get around the access ranking problem i have but now even the original query wont work- help!!

greg
0
Comment
Question by:Dubs
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13763028
You cannot use an alias to compute for another column.  You can try this:

SELECT     dbo.GCSE.SetId, dbo.GCSE.StudentId, dbo.GCSE.ExamId, dbo.GCSE.ModuleId, dbo.GCSE.CriteriaData,
                      REPLACE(REPLACE(REPLACE(dbo.GCSE.CriteriaData, CHAR(10), ''), '1', ''), 2, '') AS GCSE,
                      dbo.qxStuds.CourseYear AS YEARGROUP
FROM         dbo.GCSE INNER JOIN
                      dbo.qxStuds ON dbo.GCSE.StudentId = dbo.qxStuds.StudentId
GROUP BY dbo.GCSE.SetId, dbo.GCSE.StudentId, dbo.GCSE.ExamId, dbo.GCSE.ModuleId, dbo.GCSE.CriteriaData, REPLACE([ CRITERIADATA ], CHAR(10), ''),
                      dbo.qxStuds.CourseYear
HAVING      (dbo.GCSE.SetId = [Current Ye]) AND (dbo.GCSE.ExamId = GCSE) AND (dbo.qxStuds.CourseYear = 12)
ORDER BY dbo.GCSE.StudentId
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13763037
You might get a syntax error on the above.  Try this one:

SELECT     dbo.GCSE.SetId, dbo.GCSE.StudentId, dbo.GCSE.ExamId, dbo.GCSE.ModuleId, dbo.GCSE.CriteriaData,
                      REPLACE(REPLACE(REPLACE(dbo.GCSE.CriteriaData, CHAR(10), ''), '1', ''), 2, '') AS GCSE,
                      dbo.qxStuds.CourseYear AS YEARGROUP
FROM         dbo.GCSE INNER JOIN
                      dbo.qxStuds ON dbo.GCSE.StudentId = dbo.qxStuds.StudentId
GROUP BY dbo.GCSE.SetId, dbo.GCSE.StudentId, dbo.GCSE.ExamId, dbo.GCSE.ModuleId, dbo.GCSE.CriteriaData, REPLACE([ CRITERIADATA ], CHAR(10), ''),
                      dbo.qxStuds.CourseYear
HAVING      (dbo.GCSE.SetId = [Current Ye]) AND (dbo.GCSE.ExamId = REPLACE(REPLACE(REPLACE(dbo.GCSE.CriteriaData, CHAR(10), ''), '1', ''), 2, '') ) AND (dbo.qxStuds.CourseYear = 12)
ORDER BY dbo.GCSE.StudentId
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13763047
that's because at the time you are using the Replace fuction Extract1 is not recognized.

SELECT dbo.GCSE.SetId,
     dbo.GCSE.StudentId,
     dbo.GCSE.ExamId,
     dbo.GCSE.ModuleId,
     dbo.GCSE.CriteriaData,
     REPLACE(dbo.GCSE.CriteriaData, CHAR(10), '') AS EXTRACT1,
     REPLACE(REPLACE(dbo.GCSE.CriteriaData, CHAR(10), ''), '1', '') AS EXTRACT2,
     REPLACE(REPLACE(REPLACE(dbo.GCSE.CriteriaData, CHAR(10), ''), '1', ''), 2, '') AS GCSE,
     dbo.qxStuds.CourseYear AS YEARGROUP
FROM dbo.GCSE
INNER JOIN dbo.qxStuds ON dbo.GCSE.StudentId = dbo.qxStuds.StudentId
GROUP BY dbo.GCSE.SetId, dbo.GCSE.StudentId, dbo.GCSE.ExamId, dbo.GCSE.ModuleId, dbo.GCSE.CriteriaData, REPLACE([ CRITERIADATA ], CHAR(10), ''),
                      dbo.qxStuds.CourseYear
HAVING      (dbo.GCSE.SetId = [Current Ye]) AND (dbo.GCSE.ExamId = GCSE) AND (dbo.qxStuds.CourseYear = 12)
ORDER BY dbo.GCSE.StudentId
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

Author Comment

by:Dubs
ID: 13763261
thanks for this- hhmmm wonder why access lets you alias and then ref the alias within one query

cheers ill try this and let you know

greg
0
 

Author Comment

by:Dubs
ID: 13763334
sadly none of those options work- still get the same error- ill be very surprised if i have to write separate queries to make aliases first and then write a second query as it doesn't explain why access would accept it, annoying thing is id go back to access but access is failing on a ranking inconsistancy- i may go buy oracle :)

greg
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13763368
What does the REPLACE function in Access do?  I believe the error is in the REPLACE function.  Are you trying to remove the first x characters and replace it with an empty string?
0
 

Author Comment

by:Dubs
ID: 13763447
absolutly! and have to use replace rather than mid as im not always extracting the same amount of characters:

replace is a sql function according to the documentation im referring to...nothing like consistancy...

greg
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13763597
Try this one:

SELECT     dbo.GCSE.SetId, dbo.GCSE.StudentId, dbo.GCSE.ExamId, dbo.GCSE.ModuleId, dbo.GCSE.CriteriaData,
                      REPLACE(REPLACE(SUBSTRING(dbo.GCSE.CriteriaData, 11, LEN(dbo.GCSE.CriteriaData)), '1', ''), '2', '') AS GCSE,
                      dbo.qxStuds.CourseYear AS YEARGROUP
FROM         dbo.GCSE INNER JOIN
                      dbo.qxStuds ON dbo.GCSE.StudentId = dbo.qxStuds.StudentId
GROUP BY dbo.GCSE.SetId, dbo.GCSE.StudentId, dbo.GCSE.ExamId, dbo.GCSE.ModuleId, dbo.GCSE.CriteriaData, SUBSTRING([ CRITERIADATA ], 11, LEN([ CRITERIADATA ])),
                      dbo.qxStuds.CourseYear
HAVING      (dbo.GCSE.SetId = [Current Ye]) AND (dbo.GCSE.ExamId = REPLACE(REPLACE(SUBSTRING(dbo.GCSE.CriteriaData, 11, LEN(dbo.GCSE.CriteriaData)), '1', ''), '2', '') ) AND (dbo.qxStuds.CourseYear = 12)
ORDER BY dbo.GCSE.StudentId
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 240 total points
ID: 13765310
Dubs,

>> ill be very surprised if i have to write separate queries to make aliases first and then write a second query <<
Get ready to be surprised, then.

>>as it doesn't explain why access would accept it<<
You need to come to terms with the fact that every implementation of SQL is different.  The SQL dialect supported in MS Access is not the same as T-SQL.  By all means purchase Oracle, but guess what the SQL dialect in PL-SQL is significantly different from T-SQL and the SQL dialect in MS Access.
0
 

Author Comment

by:Dubs
ID: 13770112
No worries, thanks all the same

greg
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13772484
If you consider this question resolved, than please close it.

Thanks.
0
 

Author Comment

by:Dubs
ID: 13772715
sorry i was busy crying tover the fact that nothing i need to do can be done all in one sql language...

here are your points..
greg
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13774778
>>here are your points..<<
My points? I suspect you awarded me the points in error:  It was rafrancisco who was helping you. Please see here:
I accepted the wrong answer. Now what?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi17
0
 

Author Comment

by:Dubs
ID: 13781449
thanks but none of the solutions worked so i accepted your answer of 'it won't work', have given up- taken a different route and have been able to proceed so your answer was useful to me...

apologies to rafransisco for trying- really i should have split the points as you did give it a go...

greg
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question