DMQL OR DMQL2 search conditions on lookup fields
DMQL OR DMQL2 Query
syntax for RETS
field-criteria
::= ( field=field-value )
field-value
::= lookup-list | .EMPTY.
lookup-list
::= lookup-or | lookup-not | lookup-and |.ANY.
lookup-or
::= | lookup (
*,lookup )
lookup-not
::= ~ lookup *(
, lookup )
lookup-and
::= + lookup *(
, lookup )
(According to RETS 1.8.0
specification)
From the metadata we can
get the all possible values of given fields. In DMQL we have must to pass only
those values. It is necessary to pass lookup value in query.
Values of a look up can
be two types:
1. It keeps single
value.
2. It keeps multiple
values delimited by comma.
Suppose we have two
lookup fields in any resource and class. They are Country and City.Possible
values of field country according to metadata of look up:
Short Name
|
Long Name
|
USA
|
USA
|
UK
|
UK
|
IND
|
INDIA
|
JAP
|
JAPAN
|
RUS
|
RUSSIA
|
CHN
|
CHINA
|
GEY
|
Germany
|
SK
|
South Korea
|
IRA
|
Iran
|
Possible values of field
city according to metadata of look up:
Short Name
|
Long Name
|
NY
|
New York
|
LA
|
Los Angeles
|
CHI
|
Chicago
|
HOU
|
Houston
|
PHO
|
Phoenix
|
MOS
|
Moscow
|
SP
|
Saint Petersburg
|
SAM
|
Samara
|
VAL
|
Vladivostok
|
SIN
|
Singapore
|
GEN
|
Geneva
|
FRA
|
Frankfurt
|
HEL
|
Helsinki
|
ZUR
|
Zurich
|
OSL
|
Oslo
|
COP
|
Copenhagen
|
OK
|
Osaka-Kobe
|
TOK
|
Tokyo
|
PAR
|
Paris
|
Suppose values of two
lookup fields in any MLS sever is as follow:
UID
|
Country
|
City
|
1
|
USA
|
NY, LA, PAR
|
2
|
UK
|
NULL
|
3
|
IND
|
MOS, NY
|
4
|
JAP
|
ZUR, HOU, LA, NY
|
5
|
RUS
|
TOK, PAR
|
6
|
GEY
|
TOK, COP, OK
|
7
|
SK
|
LA
|
8
|
IRA
|
SAM, OSL, PAR
|
9
|
CHN
|
GEN, COP, SAM, OK
|
Let us assume Select
statement only selects UID
Examples of DMQL OR DMQL2 query language
1.
DMQL query:
(Country=|IND)
Verbal meaning:
Filter only those records where Country keeps IND
Output:
UID
|
3
|
2.
DMQL query:
(Country=|USA,UK)
Verbal meaning:
Filter only those records where Country keeps USA or keeps UK
Output:
UID
|
1
|
2
|
3.
DMQL query:
(Country=~USA,UK)
Verbal meaning:
Filter only those records where Country doesn't keep USA as well as UK
Output:
UID
|
3
|
4
|
5
|
6
|
7
|
8
|
4.
DMQL query:
(Country=+RUS,JAP)
Verbal meaning:
Filter only those records where Country keeps USA and UK
Output:
UID
|
5.
DMQL query: (City=|NY)
Verbal meaning:
Filter only those records where City keeps NY
Output:
UID
|
1
|
2
|
3
|
4
|
Note: It also includes
NULL value.
6.
DMQL query: (City=+NY)
Verbal meaning:
Filter only those records where City keeps NY
Output:
UID
|
1
|
3
|
4
|
7.
DMQL query:
(City=|TOK,LA)
Verbal meaning:
Filter only those records where City keeps TOK or LA
Output:
UID
|
1
|
2
|
4
|
5
|
6
|
7
|
8.
DMQL query:
(City=+NY,LA)
Verbal meaning:
Filter only those records where City keeps TOK and LA
Output:
UID
|
1
|
4
|
9.
DMQL query: (City=~PAR,
MOS)
Verbal meaning:
Filter only those records where City doesn't keep PAR and MOS
Output:
UID
|
1
|
2
|
4
|
6
|
7
|
10.
DMQL query: (City=.EMPTY.)
Verbal meaning:
Filter only those records where City keeps NULL value.
Output:
UID
|
2
|
11.
DMQL query: ~(City=.EMPTY.)
Verbal meaning:
Filter only those records where City keeps not NULL value.
Output:
UID
|
1
|
3
|
4
|
5
|
6
|
7
|
8
|
9
|
12.
DMQL query: (City=.ANY.)
Verbal meaning:
Filter only those records where City keeps any value.
Output:
UID
|
1
|
2
|
3
|
4
|
5
|
6
|
7
|
8
|
9
|
1 comment:
how to match two fields like (bed = 2 and bath = 2)
Post a Comment