Page 1 of 1

Parametric Search Query

Posted: Mon Apr 12, 2010 12:59 pm
by michel.weber
What exactly is allowed in the SQL Query clause? Can you point me to some documentation?

Functions do not seem to be allowed, for example the following gives an error :

StartDate <= DATE('now') AND EndDate > DATE('now')

also IN does not seem to work properly (without an error message) :
LanguageCoE IN ($qpLanguage)

When i pass EN in the search form, that works, but EN,FR does not.

Parametric Search Query

Posted: Mon Apr 12, 2010 2:13 pm
by mark
The SQL docs are at http://www.thunderstone.com/site/texisman/

StartDate <= convert('now','date')
If StartDate is a date type the conversion is automatic
StartDate <= 'now'

Strings need to be quoted for IN syntax
LanguageCoE in ('EN','FR')

Parametric Search Query

Posted: Mon Apr 12, 2010 2:46 pm
by John
Or if you want to pass a parameter into IN() then make it a strlst first, e.g.

<sum "%s," $langs>
<$qpLanguage=(convert($ret, 'strlst' ))>
<sql "... Field in ($qpLanguage) ...">

Parametric Search Query

Posted: Tue Apr 13, 2010 4:22 am
by michel.weber
Sorry, my question was not clear.

I was talking about the appliance/webinator admin interface on the search settings page

Parametric Search Query

Posted: Tue Apr 13, 2010 11:13 am
by Kai
The alphanumeric tokens allowed in a Parametric Search query are currently "and" "or" "not" "like" "matches" "in" "between", or a Parametric field name defined for that profile. Integer constants and single-quoted strings are allowed too.

You could rewrite your SQL as:

StartDate <= 'now' AND EndDate > 'now'

assuming StartDate is a Date type field.

For the IN query, set separate values for $qpLanguage, e.g. `qpLanguage=EN&qpLanguage=FR' in the query string.