IBM Db2 : An unexpected token “as” was found following “SELECT test_score ” (SQL0104N)

0
votes

I am now having trouble with IBM Db2 using queries. I have a code below:

test_score_distribution = %sql SELECT test_score as "Test Score", count(*) as "Frequency" from INTERNATIONAL_STUDENT_TEST_SCORES GROUP BY test_score;

test_score_distribution

But when executing, I encountered this error:

(ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "as" was found following "SELECT test_score ". Expected tokens may include: "AND". SQLSTATE=42601\r SQLCODE=-104 [SQL: SELECT test_score as Test Score, count(*) as Frequency from INTERNATIONAL_STUDENT_TEST_SCORES GROUP BY test_score;] (Background on this error at: http://sqlalche.me/e/f405)

How can i fix it?

1
sqlsqlalchemydb2python-db-api
How does the table look like? There are many errors in the statement. Have you clicked the SQLAlchemy link with the background information? - data_henrik
Please edit your question to give details of your environment and all component versions used. Do not use comments for this. I can run your query in a jupyter notebook 6.2.0 with ibm_db 3.0.4 , ibm_db_sa 0.3.6, sqlalchemy 1.3.23, with python 3.8.5. So whatever is causing your symptom is specific to your environment. - mao
I have seen this kind of problems with zero length characters - AngocA

1 Answers

0
votes

i had the same problem, try the following steps:

test_score_distribution = %sql SELECT test_score, count(*) "Frequency" from INTERNATIONAL_STUDENT_TEST_SCORES GROUP BY test_score;
    
test_score_distribution

And then to change the name you can use this command:

dataframe = test_score_distribution.DataFrame()
column_names = dataframe.columns.values
column_names[0] = "Test Score"
dataframe.columns = column_names
column_names[1] = "Frequency"
dataframe.columns = column_names
dataframe

In my experience working with SQL magic commands makes you need to delete the spaces on each query.

You can always save the Table with the .DataFrame() Function and work more freely