Valid SQL Operands

Last updated
Save as PDF

The following operands are valid for SQL statements used in State Form File Generation. This is also applicable to SQL in Widget Definition and Form Definition.

Operation	Operand
Less than/Equal to	<=
Less than	<
Equal to	=
Not Equal To	!=
Greater than/Equal to	>=
Greater than	>
And	&
Or	\
Does not have a value	IS NULL
Has a value	IS NOT NULL
Contains a value (multi-select dictionaries)	CONTAINS(field1, field2, delimiter) Ex.: CONTAINS(p.PATID,'0001','&') Ex: NOT(CONTAINS(p.PATID,'0001','&')) Ex: CONTAINS(p.PATID,s.client_id_list,'&') Ex: NOT(CONTAINS(p.PATID,s.client_id_list,'&'))
Contains multiple values	IN (field1, field2, field3,etc...) Ex: p.PATID IN ('0001','0002','0003')
Contains certain characters	LIKE 'text' Ex.: diagnosis_code_table LIKE 'Alcohol%' >> returns all records where the diagnosis_code_table entry starts with "Alcohol" Similar for '%Alcohol' and '%Alcohol%'
Entity List Inclusion/Exclusion	Entity Lists can be used in the Table Elements Grid 'Additional SQL Conditions' for associated tables Syntax: TableName.FieldName NOT IN ({Entity List Name}) / TableName.FieldName IN ({Entity List Name}) Ex: SYSTEM.patient_current_demographis.PATID NOT IN ({Test Clients})