newbie - odbc missing operator
Using dbase, I want to find all records which have a phone that begins with a specific area code. I try select name, phone from contact1 where phone startswith '(918)' (The same query works with crystal report.) which produces... Error, sql.error: ('37000', -3100, "[Microsoft][ODBC dBase Driver] Syntax error (missing operator) in query expression 'phone1 startswith '(918)''.") I can use = as an operand but need to know how to use other odbc operands. Also, anyone know where a list of operands for dbase might be located on the internet. Michael Guidry michael.guidry@trigenttechnologies.com
On Mon, Aug 16, 1999 at 11:24:22AM -0500, Michael Guidry wrote: Micheal - I'm not familiar with the dbase flavor of ODBC, nor what Crystal Report uses, but my guess is that it'll map standard SQL syntax to whatever the backend wants. In that case, you need to know that 'startswith' is not an SQL standard operator. The standard way to express what you want is: select name, phone from contact1 where phone LIKE '(918)%' The LIKE operator takes two kinds of wild cards: single character: _ (yup, underscore) and zero or more characters: %. You can think of them acting like ? and * at the DOS prompt. BTW, as you can tell, this is an SQL question, not a Zope specific one. Ross
Using dbase, I want to find all records which have a phone that begins with a specific area code. I try select name, phone from contact1 where phone startswith '(918)' (The same query works with crystal report.) which produces... Error, sql.error: ('37000', -3100, "[Microsoft][ODBC dBase Driver] Syntax error (missing operator) in query expression 'phone1 startswith '(918)''.")
I can use = as an operand but need to know how to use other odbc operands. Also, anyone know where a list of operands for dbase might be located on the internet. Michael Guidry michael.guidry@trigenttechnologies.com
-- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
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?
On Mon, Aug 16, 1999 at 11:24:22AM -0500, Michael Guidry wrote: Micheal - I'm not familiar with the dbase flavor of ODBC, nor what Crystal Report uses, but my guess is that it'll map standard SQL syntax to whatever the backend wants. In that case, you need to know that 'startswith' is not an SQL standard operator. The standard way to express what you want is:
select name, phone from contact1 where phone LIKE '(918)%'
The LIKE operator takes two kinds of wild cards: single character: _ (yup, underscore) and zero or more characters: %. You can think of them acting like ? and * at the DOS prompt.
BTW, as you can tell, this is an SQL question, not a Zope specific one.
Ross
Using dbase, I want to find all records which have a phone that begins with a specific area code. I try select name, phone from contact1 where phone startswith '(918)' (The same query works with crystal report.) which produces... Error, sql.error: ('37000', -3100, "[Microsoft][ODBC dBase Driver] Syntax error (missing operator) in query expression 'phone1 startswith '(918)''.")
I can use = as an operand but need to know how to use other odbc operands. Also, anyone know where a list of operands for dbase might be located on the internet. Michael Guidry michael.guidry@trigenttechnologies.com
-- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
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.
participants (3)
-
Michael Guidry -
Phillip J. Eby -
Ross J. Reedstrom