Inconsistent column names, issues with Oracle due to upper-/lowercase names

Hi community,
I have some questions regarding upper- vs lower-case column names in ONE DATA.

1.Because in query-processors it does not matter if I spell my column-names upper- or lowercase, I would guess ONE DATA is case-insensitive for column names?
Still there seems to be some schwurbel when displaying column names at various places:
I see in a result-table the exact name (upper-, lowercase mixed), while in a saved datatable I still see the initial column-name when I replace a table with the same column-name but different mix of upper/lower-case.

Example: Create a datatable from a custom-input-table with column “MEMBER” and save it as DT. Then from the same Custom-Input-Table in a query you can select “MeMbEr” and in the result-table this is displayed that way, while in the datatable-overview (with match schema setting while saving) still “MEMBER” is shown.

  1. This could lead to issues when saving to Oracle: It is possible to save from ONE DATA to an Oracle-table with lower-case column names without errors, I guess ONE DATA automatically converts to uppercase on the DB.
    When loading that Oracle-table in ONE DATA in a DB-Connection-Load I get an error when referencing to the column by name (neither upper or lower-case is working). The same happens when creating a VDT on top of that (of course).

Is this expected, and the user has to make sure to always use uppercase when interacting with Oracle? Would it be possible to have consistent behaviour when displaying column-names?

Thanks for your help
Christoph

Hi,

Then from the same Custom-Input-Table in a query you can select “MeMbEr” and in the result-table this is displayed that way

Spark is case insensitive regarding column names but is case preserving.

while in the datatable-overview (with match schema setting while saving) still “MEMBER” is shown.

Yes because the Data Table save does not update the existing schema but preserves what was there. It follows spark here and considers MEMBER and MeMbEr as equal.

This could lead to issues when saving to Oracle: It is possible to save from ONE DATA to an Oracle-table with lower-case column names without errors, I guess ONE DATA automatically converts to uppercase on the DB.

I think not, I’m quite sure it keeps it as lowercase, except they were lower case in an existing oracle table.

When loading that Oracle-table in ONE DATA in a DB-Connection-Load I get an error when referencing to the column by name (neither upper or lower-case is working). The same happens when creating a VDT on top of that (of course).

Did you quote them? Oracle considers unquoted column names uppercase in queries, see Schema Object Names and Qualifiers :

Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive.

By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace:

employees
“employees”
“Employees”
“EMPLOYEES”

Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace:

employees
EMPLOYEES
“EMPLOYEES”

1 Like