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

Question

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?

Answer 1

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

Answer 2

as : Doesn't take space "Test Score" will give an error "Test_Score" will be fine

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

Answer 3

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

This solved it for me

Answer 4

I was having the same issue when executing SQL queries from a jupyter notebook to a cloud database using the IBM-db2 service.

To provide some context on the environment and components version used in the initial question posted by @data_henrik: previous to the query, the following packages need to be installed,

!pip install --force-reinstall ibm_db==3.1.0 ibm_db_sa==0.3.3
!pip uninstall sqlalchemy==1.4 -y && pip install sqlalchemy==1.3.24
!pip install ipython-sql

Make sure the that sqlalchemy version is <1.4, otherwise no query will run properly (it is said to be incompatible). If previous installation does not work properly, try:

!pip install --upgrade sqlalchemy<1.4
!pip install --force-reinstall ibm_db ibm_db_sa

Then load the package and stablish a connection to the IBM cloud database using your credentials (replace in the connection string below):

%load_ext sql
%sql ibm_db_sa://YourUsername:YourPassword@YourHostname:YourPort/YourDatabaseName?security=SSL

The database contains a table INTERNATIONAL_STUDENT_TEST_SCORES with columns 'country', 'first_name', 'last_name' and 'test_score' containing 99 records.

As mentioned by @data_henrik the following query fails:

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

giving an 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)

The error message indicates that there is a problem with the keyword "as". In tipical SQL statements providing aliases with white spaces is problematic (See w3schools/sql_alias).

If white space are needed on the aliases one needs to enclose the new alias name in double quotation marks or sometimes in square brackets, but none of these works here since the alias is already in between the double quotation marks.

Nevertheless, the simple solution, as indicated by @Rakesh, is to change the alias name to "Test_Score" to avoid the white space. So this works fine:

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