Jan 24, 2012

DMQL OR DMQL2 search conditions on look up fields


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:

  1. how to match two fields like (bed = 2 and bath = 2)

    ReplyDelete