I can´t get Microsoft Query to work right in my excel. I am running it against a Oracle database,

trying to run the query:

SELECT tu.unitmaster_id, tu.name, tu.creation_date
        FROM tunit tu, (SELECT max(creation_date) as maxdate, name
                        FROM tunit
                        GROUP BY name) maxresults
            WHERE tu.name = maxresults.name
            AND tu.creation_date= maxresults.maxdate
            AND tu.language='pt'
                ORDER BY NAME

It works fine on SQLNavigator but when I run the same query on microsoft query, it won´t work, saying

it can´t add a select table or something. Can anyone help me with that, I am definitely in need...

Thanks.

I suspect that Microsoft Query can't handle the subquery. I don't think it's very smart...

What's the exact error message you are getting?

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

First it says the query can´t be displayed graphically. Then I click on continue and then it says it could not add table '(select'

I am very, very lost. I have problably wasted a good afternoon on debugging this thing and I still have no clue what it is.

Is it true microsot query doesn't accept the traditional sql language, it has its little language of its own?

I appreciate your help.

I confess I don't know very much about Microsoft Query; I've only used it a very little. Perhaps someone else with more experience can add to this.

The error complaining about not being able to display the query graphically can occur for lots of reasons - the graphical interface is limited in how it can represent more complex queries. The error doesn't necessarily mean there is anything wrong with the query, it simply means that a graphical display of it cannot be done. This is a common issue with all the (MS at least) graphical query tools - they can handle a more or less arbitrary number of tables, expressions, and grouping, etc., but they fall apart trying to represent UNION queries and, apparently (at least for this tool), subqueries as well.

There is nothing wrong with your query - it's quite acceptable SQL.

I'm not aware of any implementation by any vendor which I could (or would) characterize as "...the traditional sql language". They all differ from each other to varying degrees. I don't think it's quite fair to characterize Microsoft Query as having a "...little language of its own". It is, after all, still SQL, but, as you've discovered, a somewhat limited subset of it. :(

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I recommend you, when you use complex SQL like this, you better use a view from Oracle, that refers to your SQL, then use that view in SQL and the problem is solved.
The problem with Microsoft Query is that uses a SQL more simply than Oracle, and for that, you must use the views when using complex SQL, with subquerys, unions, outer joins, etc. Thats the way I do and works very well.