Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 28th, 2003, 01:51 PM
Authorized User
 
Join Date: Aug 2003
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Microsoft Query and Oracle

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.

 
Old August 28th, 2003, 03:05 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old August 28th, 2003, 03:39 PM
Authorized User
 
Join Date: Aug 2003
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old August 28th, 2003, 04:11 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by danielwajnberg
 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
 
Old September 17th, 2003, 10:55 AM
Registered User
 
Join Date: Sep 2003
Location: San Jose, , Costa Rica.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to RSchmidt
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
ERROR [42000] [Microsoft][ODBC driver for Oracle][ sanjeev jha General .NET 0 June 28th, 2008 12:50 AM
Oracle Stored Procedure and Microsoft Query Raoul Oracle 0 January 23rd, 2006 03:41 AM
Microsoft ODBC for Oracle amjad_mahmood VB Databases Basics 1 August 22nd, 2005 10:33 AM
Microsoft Query and Oracle stored procedure shl007 SQL Language 0 October 21st, 2003 02:43 PM
Microsoft Query and Oracle danielwajnberg Oracle 0 August 28th, 2003 01:49 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.