TIMSS 6: Ad-Hoc & Search Operators

I was inspired by Susannah 's WebEx on Ad-hoc Queries today. When looking at the Advanced Query in Ad-Hoc or the Search screen, there are several operators you can use. Below is a listing of them and how to use them.

Operator	Description	Example Data
BT	Between 2 values, separated by commas (no space)	1/1/2005,1/31/2005 (e.g. date between, and including, 1/1/2005 and 1/31/2005 )
ENDS	Ends with entered text	NON (e.g. customer class ends with NON)
EQ	Equals (=)	VA (e.g. state equals VA)
GT	Greater than (>)	12/31/2004 (e.g. date is greater than 12/31/2004)
GTE	Greater than or equal to (>=)	12/31/2004 (e.g. date is greater than or equal to 12/31/2004)
HAS	Data as entered text in it	Council (e.g. Name has the word Council in it)
IN	In list of values, separated by commas (no spaces)	MD,DC,VA (e.g. State is either MD, DC, VA)
LT	Less than (<)	12/31/2004 (e.g. date is less than 12/31/2004)
LTE	Less than or equal to (<=)	12/31/2004 (e.g. date is less than or equal to 12/31/2004)
NOT IN	Not in list of values, separated by commas (no spaces)	MD,DC,VA (e.g. State is other than MD, DC, VA; and not missing)
NOTEQ	Not equal to	VA (e.g. state other than VA; and not missing)
NOTNULL	Not missing	no data needed (e.g. email is not missing)
NULL	Missing	no data needed (e.g. email is missing)
STARTS	Starts with entered text	COR (e.g. customer class starts with COR)
LT OR NULL	Less than or missing	12/31/2004 (e.g. date is less than 12/31/2004 or date is missing)
GT OR NULL	Greater than or missing	12/31/2004 (e.g. date is greater than 12/31/2004 or date is missing)

When you setup the Ad-Hoc Metadata (META001), you can choose a default operator. The important thing to note here is that this is not only what will be defaulted on the Advanced Query tab, but is also the operator that is used on the Simple Query tab. If you are searching on the Simple Query tab and are unsure what the default operator is, click over to the Advanced Query tab and see what it is set to.

Here are 2 update statements to set the default operator to Starts With for views you've already added (starting with USR_) and queries you've saved (where a value isn't already defined). They only update those that are String values, not Date or Nuemeric.

begin tran
update meta_query_view_column
set operator_code='STARTS'
where view_name like 'USR_%'
and datatype_code='S'
rollback tran

begin tran
update meta_saved_query_detail
set operator_code='STARTS'
where (filter_data is null or operator_code not in ('NULL','NOTNULL'))
and exists (select 1 from meta_query_view_column c
where c.view_name=meta_saved_query_detail.view_name
and c.column_name=meta_saved_query_detail.column_name
and c.datatype_code='S')
rollback tran

Applies to: TIMSS6