Margriet Margriet -4 years ago 102
SQL Question

How can I use a column named _id from a dashDB table?

I have a database in Cloudant where the document ID is

_id
.

After replicating this data from Cloudant to dashDB I have 2 separate tables that I want to join using this
_id
column. In Run SQL I tried the below, but this would not run. What am I missing here? Do I need to replace the column name
_id
to something without an underscore?

select m.title, m.year, g.value
from MOVIES m
inner join MOVIES_GENRE g on m._ID = g._ID;

Answer Source

TL;DR: As @gmiley points out the issue is caused by the _ID column name, which is not an ordinary identifier (see definition below) and therefore needs to be enclosed in double quotes "_ID" or single quotes '_ID' in your SQL statements.

select m.title, m.year, g.value 
from MOVIES m
inner join MOVIES_GENRE g on m."_ID" = g."_ID";

Unlike ordinary identifiers quoted identifiers are case sensitive ("_id" is not identical to "_ID" whereas title is identical to TITLE). If you were to specify "_id" in your statement an error would be raised indicating that the column wasn't found.

Since you've mentioned that you've used the Cloudant warehousing process to populate your DashDB tables it's probably worth mentioning that property names are upper-cased when the DDL is generated during the schema discovery.

Example: The content of JSON documents with this structure

 {
  "_id": "000018723bdb4f2b06f830f676cfafd6",
  "_rev": "1-91f98642f125315b929be5b5436530e7",
  "date_received": "2016-12-04T17:46:47.090Z",
  ...
 } 

will be mapped to three columns:

  • _ID of type VARCHAR(...)
  • _REV of type VARCHAR(...)
  • DATE_RECEIVED of type ...
  • ...

Hope this helps!


From the DB2 SQL reference:

An ordinary identifier is an uppercase letter followed by zero or more characters, each of which is an uppercase letter, a digit, or the underscore character. Note that lowercase letters can be used when specifying an ordinary identifier, but they are converted to uppercase when processed. An ordinary identifier should not be a reserved word.

Examples: WKLYSAL WKLY_SAL

A delimited identifier is a sequence of one or more characters enclosed by double quotation marks. Leading blanks in the sequence are significant. Trailing blanks in the sequence are not significant, although they are stored with the identifier. Two consecutive quotation marks are used to represent one quotation mark within the delimited identifier. In this way an identifier can include lowercase letters.

Examples: "WKLY_SAL" "WKLY SAL" "UNION" "wkly_sal"

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download