Sorry, for the late reply, seems like not many ppl really use sqltest. The sqgroup/sqltest combo has some things you didn't find out: sqltest works like the if, when you add the optional attribute, like this: <dtml-sqltest ticker type="nb" optional> (op=eq is the default) the sqlgroup provides you with some additional tags, the "and" and "or" tag, so it looks for itself when to set the `and' and 'or' operators, depending if the previous sqltest did something or didn't create a statement, due to the `optional' tag. This could be your complete query: SELECT company_name,ticker,primary_exchange FROM company_index <dtml-sqlgroup where> <dtml-sqltest ticker type=nb optional> <dtml-and> <dtml-sqltest company_name op=like type=nb optional> <dtml-and> <dtml-sqltest industry_group type=nb optional> <dtml-and> <dtml-sqltest industry_subgroup type=nb optional> <dtml-and> <dtml-sqltest market_cap type=nb optional> <dtml-and> <dtml-sqltest market_sector type=nb optional> <dtml-and> <dtml-sqltest primary_exchange type=nb optional> </dtml-sqlgroup> Regards Christian * Jorge O. Martinez <jmartinez@eMediaMillWorks.com> [020607 18:02]:
Hi:
Not sure if this is a DB question, or DTML question, so I'm sending it to the general Zope list.
I've been banging my head over this relatively simple query, which (if my understanding of the dtml-sqltest tag is correct), should be fine. The idea is simple, get 3 columns from company_index, and display the results, if there's further constrains set (like ticker, company_name, etc.), taylor the query accordingly:
select company_name,ticker,primary_exchange from company_index <dtml-sqlgroup where> <dtml-sqltest ticker op=eq type=nb> <dtml-sqltest company_name op=like type=nb> <dtml-sqltest industry_group op=eq type=nb> <dtml-sqltest industry_subgroup op=eq type=nb> <dtml-sqltest market_cap op=eq type=nb> <dtml-sqltest market_sector op=eq type=nb> <dtml-sqltest primary_exchange op=eq type=nb> </dtml-sqlgroup>
but it's not happy. It keeps displaying errors like this:
*************
Zope has encountered an error while publishing this resource.
Error Type: KeyError Error Value: industry_group
Troubleshooting Suggestions
* This resource may be trying to reference a nonexistent object or variable industry_group. * The URL may be incorrect. * The parameters passed to this resource may be incorrect. * A resource that this resource relies on may be encountering an error.
For more detailed information about the error, please refer to the HTML source for this page.
If the error persists please contact the site maintainer. Thank you for your patience.
**************
Which in my opinion does not make sense. I intedended for only one var to be set, and leave the others non-existing alone. I get this error when I only set the company_name, and I expect my query to be:
select company_name,ticker,primary_exchange from company_index where company_index like 'Value I passed%'
Shouldn't sqltest should recognize that, and not raise an error?
-------------------------
I used a variation, which I don't think should be used because supposedly sqltest's job is to test if a var is set, and include it in my 'where' if it is. As you can see, I check with a <dtml-if ..> if the var is set, but I think I should not have to do it since, I think based on docs I've read, that <dtml-sqltest...> should do that.
select company_name,ticker,primary_exchange from company_index <dtml-sqlgroup where> <dtml-if ticker> <dtml-sqltest ticker op=eq type=nb> </dtml-if> <dtml-if company_name> <dtml-sqltest company_name op=like type=nb> </dtml-if> <dtml-if industry_group> <dtml-sqltest industry_group op=eq type=nb> </dtml-if> <dtml-if industry_subgroup> <dtml-sqltest industry_subgroup op=eq type=nb> </dtml-if> <dtml-if market_cap> <dtml-sqltest market_cap op=eq type=nb> </dtml-if> <dtml-if market_sector> <dtml-sqltest market_sector op=eq type=nb> </dtml-if> <dtml-if primary_exchange> <dtml-sqltest primary_exchange op=eq type=nb> </dtml-if> </dtml-sqlgroup>
By the way, this way does not work either, I also get an error using the above syntax:
****************
Zope Error
Zope has encountered an error while publishing this resource.
Error Type: DatabaseError Error Value: (933, 'ORA-00933: SQL command not properly ended')
Troubleshooting Suggestions
* The URL may be incorrect. * The parameters passed to this resource may be incorrect. * A resource that this resource relies on may be encountering an error.
For more detailed information about the error, please refer to the HTML source for this page.
If the error persists please contact the site maintainer. Thank you for your patience.
****************
Any hints are gretly appreciated.
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
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
-- 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'])