[Zope] Nested select or call a ZSQL method from another ZSQL?

[Jorge O. Martinez]

Hi:

Yes, I kinda had an idea of the SQL to use (the DB we're using is Oracle), the 
real question I had was how to do it with a Zsql method. I was wondering if it 
supported it... We solved this issue for now creating a 'view' since I could 
not figure out how to do it (or if it can be done) with ZSQL methods...However, 
I am still wondering if it can be done with ZSQL...

Thanks!

Jorge M.

Christian Theune wrote:
> Hi,
> 
> the sqldelimiter is used to separate two or more querys that are executed *after* each other.
> 
> The behaviour you want is called "subselects" and is depending on you RDBMS. MySQL for example (IIRC)
> doesn't have those, PostgreSQL has them.
> 
> Using PostgreSQL it might look like this:
> 
>  SELECT
>     *
>  FROM
>     foo
>  WHERE
>     id = ( SELECT
>                 max(id)
>             FROM
>                 bar )
> 
> * Jorge O. Martinez <jmartinez@eMediaMillWorks.com> [020610 00:10]:
> 
>>Hi:
>>
>>I am not sure what is the better way to go, but I need to do a select 
>>statement using some input from another zsql method. I tried using 
>> <dtml-var sql_delimiter> but didn't get it work, the query is simple:
>>
>>select * from foo
>><dtml-sqlgroup where>
>> id =
>><dtml-var sql_delimiter>
>>select my_id from bar where user = VAR and class = CLA
>></dtml-var sql_delimiter>
>></dtml-sqlgroup>
>>
>>(I know this looks horrible, but not sure what the right syntax would be 
>>for that nested where and select statements.
>>--------------
>>
>>The only value I pass to this method are VAR and CLA, I would then expect 
>>the method to run the nested select query first, and then, once I got the 
>>value for 'my_id', I would like the outer select to be executed with the 
>>value I got from the nested one. I am not having luck, is the above the 
>>right approach? Or would it be better to just to a separate zsql method 
>>with the query inside sql_delimiter? If so, what would be the syntax?
> 
> 
> Well. If your RDBMS doesn't support subselects, then two separated sql methods
> remain your only choice.
> 
> Regards 
> 
> Christian
> 



-- 
Jorge O. Martinez
MIS Senior Associate
eMediaMillWorks
1100 Mercantile Lane, Suite 119
Largo, MD 20774
E-mail  => jmartinez@eMediaMillWorks.com
Phone   => (301)883-2482 ext. 105
Fax     => (301)883-9754