1

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?

data_henrik
  • 16,724
  • 2
  • 28
  • 49
Le Hoang Phuc
  • 11
  • 1
  • 2
  • 1
    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 May 10 '21 at 05:25
  • 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 May 10 '21 at 09:41
  • I have seen this kind of problems with zero length characters – AngocA May 12 '21 at 02:11

4 Answers4

1
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

Timothy G.
  • 6,335
  • 7
  • 30
  • 46
Godfatherfigure
  • 11
  • 2
0

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

Ricardo Jose Hincapie Llerena
  • 1
0

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

Rakesh Kumar Mahato
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 12 '21 at 07:17
0

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
Ram HR
  • 1
  • 3