SQL Column Name Change

From where you are running this.
If its sqlplus, then use the following

col DOS format a10

I am connecting to an Orace database and transferring data to Excel.

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.

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.

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?

What is the select statement you are using when importing external data into excel?

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.

>>is just ignored and when I bring up the SQL statement

What do you mean 'bring up' the sql statement?

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).

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.

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,

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.

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.

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.

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.

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;

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.

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).

I am sorry, I misunderstood the question then.