Column alias

[gencom99]

I would like to put column aliases on some of the fields in the SELECT statement.

Private Sub CommandButton1_Click()
Dim NumList As String
Dim SourceBook As Workbook
Dim ListSheet As Worksheet
Dim QuerySheet As Worksheet
Dim Query As QueryTable
Dim Numb As String

Set SourceBook = Application.ActiveWorkbook
Set ListSheet = SourceBook.Worksheets("Main")
Set QuerySheet = SourceBook.Worksheets("MONEY")
Numb = Range("H6")


If ListSheet.Cells(6, 8).Value = "" Then Exit Sub
NumList = "'" & ListSheet.Cells(6, 8).Value & "'"

Set Query = QuerySheet.QueryTables("MNY")
Query.Sql = "SELECT NUMB_TBLE.NUMB_NAME, extract(month from NUMB_DATES_TBLE.DAY_MTH_YR) " _
& "FROM JES.NUMB TBLE NUMB TBLE,
JES.NUMB_DATES_TBLE NUMB_DATES_TBLE" _
& "WHERE NUMB_TBLE.NUMB_ID = NUMB_DATES_TBLE.NUMB_ID AND NUMB_TBLE.NUMB_CODE IN (" & NumList & ") " _
& "Group by numb_tble.numb_name,
extract(month from NUMB_DATES_TBLE.DAY_MTH_YR)




Query.Refresh (False)


End Sub

I want to alias the column NUMB_TBLE.NUMB_NAME, I want it to say LAST_NAME

I have tried NUMB_TBLE.NUMB_NAME AS LAST_NAME. It works but it changes the order of the columns. I do not want the order of the columns to change.

When the query runs, in Excel, the column headings are DAY_MTH_YR and LAST_NAME

If I do not use the alias, the column headings in Excel are NUMB_NAME, DAY_MTH_YR.

I want the order to still be Name of the person, Date of the transaction.

When I use the alias, it becomes Date of the transaction, Name of the person

The order changes.

How do I alias the columns without effecting the order of the columns.

I connect to the Oracle database and the result set is put into excel

 

[genomon]

Add an ORDER BY clause to your SELECT query.

Cogito eggo sum – I think, therefore I am a waffle.

 

[gencom99]

If I add an ORDER BY to my SELECT statement, the data in the columns get sorted.

I do not want to sort the data.

I want to alias the some of the columns to make the names more user-friendly.

When I alias a column, it changes the placement of the columns in the result.

For example if my Select statement is

Select BANK_NUMBER, MONTH_YEAR
FROM MYTABLE

IF I alias the column

Select BANK_NUMBER AS ACCOUNT, MONTH_YEAR
FROM MYTABLE

THE result set becomes MONTH_YEAR ACCOUNT

I want to alias the column names without changing the order

 

[HarleyQuinn]

I've got to say, I've worked with Oracle for years and have never seen the behaviour you describe I'm afraid.

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[ajrimmer]

Strange one. Don't know why that's happening.

However, have you tried nesting select statements to get them in the order you want?

eg

SELECT Account, Month_Year 
FROM (SELECT BANK_NUMBER AS ACCOUNT, MONTH_YEAR
FROM MYTABLE.... ) tblAlias

 

[xlbo]

Right click on your query table and examine the properties

Do you have "Preserve Column/Sort/Filter Order" checked?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[gencom99]

I am connecting to Oracle through Microsoft Query

 

[gencom99]

Yes, I have Preserve Column/Sort/Filter Order checked

 

[SkipVought]

I use MS Query alot to access Oracle tables.

There is no SQL property. There is a CommandText property.

FROM JES.NUMB TBLE

spaces in table and field names are a pain and should be coded...

FROM JES.[NUMB TBLE]

I want to alias the column NUMB_TBLE.NUMB_NAME, I want it to say LAST_NAME

SELECT NUMB_TBLE.NUMB_NAME AS LAST_NAME

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Try unticking Preserve Column/Sort/Filter Order

That may solve your issue (it won't harm in any case - all it does it keep the original field order even if you change it in the query)

It may be that it is getting its knickers in a twist as you are not using the same field names as when you originally defined the query

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question