Jan 25, 2012

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

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


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"