Knowledgebase: Tutorials
[QODBC-Desktop] Troubleshooting - Cannot use alias in MS Query
Posted by Jack - QODBC Support on 02 March 2015 11:54 AM

Troubleshooting - Cannot use alias in MS Query

Problem Description:

I am getting the below error message when trying to SELECT data fields AS Alias, the statement runs fine otherwise.

[sql syntax error] Expected lexical element not found:=

Please see the following SQL statement:

SELECT Item.Name AS SKU, Item.CustomFieldColor AS Item, Item.Description, Item.SalesPrice, Item.PurchaseCost, Item.QuantityOnHand FROM Item Item WHERE (Item.Name<>'IFR' And Item.Name<>'OTW') AND (Item.Description<>'') AND (Item.Type='ItemInventory') ORDER BY Item.Name

The above statement is working fine in QODBC Test Tool, MS Access. But I am facing the issue in MS Excel.  

 

http://support.flexquarters.com/esupport/newimages/MSQueryAlias/step1.PNG

 

Solutions:

MS Excel has some issue when you alias in the query. When you try to use Microsoft Query to return data from some third-party databases into Microsoft Excel, apostrophes (') around alias names can cause the query to fail.

Please refer below mentioned link to resolve this issue:

Using a field alias in Query does not work with some third-party databases

You can either apply hotfix or change registry values.

The result after changing registry values & execute the query again:

http://support.flexquarters.com/esupport/newimages/MSQueryAlias/step2.PNG

(45 vote(s))
Helpful
Not helpful

Comments (2)
John Harrington
18 March 2015 09:23 PM
Link does not work: http://support.microsoft.com/kb/298955
Jack
19 March 2015 07:19 AM
Hi John,

Link has been updated.
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).