CODEWITHSUNDEEP
oracle-database
user1471980
user1471980

Reputation: 10656

how do you issue select statment against the oracle table without double quotes around the column names

I need to connect to oracle table and issue a select stament. When I do this:

SELECT T."Node" as LPAR, to_char(CAST((FROM_TZ(CAST(GET_UTC_TIMESTAMP(T."Timestamp", TMZDIFF) AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE),'dd-Mon-yyyy hh24:mi') as DATETIME,T."MAX_Memory_Size_MB",T."MIN_Comp_Memory",T."AVG_Comp_Memory", T."MAX_Comp_Memory", T."MIN_Non_Comp_Memory", T."AVG_Non_Comp_Memory", "MAX_Non_Comp_Memory" FROM DW.KPX_PHYSICAL_MEMORY_DV T where t."Node" like 'server%'

it works, but the program I am using does not like double quotes around the column names e.g. T."Node".

I tried to rewrite this sql statment as below, but now I get invalid identifier.

Is there a way around this?

SELECT Node as LPAR, to_char(CAST((FROM_TZ(CAST(GET_UTC_TIMESTAMP(Timestamp, TMZDIFF) AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE),'dd-Mon-yyyy hh24:mi') as DATETIME,MAX_Memory_Size_MB, MIN_Comp_Memory,AVG_Comp_Memory, MAX_Comp_Memory, MIN_Non_Comp_Memory, AVG_Non_Comp_Memory, MAX_Non_Comp_Memory FROM DW.KPX_PHYSICAL_MEMORY_DV where Node like 'server%'

Upvotes: 0

Views: 103

Answers (1)

Justin Cave
Justin Cave

Reputation: 231881

If the first statement works, that indicates that you have created a case-sensitive column name. If you do that, you will have to use double-quotes around the identifier and specify the case correctly every single time you refer to the column. Creating a case-sensitive identifier is generally something that you should avoid at all costs.

If this is a new database object, the preferred solution would be to change the column name to be case-insensitive

ALTER TABLE DW.KPX_PHYSICAL_MEMORY_DV
  RENAME COLUMN "Node" TO Node;

Another option would be to create a view on top of the table that exposes case-insensitive identifiers and to query that view rather than the base table. Of course, you'd also need to do that for all the other case-insensitive identifiers you reference in the query.

Upvotes: 2

Related Questions