DMQL OR DMQL2 Tutorial

DMQL OR DMQL2 (Data mining query language) tutorials for RETS with examples

In RETES to get the data from MLS sever it uses DMQL. DMQL has two parts:

1. Select statement

2. Search condition

Note: In DMQL we can use only system name or standard name of fields. Default is system name. If you want to use standard name then you must have to set StandardNames = 1

Select statement: It is very simple. We have to pass the only field name delimited comma. For example:

Acres,ListPrice,RoomArea,Country,City

Search condition: It is most difficult part of DMQL but we will go through from basic search condition to difficult one. It just like WHERE clause of sql server queries language.

DMQL OR DMQL2 search conditions on numeric fields:

Suppose Price, Price1, Price2 and Price3 are numeric fields of any Resource (You can think it as database or schema) and class (You can think it as table).

1.

Sql server query: WHERE Price = 10

DMQL query: (Price=10)

Verbal meaning:  Filter only those records where Price is equal to 10.

Note: DMQL is space and case insensitive. That is following all search conditions correct and equivalent:

a.   ( Price =  10  )

b. (PRICE = 10)

2.

Sql server query: WHERE Price >= 10

DMQL query: (Price=10+)

Verbal meaning:  Filter only those records where Price is greater than or equal to 10.

3.

Sql server query: WHERE Price <= 10

DMQL query: (Price=10-)

Verbal meaning:  Filter only those records where Price is less than or equal to 10.

4.

Sql server query: WHERE Price BETWEEN 10 AND 20

DMQL query: (Price=10-20)

Verbal meaning:  Filter only those records where Price is greater than or equal to 10 and price is less than equal to 20.

5.

Sql server query: WHERE Price1 = 10 AND Price2 >= 20

DMQL query: (Price1=10),(Price2=20+)

Or DMQL query: (Price1=10) AND (Price2=20+)

Verbal meaning:  Filter only those records where Price1 is equal to 10 and Price2 is greater than or equal to 20

6.

Sql server query: WHERE Price1 = 10 OR Price2 >= 20

DMQL query: (Price1=10)|(Price2=20+)

Or DMQL query: (Price1=10) OR (Price2=20+)

Verbal meaning:  Filter only those records where Price1 is equal to 10 or Price2 is greater than or equal to 20

7.

Sql server query: WHERE Price1 = 10 AND Price2 != 20

DMQL query: (Price1=10),~(Price2=20)

Or DMQL query: (Price1=10) AND NOT (Price2=20)

Verbal meaning:  Filter only those records where Price1 is equal to 10 and Price2 is not equal to 20

8.

Sql server query: WHERE Price1 IS NULL OR Price2 IS NOT NULL

DMQL query: (Price1=.EMPTY.)|~(Price2=.EMPTY.)

Or DMQL query: (Price1=.EMPTY.) OR NOT (Price2=.EMPTY.)

Verbal meaning:  Filter only those records where Price1 is equal null or price2 is not null

9. Sql server query: WHERE (Price1 >= 10) OR (Price2 = 20 AND Price3 != 50) OR ((Price1 IS NULL OR Price2 BETWEEN 50 AND 100) AND (Price3 <= 100))

DMQL query: (Price1=10+) | ((Price2=20), ~(Price3=50)) | (((Price1=.EMPTY.) | (Price2 50-100)) , (Price3=100-))

Or DMQL query: (Price1=10+) OR ((Price2=20) AND  NOT (Price3=50)) OR (((Price1=.EMPTY.) OR (Price2 50-100)) AND (Price3=100-))


DMQL OR DMQL2 Tutorial
DMQL OR DMQL2 search conditions on characters or string fields
DMQL OR DMQL2 search conditions on look up fields

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

DMQL OR DMQL2 search conditions on characters or string fields

DMQL or DMQL2 search conditions on Characters or string fields

DMQL OR DMQL2 Query syntax for RETS

field-criteria   ::= ( field=field-value )
field-value  ::= string-list | string-literal | .EMPTY.
string-list  ::= string *( , string )
string       ::= string-eq | string-start | string-contains | string-char
string-eq    ::= 1*ALPHANUM
string-start ::= 1*ALPHANUM *
string-contains  ::= * 1*ALPHANUM *
string-char  ::= *ALPHANUM *(? *ALPHANUM)

string-literal   ::= "<PLAINTEXT except "> *(2" *<PLAINTEXT except "> ) "

(According to RETS 1.8.0 specification)

Suppose Remarks, Remarks1, Remarks2 and Remarks3 is any characters type field.

Examples of DMQL OR DMQL2 query language

1.

Sql server query: WHERE Remarks = 'Exact Help'

DMQL query: (Remarks=Exact Help)

Or DMQL query: (Remarks="Exact Help")

Verbal meaning:  Filter only those records where Remarks is equal to "Exact Help".

Note: DMQL string comparison is not case sensitive. That is following are equivalent:

a. (Remarks='Exact Help')

b. Remarks='EXACT HELP')

2.

Sql server query: WHERE Remarks LIKE 'Exact%'

DMQL query: (Remarks=Exact*)

Verbal meaning:  Filter only those records where Remarks start with "Exact"

3.

Sql server query: WHERE Remarks LIKE '%Help'

DMQL query: (Remarks=*Help)

Verbal meaning:  Filter only those records where Remarks end with "Help"

4.

Sql server query: WHERE Remarks LIKE '%Help%'

DMQL query: (Remarks=*Help*)

Verbal meaning:  Filter only those records where Remarks contains "Help"

5.

Sql server query: WHERE Remarks LIKE '_A_'

DMQL query: (Remarks=?A?)

Verbal meaning:  Filter only those records where Remarks has only three characters and second character is 'A'

6.

Sql server query:  WHERE  Remarks1  !=  'Exact'  AND Remarks2 = 'Help'

DMQL query: ~ (Remarks1=Exact), (Remarks2=Help)

Or DMQL query: NOT (Remarks1=Exact) AND (Remarks2=Help)

Verbal meaning:  Filter only those records where Remarks1 is not equal to "Exact" and Remarks2 is equal to "Help"  

7.

Sql server query:  WHERE  Remarks  IN  ('O',  'E', 'A', 'B', 'C', 'F')

DMQL query: (Remarks=O, E, A, B, C, F)

Verbal meaning:  Filter only those records where Remarks is any of "O", "E", "A", "B", "C", "F"

8.

Sql server query: WHERE Remarks NOT IN ('O', 'E', 'A')

DMQL query: ~ (Remarks=O, E, A)

Or DMQL query: NOT (Remarks=O, E, A)

Verbal meaning:  Filter only those records where Remarks none of "O", "E", "A"

9.

Sql server query:  WHERE  Remarks  LIKE  '%O%'  OR Remarks LIKE '%E%' OR Remarks  LIKE '%A%' 

DMQL query: (Remarks=*O*, *E*, *A*)

Verbal meaning:  Filter only those records where Remarks contains any of "O", "E", "A"

10.

Sql server query:  WHERE  Remarks  NOT  LIKE  '%O%' AND Remarks NOT LIKE '%E%' AND Remarks NOT LIKE '%A%' 

DMQL query: ~ (Remarks=*O*, *E*, *A*)

Verbal meaning:  Filter only those records where Remarks contains none any of "O", "E", "A"

11.

Sql server query:  WHERE  Remarks  LIKE  '%O%'  OR  Remarks LIKE 'E%' OR Remarks = 'A' 

DMQL query: (Remarks=*O*, E*, A)

Verbal meaning:  Filter only those records where Remarks contains "O" or start with "E" or equal to "A"

12.

Sql server query:  WHERE  Remarks1  LIKE  '%O%'  AND Remarks2 LIKE 'E%' OR Remarks3 != 'A' 

DMQL query: (Remarks1=*O*),(Remark2=E*)!~(Remark3=A)

Verbal meaning:  Filter only those records where Remarks contains "O" and start with "E" or not equal to "A"

DMQL OR DMQL2 Tutorial

DMQL OR DMQL2 search conditions on characters or string fields

DMQL OR DMQL2 search conditions on look up fields