2

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!

PriceHardman
  • 1,693
  • 1
  • 12
  • 14
  • 1
    Assuming you have a recent enough version of Informix, then the syntax you used should work fine with Informix. To have an insufficiently recent version, you'd have to be on an unsupported version of the product, and probably quite an old one. So, which version of Informix are you using? If it is 7.31 (or, perish the thought, 7.30 or older), you are out of luck. If you're on 11.10 or later, you should be fine. That leaves versions 9.30, 9.40 and 10.00 (all out of support) as versions that might, or might not, have the 'sub-query in the FROM clause' support. – Jonathan Leffler Sep 17 '12 at 19:44
  • Thanks so much for the info, Jonathan. I'm quite unexperienced in matters relating to DBMS's, especially Informix. My company uses a (severely) outdated Informix system that we're in the process of upgrading. We're running version 9.40 of Informix Dynamic Server, but version 7.32 of IBM Informix-SQL. I looked at [page 6 of the guide](http://www.informix.com.ua/doc/9.40/ct1sqna.pdf) for 9.40, and it says FROM clause subqueries are supported. Is it Dynamic Server or Informix-SQL that would determine support for SQL language features? – PriceHardman Sep 18 '12 at 15:56
  • It is Informix Dynamic Server that would support the notation specifically. ISQL is a set of front-end tools using the curses library that mediate access to the server. I'd forgotten that it was as long ago as 9.40 that the 'sub-query in the FROM clause' was added; I thought it might be 10.00, but I hadn't looked at the manuals (being lazy). So, in theory, you should be 'OK'. (Informix SQL is separately versioned; 7.50 is current, and 7.32 is the prior version.) – Jonathan Leffler Sep 18 '12 at 16:20
  • If the problem persists (and I've no reason to think that it won't), then I'd want to start tracking exactly what SQL is being sent to Informix. The message does not look like an Informix message; I'm inclined to think that something on the client side (meaning MS Query, or possibly the driver being used to access Informix from MS Query) is doing the complaining when it shouldn't. However, doing that tracking is going to be non-trivial, and I'm no expert on Windows. Were it Unix, then I've a battery of tools that I can use. Are you using an ODBC driver or something else to connect to Informix? – Jonathan Leffler Sep 18 '12 at 16:21
  • I'm connecting to Informix from MSQuery using the IBM Informix ODBC Driver v3.50. Since its looking more and more like this is not a quick-fix issue with my syntax like I had originally thought, I'll see what my IT person thinks about all this. – PriceHardman Sep 18 '12 at 16:46
  • Sounds like a plan. Sorry I can't give the definitive answer immediately...just out of my experience range. – Jonathan Leffler Sep 18 '12 at 16:53
  • Not to worry, Jonathan. You've been a huge help. This was a much bigger issue than I initially thought; I'm just glad that I have an answer as to why this wasn't working. Thanks again. – PriceHardman Sep 18 '12 at 17:08
  • Jonathan, just thought I'd give you a heads up. My company just updated their version of Informix, and the problem is solved. Subqueries on the select clause now work just fine. Thanks again for your help. – PriceHardman Nov 12 '12 at 16:19
  • Thanks for the information, and well done for remembering that this was here. – Jonathan Leffler Nov 12 '12 at 16:23

1 Answers1

1

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.

Dave Sexton
  • 10,768
  • 3
  • 42
  • 56