[Zope] newbie - odbc missing operator

[Phillip J. Eby]

At 05:51 PM 8/16/99 -0500, Michael Guidry wrote:
>Thanks for the pointer. Let's get into some Z stuff. How does one do the
>following?
>Instead of the string '(918)' we need to select for a list of areacodes.
>i.e. (918), (404), (950).
>
>something like ...
>select name, phone from contact where phone LIKE '(918)%' OR phone LIKE
>'(404)%'....
>should be built using DTML???
>but how?
>

Assuming "areacodelist" is a Python list of the form:

['918','404','950']

Then write your method thus:

select name, phone from contact 
<!--#sqlgroup where-->
<!--#in areacodelist-->
<!--#unless sequence-start-->OR <!--#/unless-->
phone LIKE '(<!--#var sequence-item sql_quote-->)%'
<!--#/in-->
<!--#/sqlgroup-->


This will output something like:

select name, phone from contact 
WHERE
phone LIKE '(918)%'
OR phone LIKE '(404)%'
OR phone LIKE '(950)%'

It will also correctly handle an empty list of areacodes, or a list
containing only one areacode.  If you have other criteria you want applied,
use an <!--#and--> seperator between the <!--#/in--> and the
<!--#/sqlgroup--> in order to add another block of criteria.  The sqlgroup
tag will then insert parentheses and an AND if required to make your query
correct.