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

DMQL OR DMQL2 Tutorial

DMQL OR DMQL2 search conditions on characters or string fields

DMQL OR DMQL2 search conditions on look up fields