Clever Geek Handbook

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 two separate tables that I want to join using this column _id

. In Run SQL, I tried the following but it didn't work. What am I missing here? Do I need to replace the column name _id

with something without underscore?

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


source to share


1 answer


TL; DR : As @gmiley points out that the problem is caused by a column name _ID

that is not a normal identifier (see definition below) and therefore must 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 regular identifiers, quoted identifiers are case sensitive ( "_ID"

not identical "_ID"

, whereas title

identical title

). If you had specified "_ID"

in your statement, an error would have been raised indicating that the column was not found.

Since you mentioned that you used the Cloudant storage process to populate DashDB tables, it is probably worth mentioning that property names are uppercase when the DDL is generated during schema discovery.

Example: Content of JSON documents with this structure

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


will be displayed in three columns:

  • _ID

    type VARCHAR(...)

  • _REV

    type VARCHAR(...)

  • DATE_RECEIVED

    like ...
  • ...

Hope this helps!

From the DB2 SQL link :

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

Examples: WKLYSAL

WKLY_SAL

The designated identifier is a sequence of one or more characters enclosed in double quotes. Leading gaps in the sequence are significant. The catching spaces in the sequence are negligible, although they are stored with an identifier. Two consecutive quotation marks are used to represent one quotation mark in a separating identifier. Thus, the identifier can contain lowercase letters.

Examples: "WKLY_SAL"

"WKLY SAL"

"UNION"

"WKLY_SAL"

+4


source






More articles:


All Articles