[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Column Name Change

Posted on 2011-02-17
20
Medium Priority
?
1,096 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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 77

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 32

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 77

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
 
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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
What we learned in Webroot's webinar on multi-vector protection.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

650 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