SQL Column Name Change

Hello,

to_char(AllASPPgmsFinWPmt_tbl.DOS , 'MM/DD/YYYY'),
The field name returned to Excel above is too long.  I would like to it return to Excel just as DOS

I tried:
to_char(AllASPPgmsFinWPmt_tbl.DOS , 'MM/DD/YYYY') AS DOS,

This did not work though.  Any thoughts.. Thanks as always and please let me know if any information is needed.
jvera524Asked:
Who is Participating?
 
paulwquinnConnect With a Mentor Commented:
Unfortunately, it seems that the prevailing wisdom re column aliases in MS Query is to use views (the workaround I described earlier) and/or procedures on the database side to rename the column before MS Query ever references it. There are many solutions on the Web (Google is your friend! :^) ), including:

(1) Using no delimiters around the column alias in the query
(2) Using single quotes around the column alias in the query
(3) Using square brackets ( [ and ] ) around the column alias in the query
(4) Using a completely different syntax:
     SELECT... DOS = to_char(AllASPPgmsFinWPmt_tbl.DOS , 'MM/DD/YYYY'),1,10) ... FROM etc.

There are apocryphal accounts of all of the above actually working, but many more saying "none of the above"..

Seems your best bet is to use database-side views or code if possible, or something besides MS Query (e.g. VBA, see example at http://snippets.dzone.com/posts/show/4518, SQL*XL, etc.) if it's not.
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
From where you are running this.
If its sqlplus, then use the following

col DOS format a10
0
 
jvera524Author Commented:
I am connecting to an Orace database and transferring data to Excel.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
How you are transferring.
Are you saving the output on flat file and then import that file or
spool the output in HTML format and rename the file with .xls extension and open in excel file.
0
 
jvera524Author Commented:
I am using an Excel macro and I connect to the Oracle database and use a SQL select statement.  I do not think I do any of what you said above.
0
 
slightwv (䄆 Netminder) Commented:
Unfortunately I'm not on a machine where I can link Excel to Oracle to confirm this but just for grins try:

to_char(AllASPPgmsFinWPmt_tbl.DOS , 'MM/DD/YYYY') AS "DOS"

If this doesn't work please provide a little more about the problem other than "it didn't work".  Did it generate an error, did it ignore the alias, what?
0
 
awking00Commented:
What is the select statement you are using when importing external data into excel?
0
 
jvera524Author Commented:
Hello slightwv,

I tried what you said there was no error, but it seems the addition of AS "DOS" is just ignored and when I bring up the SQL statement the AS "DOS" is no longer there.

0
 
slightwv (䄆 Netminder) Commented:
>>is just ignored and when I bring up the SQL statement

What do you mean 'bring up' the sql statement?
0
 
paulwquinnCommented:
I assume you are trying to use MS Query from within Excel. It has problems with column aliases.

See http://support.microsoft.com/default.aspx?scid=kb;en-us;298955

Check the "More Information" Section of the above. There is an AllowAlias registry key that you probably need to set to turn column aliases on. Make sure (1) the AllowAlias key is defined and (2) it is NOT set to 0 (1 to enable, possibly 2 to enable the hotfix, if required).
0
 
jvera524Author Commented:
Hello paulwquinn,

Thanks for the article.  I made the changes. Do I need to restart the computer or something else?
I tried to alias the column in Microsoft Query, but the same that happened earlier happened again.

Is this the correct way to alias a column?
to_char(AllASPPgmsFinWPmt_tbl.DOS , 'MM/DD/YYYY') AS DOS,

Thanks for the help.

0
 
paulwquinnCommented:
After making any registry changes you generally need to restart the computer to pick them up. The actual syntax of your aliasing looks OK, although the "AS" keyword is usually optional (I never use it). Simply:

to_char(AllASPPgmsFinWPmt_tbl.DOS , 'MM/DD/YYYY') DOS,
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
I am using excel 2007, there is an option to shrink column to autofit its width.
Click on Data---> In connection tab Click Properties --> Click Adjust column Width.
0
 
jvera524Author Commented:
paulwquinn is there anything I need to do Excel and/or Microsoft Query to register the change. I shut down my computer and restarted and it is not working.  The same thing happens. What I type for the alias just disappears from the SQl statement. Does it matter that I am working with a Oracle database?

Thanks.
0
 
paulwquinnCommented:
Hmm...the database shouldn't matter... Oracle certainly supports column aliases. MS Query, on the other hand has had various issues with column aliases over the years. What version of Excel are you using?

Try double-clicking on the column in MS Query to bring up the "Edit Column..." dialog and changing the column name there.

0
 
jvera524Author Commented:
I double clicked the column to bring up the "Edit Column..." dialog. It did not come up. When I use the menu bar and click on "Records" I see "Edit Column..." , but it is not enabled.
0
 
paulwquinnCommented:
Do you have access to the Oracle database? One workaround would be to use your query to construct a view on the database side, then execute a simplified query agains the view.

On the Oracle server:

CREATE VIEW myview AS  Your-SELECT-statement-here-including-column-alias;

In Excel/MS Query:

SELECT * FROM myview;
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
Just modify the query like in excel and see if you get correct result.
select * from ( select substr( to_char(AllASPPgmsFinWPmt_tbl.DOS , 'MM/DD/YYYY'),1,10) as DOS from table)

But I believe, you can find the connection related info in Excel where you can resize the cells accordingly.
0
 
paulwquinnCommented:
Hi virdi_ds,

Thanks for your contributions, but the question relates to the actual text heading used for the Excel column, NOT the column width. It doesn't matter how wide the column is: the desired heading is "DOS", NOT
to_char(AllASPPgmsFinWPmt_tbl.DOS , 'MM/DD/YYYY'),1,10).
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
I am sorry, I misunderstood the question then.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.