Nested select or call a ZSQL method from another ZSQL?
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? Thanks for any ideas before hand, Regards, Jorge M. -- 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
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 -- Christian Theune - ct@gocept.com gocept gmbh & co.kg - schalaunische strasse 6 - 06366 koethen/anhalt tel.+49 3496 3099112 - fax.+49 3496 3099118 mob. - 0178 48 33 981 reduce(lambda x,y:x+y,[chr(ord(x)^42) for x in 'zS^BED\nX_FOY\x0b'])
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
[Jorge O. Martinez]
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...
That's good, you should normally access the database via views anyway. Cheers, Tom P
Jorge O. Martinez writes:
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... You can build any SQL statement accepted by your database.
The nested "(select ...)" with "in" or "exists" should work... Dieter
participants (4)
-
Christian Theune -
Dieter Maurer -
Jorge O. Martinez -
Thomas B. Passin