This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes SQL error:Invalid identifier Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL error:Invalid identifier" Watch "SQL error:Invalid identifier" New topic
Author

SQL error:Invalid identifier

Victor Ramen
Ranch Hand

Joined: Jul 14, 2008
Posts: 56
Hello,
I am sql rookie and I would appreciate very much some assistance on this rather basic issue.

select comp_table.* from (select column_1,avg(column_2) as "avg" from table_1, group by column_1) comp_table---> returns correct records with 2 columns named : column_1, avg;

But if I change to :

select comp_table.avg from (select column_1,avg(column_2) as "avg" from table_1, group by column_1) comp_table---> returns Error: Invalid identifier "avg"

The thing is I only need to select the avg column; So I cannot do select comp_table.*. Can you guys please help?
Also, if you could kindly provide some tuning tips for the query...that would be awesome.

Thank you.


Byron Mabbett
Greenhorn

Joined: Sep 16, 2010
Posts: 2
Hi Roy,
You just need to not define your column as a literal. (No need for the quotes around the column name)
Use


If you just need the avg column with out the identifier you do not need the inner query

Also it can be best to try to stay away from what may be reserved words in any language like avg for column heading. Who knows if someone is going to import the results into a spreadsheet or such like.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 37941
    
  22
Welcome to the Ranch , Byron Mabbett
Victor Ramen
Ranch Hand

Joined: Jul 14, 2008
Posts: 56
Thank you. It solved the problem.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: SQL error:Invalid identifier
 
Similar Threads
acumulative sum with ireport
SQL Question -- why can't I use an aggregate function here?
SQL to HQL
Using Jenny with Postgres...is there a mechanism to set the schema being used?
Multiple repeat insert