SQL Subqueries

I'm not quite sure what you mean by "multi-level selects", but I suspect you mean correlated subqueries. Similar to sentence fragments.

Useful device. Will be used. More later.

Like most syntactic outliers, subqueries are useful. Used judiciously. The can simplify some convoluted coding, reduce extra steps, and occasionally improve performance. They can also to the reverse of all in spades.

My usual preference is to keep subqueries restricted to a depth of one, possibly two in exceptional cases. Anything more than this and I start abstracting out the queries, either as views or additional CREATE TABLE queries (use your workspace for these). The set nature of SQL means that, if you can work out the logic and parameters of the queries themselves, you can replace a subquery with the equivalent table or view.

I have to admit complete ignorance of PL/SQL, but I've used SQL extensively in generating reports with SAS, where many intermediate steps can often be saved with some only mildly convoluted query logic.

I've seen queries similar to

select d.(stuff) from a, b, c, d
where a.(stuff) = b.(stuff) and b.(stuff) = c.(stuff) and c.(stuff) = d.(stuff)

and have seen query "optimizers" make stupid decisions about what to select from where and when. This is particularly evident when you upgrade from one version of a database to another; often times they've "improved" the optimizer enough to shoot the hell out of your carefully crafted queries that worked with the old one.

(Most databases seem to have pathetic query analyzers and diagnostics, but I digress.)

Not that this helps in this particular problem. I ran into a report converting tool ("automatically" converted from one 4GL with one flavor of database selection syntax to another) that did some Gawdawful stuff to the SQL, but because there were so many reports it wasn't possible to go through and optimize each report. I have every reason to think that those reports (in the tortured SQL version) still exist.

Welcome to IWETHEY!