Some of the example code was unproperly copied. Pleas ignore the first message.

A complex question on SQL engine SQL code-generation for those who believe SQL is a powerful yet often under-used resource.

I wish to deliver multi-vendor capabilities to a VFP C/S application currently using Sybase Anywhere (works great for small to mid-size database volumes).

This app - a generic HR data mart - generates on-the-fly SQL pass-thru SELECT statements that rely highly on self-joins using the some of the powerful tricks of SQL 92... no use of UDF, no server code, no stored-procs, all dynamic calls on fully normalised data structures.

Sybase anywhere sophisticated SQL grammar allow for incredibly powerful SELECT calls that take advantage of use-and-abuse of self-joins and SUB-SELECT statement DIRECTLY in the expression-list (a hot spec not matched by oracle, ibm, sybase and others).

A short example:

Listing the name,first_name,number of kids,spouse name and kids list for big families ... automatically generates a spaghetti-yet-powerful:

SELECT last_name,first_name,person_id,(SELECT first_name FROM family AS t0 WHERE person_id=personal_details.person_id and (relation='spouse') and (SELECT COUNT(*) FROM family AS t1 WHERE person_id=personal_details.person_id and (relation='spouse') and (t1.birth_date>t0.birth_date or (t1.birth_date=t0.birth_date and t1.first_namet0.birth_date or (t1.birth_date=t0.birth_date and t1.first_namet0.birth_date or (t1.birth_date=t0.birth_date and t1.first_namet0.birth_date or (t1.birth_date=t0.birth_date and t1.first_namet0.birth_date or (t1.birth_date=t0.birth_date and t1.first_name 4 ORDER BY last_name,first_name

Oops ! This code (and even statements 5x as big..) runs like a charm (in Sybase not on Fox!)... and back to Fox for cursor management.

Has anyone tried to generate equivalent kind of code on Oracle and/or SQL server resorting to using sub-select in the FROM clause rather than the expression-list and KEY JOIN features.

A code that could look like:

SELECT last_name,first_name,table_for_column_1.first_name AS "spouse name" FROM personal_details KEY JOIN (SELECT first_name FROM family AS t0 WHERE person_id=personal_details.person_id and (relation='spouse') and (SELECT COUNT(*) FROM family AS t1 WHERE person_id=personal_details.person_id and (relation='spouse') and (t1.birth_date>t0.birth_date or (t1.birth_date=t0.birth_date and t1.first_name
Thanks for your patience at reading this long, long stuff!

François