Skip to Main Content
Forums

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to handle column names with space in between

810928Nov 16 2010 — edited Dec 30 2010
I have a requirement where in I am writing the output of one query into a table. The query is returning a resultset which has space in the column name for ex -
select DT_LOC "*Date A*" , COL1, COL2 "*Column Name*" from TABLE
I am trying to write the output of above query into table B which has similar structure using a stored procedure. My source has the above query, where as target has the insert statement as below -
insert into TABLEB values ( #"Date A" , #COL1, #"Column Name")
or
insert into TABLEB values ( "#Date A" , #COL1, "#Column Name")

ODI is not able to handle the column name with space in between i.e. Date A & Column Name. Note - I can not map these tables as models as they can change dynamically.

Comments

Cezar Santos
Hi,

what are the source and target technologies?


Cezar Santos
http://odiexperts.com
722252
I am having th same problem loading data from Essbase to a text file. The Essbase Column Name has spaces in between (i.e. - "Job Code"). I am using ODI 10.1.3.5 and the source is Essbase, the Staging is MSSQL Server 2005, and the target is a text file. This is giving me an error when the interface tries to create a temporary table in MSSQL Server. Any thoughts on how to solve this?
656777
Hi,

I guess you're using an interface to load the data. If so then you should be aware that the tablename is included in your mapping.
so if in your source table you have the column: Date A and you drag this onto your target table column you'll get something like:
{tablename}.Data A
In SQL server (e.g.) valid ways to write database/table/column names with spaces are: double quotes " " and brackets [ ].
However ODI does not like the brackets, so double quotes are the only option here, so your mapping should become:
{tablename}."Data A" when you use SQL Server.

I see you're using hash signs #. In your example, please be aware that ODI will always first try to interpret the #Data, #COL1 and #Column as a variable and, should such a variable name exist, replace it with the variable value.
656777
Hi,

also for you it's important to use double quotes around the column names with spaces.
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 27 2011
Added on Nov 16 2010
#business-intelligence-applications, #data-integrator, #system-management-and-integration
4 comments
14,488 views