Quick and dirty query tool

[WQ]

Hello Folks,

I was wondering if anyone can suggest a good but not too expensive Visual
Query Tool, which will make do the following:
1. Build my joins by drag and drop columns between tables
2. Build my output clause by double clicking on displayed column list
3. Support native oracle functions like decode, nvl etc. (See more on this
later).

Okay, there are perhaps a lot of tools out there. I myself have evaluated a
few and most of them were very expensive compared to my minimal needs. Two
of them, which I frequently use are Microsoft Access and PL/SQL Query
Reporter by All Round Automation.

Here are the Pros and Cons:

1. MS Access
Pros:
Extremely Fast because, when you link the table using an ODBC DSN, it caches
the data dictionery. This makes joing tables very fast.
Cons:
Does not support native Oracle functions like Decode, NVL etc. The SQL,
which comes out is not formatted properly. You will et dizzy by the number
of parenthesis it generates. Uses ODBC connections only vs ADO.

2. PL/SQL Query Reporter
Pros:
Creates nicely formatted queries.
Cons:
Everything else is bad about this tool. It is slow, because it queries the
DDL at the time of joining tables. Stores queries in a proprietary format,
making it virtually unusable, if you were to switch tools.

I am currently evaluating Advanced Query Tool and DB Kwik Edit.

I would appreciate any input you can provide on this subject.

Regards.

Walt.

[Mark Bole]

WQ wrote: Hello Folks, I was wondering if anyone can suggest a good but not too expensive Visual Query Tool, which will make do the following: 1. Build my joins by drag and drop columns between tables 2. Build my output clause by double clicking on displayed column list 3. Support native oracle functions like decode, nvl etc. (See more on this later). Okay, there are perhaps a lot of tools out there. I myself have evaluated a few and most of them were very expensive compared to my minimal needs. Two of them, which I frequently use are Microsoft Access and PL/SQL Query Reporter by All Round Automation. Here are the Pros and Cons: 1. MS Access Pros: Extremely Fast because, when you link the table using an ODBC DSN, it caches the data dictionery. This makes joing tables very fast. Cons: Does not support native Oracle functions like Decode, NVL etc. The SQL, which comes out is not formatted properly. You will et dizzy by the number of parenthesis it generates. Uses ODBC connections only vs ADO.
[...]

Have you tried pass-through SQL? You can create a view using Oracle
native functions and then use it like other MS Access queries (views).

-Mark Bole

[Daniel Morgan]

WQ wrote: Hello Folks, I was wondering if anyone can suggest a good but not too expensive Visual Query Tool, which will make do the following: 1. Build my joins by drag and drop columns between tables 2. Build my output clause by double clicking on displayed column list 3. Support native oracle functions like decode, nvl etc. (See more on this later). Okay, there are perhaps a lot of tools out there. I myself have evaluated a few and most of them were very expensive compared to my minimal needs. Two of them, which I frequently use are Microsoft Access and PL/SQL Query Reporter by All Round Automation. Here are the Pros and Cons: 1. MS Access Pros: Extremely Fast because, when you link the table using an ODBC DSN, it caches the data dictionery. This makes joing tables very fast. Cons: Does not support native Oracle functions like Decode, NVL etc. The SQL, which comes out is not formatted properly. You will et dizzy by the number of parenthesis it generates. Uses ODBC connections only vs ADO. 2. PL/SQL Query Reporter Pros: Creates nicely formatted queries. Cons: Everything else is bad about this tool. It is slow, because it queries the DDL at the time of joining tables. Stores queries in a proprietary format, making it virtually unusable, if you were to switch tools. I am currently evaluating Advanced Query Tool and DB Kwik Edit. I would appreciate any input you can provide on this subject. Regards. Walt.

Use Oracle Discover or Business Objects Crystal Reports.
Other options ... Brio and Cognos.

Access is incapable of much Oracle functionality and is a pig.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[information_person]

On Fri, 03 Dec 2004 03:02:39 GMT, "WQ" <noreply@no-reply-mail.com>
wrote:
Hello Folks,I was wondering if anyone can suggest a good but not too expensive VisualQuery Tool, which will make do the following:1. Build my joins by drag and drop columns between tables2. Build my output clause by double clicking on displayed column list3. Support native oracle functions like decode, nvl etc. (See more on thislater).Okay, there are perhaps a lot of tools out there. I myself have evaluated afew and most of them were very expensive compared to my minimal needs. Twoof them, which I frequently use are Microsoft Access and PL/SQL QueryReporter by All Round Automation.Here are the Pros and Cons:1. MS AccessPros:Extremely Fast because, when you link the table using an ODBC DSN, it cachesthe data dictionery. This makes joing tables very fast.Cons:Does not support native Oracle functions like Decode, NVL etc. The SQL,which comes out is not formatted properly. You will et dizzy by the numberof parenthesis it generates. Uses ODBC connections only vs ADO.2. PL/SQL Query ReporterPros:Creates nicely formatted queries.Cons:Everything else is bad about this tool. It is slow, because it queries theDDL at the time of joining tables. Stores queries in a proprietary format,making it virtually unusable, if you were to switch tools.I am currently evaluating Advanced Query Tool and DB Kwik Edit.I would appreciate any input you can provide on this subject.Regards.Walt.

I use AQT (Advanced Query Tool). It's $125 for a license and can do
drag and drop query building or straight forward SQL coded queries.
Mark

[WQ]

Folks,

Thanks for your comments.
As far as Oracle Discover or Business Object are concerned, they are not
quick and dirty, and definitely not cheap.
I did finally evaluate Advance Query Tool and DB Kwick Edit.

1. Advance Query Tool could be good, if the interface was not so clumsy.
For example, you can select multple tables, but can not deselect. The only
way to remove your selection is to select first, and then delete it in the
query Window. In general the tool was fast but, as I said, it was clumry to
use.

2. DB Kwick Edit is just a toy. It is okay if you are using it against SQL
Server with 10 tables or MS Access. Connect it to a database with 250
tables, it takes three minutes to load. I sent an email to the folks with
my feedback, still waiting for a reply.

So, I am back to square one. Apart from using SQL Pass through queries in
Access, I have really not found anything worth while.

Regards.

Walt.


"TiefelHund" <nowhere@nowhere.com> wrote in message
news:b082r0hrne7stbj96cmqt9iq9tvl4ii1nq@4ax.com... On Fri, 03 Dec 2004 03:02:39 GMT, "WQ" <noreply@no-reply-mail.com> wrote:Hello Folks,I was wondering if anyone can suggest a good but not too expensive VisualQuery Tool, which will make do the following:1. Build my joins by drag and drop columns between tables2. Build my output clause by double clicking on displayed column list3. Support native oracle functions like decode, nvl etc. (See more on
thislater).Okay, there are perhaps a lot of tools out there. I myself have
evaluated afew and most of them were very expensive compared to my minimal needs.
Twoof them, which I frequently use are Microsoft Access and PL/SQL QueryReporter by All Round Automation.Here are the Pros and Cons:1. MS AccessPros:Extremely Fast because, when you link the table using an ODBC DSN, it
cachesthe data dictionery. This makes joing tables very fast.Cons:Does not support native Oracle functions like Decode, NVL etc. The SQL,which comes out is not formatted properly. You will et dizzy by the
numberof parenthesis it generates. Uses ODBC connections only vs ADO.2. PL/SQL Query ReporterPros:Creates nicely formatted queries.Cons:Everything else is bad about this tool. It is slow, because it queries
theDDL at the time of joining tables. Stores queries in a proprietary
format,making it virtually unusable, if you were to switch tools.I am currently evaluating Advanced Query Tool and DB Kwik Edit.I would appreciate any input you can provide on this subject.Regards.Walt. I use AQT (Advanced Query Tool). It's $125 for a license and can do drag and drop query building or straight forward SQL coded queries. Mark

[Daniel Morgan]

WQ wrote: Folks, Thanks for your comments. As far as Oracle Discover or Business Object are concerned, they are not quick and dirty, and definitely not cheap.

Assuming you have legally licensed Oracle please define "cheap".
How much do you think Discover costs compared to your Oracle license?

I understand quick ... but dirty? Why would anyone want that? If the
reports are worth running they are worth the investment to do it
correctly.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[WQ]

Okay, I am sure you well understand the expression "Quick and dirty". Since
you are asking me to clearly define the dollars, so I will lay it out for
you. Discover license cost more than $10,000 for an enterprise license. So
is Cognos and all the big wigs. Yes, we have all legal licenses for Oracle,
so please don't call the SPA. I guess you are one of those who buys a saw
for cutting paper, where a scissor will do just fine.

There are so many nifty tools out there, and beat big boys not only in
pricing, but functionality as well.
Try ALL Round Automation's PL/SQL Developer, provides a much better value
over all, when compared to, say Toad.

Like you, in the real world, we don't live on grants, you know.


"DA Morgan" <damorgan@x.washington.edu> wrote in message
news:1102272634.69770@yasure... WQ wrote: Folks, Thanks for your comments. As far as Oracle Discover or Business Object are concerned, they are not quick and dirty, and definitely not cheap. Assuming you have legally licensed Oracle please define "cheap". How much do you think Discover costs compared to your Oracle license? I understand quick ... but dirty? Why would anyone want that? If the reports are worth running they are worth the investment to do it correctly. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond)

[Daniel Morgan]

WQ wrote: Okay, I am sure you well understand the expression "Quick and dirty". Since you are asking me to clearly define the dollars, so I will lay it out for you. Discover license cost more than $10,000 for an enterprise license. So is Cognos and all the big wigs. Yes, we have all legal licenses for Oracle, so please don't call the SPA. I guess you are one of those who buys a saw for cutting paper, where a scissor will do just fine. There are so many nifty tools out there, and beat big boys not only in pricing, but functionality as well. Try ALL Round Automation's PL/SQL Developer, provides a much better value over all, when compared to, say Toad. Like you, in the real world, we don't live on grants, you know.

I don't live on grants either. Have never solicited one and never
received one.

I wasn't implying you had a license issue but rather that givent the
cost of a license the cost of a report writer is pretty small: Not zero
but small.

But I am wondering where you got the $10K price. At
http://store.oracle.com they are offering it at $1K for a named user
perpetual license. How many developers do you have?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)