Re: suggestions for good graphical DB query builder

> >> Better to invest the money in good training than in a dumb-as-dirt
> >> GUI.
> >
> > Although I do agree with you that an understanding of SQL helps
> > you to write proper queries, your opinion about tools producing
> > simplistic queries is a bit over the top, in my opinion.
> >
> > There are tools available that allow you to use derived tables,
> > sub-selects, multiple unions, complex multi joins etc etc. It's not
> > all "select * from ... " :-)

[Quoted] For your convenience, I've tried your examples in our own Query Builder included in Database Workbench, which is based on a third party product.

> Can you find INTERSECT and MINUS?

[Quoted] Yes, no problem whatsoever.

> Can you find WITH?

[Quoted] Parsed and used correctly, did notice a minor GUI error, reported to the vendor.

> Can you find the SAMPLE clause?

[Quoted] Can you give me an example? :)

> Can you find regular expressions?
> Can you find the analytic functions?
> Can you find CONNECT BY PRIOR?

[Quoted] I'm not sure what you mean by "find" here? You mean being able to select them from a list?

> How about CUBE? ROLLUP? GROUPING SETS? GROUP_ID?
> Partition and subpartition selections?
> Database links?

Not tested.

> Could you write this?
> SELECT CAST(POWERMULTISET(cust_address_ntab)
> AS cust_address_tab_tab_typ)
> FROM customers_demo;

Given that expressions can be entered freely, yes. Parsed correctly as well.

> or this?
> SELECT customer_id, SET(cust_address_ntab) address
> FROM customers_demo;

See above.

> or this?
> SELECT COUNT(*)
> FROM all_objs
> WHERE data_object_id IS NOT NAN;

[Quoted] Parser failed on NAN, this surprised me as well, reported to the vendor.

> or this?
> SELECT *
> FROM persons p
> WHERE VALUE(p) IS OF TYPE (employee_t);

[Quoted] Parser failed on OF TYPE, this surprised me as well, reported to the vendor.

> or this?
> SELECT COUNT(*)
> FROM customer_demo
> WHERE cust_address_ntab IS NOT EMPTY;

[Quoted] Parser failed on EMPTY, not surprised anymore, reported to the vendor.

> or this?
> SELECT path(1), depth(2)
> FROM resource_view
> WHERE UNDER_PATH(res, '/sys/schemas', 1)=1
> AND UNDER_PATH(res, '/sys/schemas', 2)=1;

No problem whatsoever.

> Trained people can.

I'm not dismissing training at all.

> I will repeat my statement again for anyone that missed it. Money is
> better spent on hiring and training good employees than buying dumbed
> down GUI tools that give the impression of competence without the
> substance of actually having it. The result is almost always SELECT *
> FROM ....

-- 
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com