CODEWITHSUNDEEP
sqlexcelsyntaxinformixms-query
PriceHardman
PriceHardman

Reputation: 1733

Subquery Syntax Discrepancy in Microsoft Query

I posted a question last week about using subqueries in Microsoft Query, and although my problem in that instance was not solved, I've found the problem to be much more basic than in that previous case.

I'm connected to an Informix database via ODBC trying in Microsoft Query (from Excel 2010) to perform a simple query, and am having a lot of trouble trying to get the syntax for subqueries correct. I have a feeling that MSQuery is getting hung up on some idiosyncrasy related to the Informix/ODBC source. My reason for thinking this is because if I try a very simple subquery using an Excel spreadsheet as my datasource, the following syntax works:

SELECT * FROM
(
SELECT x0.field1, x0.field2
FROM `C:\USERS\NAME\DIRECTORY\Test.xlsx`.`Sheet1$` x0
)

However, when I try the same very simple query using a subquery from my Informix db,

SELECT * FROM
(
SELECT x0.id, x0.creation_date
FROM coastal.waybill x0
)

I get the error "Could not add the table '(select'." All I can surmise is that the syntax one uses in MSQuery has to be tailored to the data source. However, when I looked up Informix's syntax for subqueries in the FROM clause I got even more confused, because it appears that I followed their syntax to a tee, since their example is:

SELECT LIMIT 1 * FROM 
   (SELECT c.customer_num, c.lname, c.company, 
           c.phone, u.call_dtime, u.call_descr
                 FROM customer c, cust_calls u
                 WHERE c.customer_num = u.customer_num
    ORDER BY u.call_dtime DESC);

I'm thoroughly lost. Any assistance would be greatly appreciated!

Upvotes: 2

Views: 1983

Answers (1)

Dave Sexton
Dave Sexton

Reputation: 11188

Have you tried a table alias like so:

SELECT a.* FROM
(
SELECT x0.id, x0.creation_date
FROM coastal.waybill x0
) a

I used to MS Query a lot on for Oracle databases and sub-queries didn't need an alias, I then moved to MS SQL which does require them.

Upvotes: 1

Related Questions