Solved

SQL Column Name Change

Posted on 2011-02-17
20
1,043 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:jvera524
  • 6
  • 6
  • 5
  • +2
20 Comments
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 34920961
From where you are running this.
If its sqlplus, then use the following

col DOS format a10
0
 

Author Comment

by:jvera524
ID: 34921102
I am connecting to an Orace database and transferring data to Excel.
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 34921212
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
 

Author Comment

by:jvera524
ID: 34921518
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34925637
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
 
LVL 31

Expert Comment

by:awking00
ID: 34925639
What is the select statement you are using when importing external data into excel?
0
 

Author Comment

by:jvera524
ID: 34928132
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34928146
>>is just ignored and when I bring up the SQL statement

What do you mean 'bring up' the sql statement?
0
 
LVL 3

Expert Comment

by:paulwquinn
ID: 34928273
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
 

Author Comment

by:jvera524
ID: 34928810
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 3

Expert Comment

by:paulwquinn
ID: 34929153
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
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 34929174
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
 

Author Comment

by:jvera524
ID: 34929310
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
 
LVL 3

Expert Comment

by:paulwquinn
ID: 34929469
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
 

Author Comment

by:jvera524
ID: 34929744
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
 
LVL 3

Expert Comment

by:paulwquinn
ID: 34929803
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
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 34929821
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
 
LVL 3

Expert Comment

by:paulwquinn
ID: 34930010
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
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 34930128
I am sorry, I misunderstood the question then.
0
 
LVL 3

Accepted Solution

by:
paulwquinn earned 500 total points
ID: 34930306
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now