Column Alias in MS Query gives Oracle error

[MRyerson]

Hi everyone,

I am trying to use MS Query from Excel to create a query against an Oracle (9.2) database. Most everything works just fine, but when I try to create a column alias by double-clicking on a column header in the bottom pane and entering a column heading, the SQL created by MS Query is invalid for our implementation.

SELECT CUSTOMER.CUST_NBR AS 'Customer',
FROM OWNER.CUSTOMER CUSTOMER

I get the error: "OR-00923: FROM keyword not found where expected."

I tried this on our Oracle interface and got the same error. In that interface, I removed the AS keyword and substituted double-quotes for the single quotes, and it ran fine.

However, making the same changes to the SQL statement in MS Query does NOT fix the problem.

Anyone have experience with anything like this?

Thanks in Advance,


Marty

 

[SkipVought]

Hi,

Remove comma

SELECT CUSTOMER.CUST_NBR AS 'Customer'
FROM OWNER.CUSTOMER CUSTOMER

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[MRyerson]

Sorry, I edited the statement incorrectly. The original had more columns projected, and did not have a comma following the final column name. The statement as given in Skip's reply gives the same error. I am sure this has something to do with the particular

I did find a sort of work-around, but it is not entirely satisfactory.

If you enter the name with double-quotes in the Edit Column dialog, it works. However, this results in the double quotes being displayed in the header of the returned data.

It generates SQL that looks like this:

SELECT CUSTOMER.CUST_NBR AS """Customer""", CUSTOMER.AAMA_LABEL AS """Label""", CUSTOMER.ACCT_BAL AS """Balance"""
FROM OWNER.CUSTOMER CUSTOMER

Marty

 

[SkipVought]

Loose the tic/quote...

SELECT CUSTOMER.CUST_NBR AS Customer, CUSTOMER.AAMA_LABEL AS Label, CUSTOMER.ACCT_BAL AS Balance
FROM OWNER.CUSTOMER CUSTOMER

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[MRyerson]

Sorry Skip,

I have tried omitting the AS, I have tried using double-quotes, I have tried using no quotes, and every combination of the above.

The variations you suggest do work in TOAD, just not in MS Query.

Thanks for trying,

Marty