(sv) HDC Query Language (HQL)

1 Syntax
2 List of operators:
- 2.1 Data types:
3 Example expressions
4 HQL Query language support for field names
- 4.1 Examples

HyperDoc Query Language (HQL) is used across HDC application: advanced search,presentation filters and HOPA (as one of possible locators).

Syntax

HQL statement consists of at least one expression in following form:

{property_name} {operator} {value} || {property_name} {operator}

property_name - name of the property of the given class

operator - comparison operator

value - the expected value of property

Please note that not numeric values have to be double quoted (this applies to text and dates)

Several expression could form complex statements with usage of logical operators: and, or. Additionally to accurately specify condition priority one could use parenthesis. Here is a sample form of complex statement for illustration:

((expression or expression) and (expression and expression or expression)) and expression

Please note that logical operators should be used in lower case, same applies to property names and some operators (which are not based on symbols).

List of operators:

Operator Name	Operator Symbol	Example	Suitable types *

Operator Name	Operator Symbol	Example	Suitable types *
Equals	=	property = 1	numeric, text, datetime and boolean
Not Equals	!=	property != "sample"	numeric, text, datetime and boolean
Not Less Than	>=	property >= 11.23	numeric
Not Greater Than	<=	property <= 12	numeric
Greater Than	>	property > 1	numeric
Less Than	<	property < 5.05	numeric
Before	before	property before"21.07.2017"	datetime
After	after	property after "2013-05-25T12:12:12.100+02:00"	datetime
In Set	in	property in ("DICT1", "DICT2", "DICT3")	numeric, text
Not In Set	not in	property not in (1, 2, 3)	numeric, text
Contains	contains	property contains "room"	text
Not contains	not contains	property not contains "room"	text
Starts With	starts-with	property starts-with "plan"	text
Not Starts With	not starts-with	property not starts-with "plan"	text
Is Empty	is empty	property is empty	numeric, text, datetime and boolean **
Is Not Empty	is not empty	property is not empty	numeric, text, datetime and boolean **

Data types:

Numeric types: Float, Integer, Serial, Currency, Area, Quantity, Length
Text types: Text, GUID
boolean: bool (true/false; on/off; yes/no; 0/1)
datetime: various date formats:
- dd/MM/YYYY
- dd-MM-YYYY
- dd.MM.YYYY
- YYYY/MM/dd
- YYYY-MM-dd
- YYYY.MM.dd
- YYYY-MM-ddTHH:mm:ss.SSSXXX
Field values (there is a possibility to process HQL queries that compares values of two fields (numerical and string only).

* using wrong operator doesn't end with an error unless it is strictly not possible to evaluate given expression i.e. starts-with "test" would raise error, when "text" > "Text" would be processed, but is not recommended

** for text types we not only check whether value is set, but also whether it is not an empty string

Example expressions

fi2space_areabra < 11 and fi2space_descr starts-with "test"
fi2space_function in ("00", "01") or fi2space_function contains "PER" and hd_objcustom2 in ("BIA")
hd_docrevdate before "2017-08-08T00:00:00.000+02:00"
(fi2space_function in ("00", "01") or fi2space_function contains "PER") and hd_objcustom2 in ("BIA")
((fi2space_class is empty) or (fi2space_function is empty))
unbounded_height not in (-3.14, 0, 1.1, 2.5) and fi2space_class contains "ÖVA"
fi2space_areabra is empty and fi2space_function in ("00", "01") or fi2space_function contains "PER" and hd_objcustom2 in ("BIA") or fi2space_areanta > 11 and (fi2space_class starts-with "ÖVA" or dummy_date before "2013-01-01")
hd_docdescr = "Italic"
fi2space_id in ("000480000900059","000480000900051","000480000900040")
hd_docdate before "1990-12-01"
fi2space_id contains "48000090004"
fi2space_descr starts-with "RUM 2"
hd_docdate before "1996-11-05" and hd_docdate after "1996-11-03"
fi2space_class in ("02","ÖVA") and fi2space_function not in ("KOMMUNIKATION","03","02","00","PERSONAL","SERVICE","99","TEKNIK","01")
fi2space_class is empty
fi2space_areabra >= "50" and fi2space_areabra < 100
fi2space_id != "01"
hd_docdescr is not empty
rentalunit_occupied = "true"
fi2space_descr starts-with fi2space_name
fi2space_areabra > fi2space_areanta

HQL Query language support for field names

So far query language (HQL) used for filters, presentations or object search only accepted specific values.

These values could be given as string, number or date. Example:

space_diff_bra_bta >= 1.02 and space_diff_bra_bta <= 1.05
space_diff_bra_bta >= 1.02 and space_diff_bra_bta <= 1.05
fi2space_name starts-with "test"

Now the language was extended to support also comparing values from other fields. For now numerical and string only. Example:

space_diff_bra_bta > 1.05 and space_bra > space_bta
(space_diff_bra_bta > 1.05) and (space_bra > space_bta)

Examples

Automatic presentation filter example with HQL comparing two numerical fields:
Manual Presentation filter example with HQL comparing two text fields:
Auto Sum plugin with HQL condition

This was not added to advanced search interface so it can be used only there were you can type in HQL (like filters, smart fields).