how to SELECT a column which has a space in between its name

Question

I am trying to execute a SQL query through OCCI calls in my CPP program. I want to read 2 columns out of those one column name has a space in between. I tried enclosing the column name between ' ', " ", [ ] and nothing helped. Can experts suggest me on answering this.

Below is my code: ....

string sqlStmt = "SELECT 'REJECTED COST', APPROVED_COST FROM COST_TABLE where PART_NUM= 'PN4879-1'";
stmt = conn->createStatement(sqlStmt);
ResultSet *rset = stmt->executeQuery();
double dRejCost = 0;
double dAppCost = 0;
if(rset->next())
        {
            dRejCost = rset->getNumber(1);
            dAppCost = rset->getNumber(2);

        }
stmt->closeResultSet(rset);
conn->terminateStatement(stmt);

Error/Exception:

The error I get @ dRejCost = rset->getNumber(1);:
ORA-01722: invalid number

PS: The ORACLE table has the many columns where "REJECTED COST" column header is named with a space. I don't have the privilege to ask the DB team to change the DB table name though.

Thanks in advance.

The SQL standard requires such an identifier to be enclosed in double quotes: `"REJECTED COST"` and so does Oracle. Your code needs to use: `string sqlStmt = "SELECT \"REJECTED COST\", .... ";` (or whatever escapes C is using inside strings). Single quotes are only for string literals in SQL statements. But "*ORA-01722: invalid number*" stems from the fact that your current statement returns a string `'..'` for that column. — , Oct 20 '14 at 05:37

score 2 · Accepted Answer · answered Oct 20 '14 at 05:45

Single quotes (') denote character literals. I.e., you're selecting the string 'REJECTED COST', which, obviously, cannot be cast to a number. In order to select a column name with a space, you should use double quotes ("). Note that they need to be escaped, as you're using them inside a c++ string, which is also denoted by double quotes:

string sqlStmt = "SELECT \"REJECTED COST\", APPROVED_COST FROM COST_TABLE where PART_NUM= 'PN4879-1'";

how to SELECT a column which has a space in between its name

1 Answers1