Re: [CLSQL-Help] subselects in select :from

On Tue, 2005-11-15 at 10:31 -0700, Kevin Rosenberg wrote:
> James Bielman wrote:
> > Should this syntax for subqueries work?
> > * (clsql:select [*] :from [select [*] :from [admin_users]])
> 
> I wouldn't expect it to. CLSQL has followed the CommonSQL expression
> specification to the best that Xanalyses had documented. If you've
> read the CommonSQL docs, you'll find the documentation is rather scant
> and scattered. I don't recall seeing examples of subqueries like yours.

It turns out all that's needed to get this working right is to make sure
the query in FROM gets rendered as a subquery with the proper
parentheses.  Here's a patch that implements this---I think it would be
a useful extension to CLSQL, since Lispworks just signals an error.

(Passes tests with Oracle and SQLite3 on SBCL and Lispworks 4.3.7, not
sure about others).

Being able to do subselects like this is pretty important for Oracle,
which doesn't support the LIMIT/OFFSET keywords.  In order to page
through a set of records, you have to do ugly things like:

  SELECT * FROM (SELECT * FROM PEOPLE
                  WHERE name >= 'JAMESJB'
                  ORDER BY name)
   WHERE ROWNUM < 10

..making subselects of this type rather important IMHO.  (The reason I'd
like an SQL syntax for this is because I eventually want to be able to
get view class instances back from such a query, but that's another can
of worms...)

James

_______________________________________________
CLSQL-Devel mailing list
[email protected]
http://lists.b9.com/mailman/listinfo/clsql-devel

--- clsql-3.4.4/sql/expressions.lisp	2005-11-11 08:26:09.000000000 -0800
+++ clsql-devel/sql/expressions.lisp	2005-11-15 21:32:43.000000000 -0800
@@ -597,7 +597,8 @@
                                                       :test #'ident-table-equal))
                             database))
           (string (write-string from *sql-stream*))
-          (t (output-sql from database)))))
+          (t (let ((*in-subselect* t))
+               (output-sql from database))))))
     (when inner-join
       (write-string " INNER JOIN " *sql-stream*)
       (output-sql inner-join database))