1

I am trying to get "unique types of crimes have been recorded at GAS STATION locations" from the table CHICAGO_CRIME_DATA on Juptyter Notebook.

%sql select DISTINCT PRIMARY_TYPE from CHICAGO_CRIME_DATA WHERE LOCATION_DESCRIPTION = 'GAS STATION';

PRIMARY_TYPE and LOCATION_DESCRIPTION are the column names. I am operating in IBM DB2. It is producing an error.

ibm_db_sa://ddk87262:***@dashdb-txn-sbox-yp-lon02-01.services.eu-gb.bluemix.net:50000/BLUDB

(ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0134N Improper use of a string column, host variable, constant, or function "PRIMARY_TYPE". SQLSTATE=42907 SQLCODE=-134

[SQL: select DISTINCT PRIMARY_TYPE from CHICAGO_CRIME_DATA WHERE LOCATION_DESCRIPTION = 'GAS STATION';]

(Background on this error at: http://sqlalche.me/e/f405)

I have double-checked the column names . The code displays results (with redundant rows) if I dont use the Distinct function.

What would cause this error?

Community
  • 1
  • 1

1 Answers1

3

Check for the Db2 column datatype of PRIMARY_TYPE column.

You can look in syscat.columns for your table to see the column data types for PRIMARY_TYPE, if you have access. You can also use describe table CHICAGO_CRIME_DATA . The create table statement may also be documented for your table showing the Db2 column data type.

You can see the details of that error message SQL0134N here.

It is likely that the column datatype is one of CLOB, DBCLOB, BLOB, LONG VARCHAR, or LONG VARGRAPHIC , and these are not allowed with DISTINCT.

You can workaround this by extracting some characters (e.g. substr etc.) from the column and casting to CHAR or VARCHAR, and then use distinct on this type.

mao
  • 11,321
  • 2
  • 13
  • 29