DMQL2 - DMQL query in data mining example

[Table Content]

• What is data mining?
• What is RETS?
• DMQL2 / DMQL
• DMQL2 / DMQL syntax
• DMQL OR DMQL2 (Data mining query language) tutorials for RETS with examples
• • DMQL OR DMQL2 search conditions on numeric fields - Example
  • DMQL OR DMQL2 search conditions on characters or string fields - Example
  • DMQL OR DMQL2 search conditions on date time fields - Example

What is data mining?

The process of extracting information from enormous data sets is known as Data Mining. We might say, in other words, that data mining is the mining of knowledge.

What is RETS?

RETS stands for Real Estate Transaction Standard, a series of documents that explain how software developers should program their software according to specifications agreed by the Real Estate Standards Organization (RESO).

DMQL2 / DMQL

What is dmql in data mining?

One of the major benefits of using RETS over a different form of distribution (such as FTP) is the ability to clearly tell the server which records you want to return. A query language called DMQL (or, more recently, DMQL2) is used for this, which specifies the structure of the search conditions you are sending to the server.

DMQL stands for Data Mining Query Language.

DMQL consists of simple conditions for the name-value pair passed as part of the requested URL in RETS. You can restrict what kinds of records you get back in real-time by passing under requested conditions.

DMQL2 / DMQL syntax

Name-value conditions are transferred in parentheses with each of them surrounded. For instance:

(name=value)

To pass multiple conditions, each is separated by a comma. This will only return records where name1 is equal to value1 and name2 is equal to value2:

(name1=value1),(name2=value2)

You can also search a single field for multiple values. For example, the below returns records that match 1) name1 is value1a or value1b or value1c, and 2) name2 is value2:

(name1=value1a,value1b,value1c),(name2=value2)

The name part is either the SystemName (when requesting StandardNames use as false, default) or the StandardName (when requesting StandardNames use as true) defined for the field.

The value part is made up of searchable patterns as described in the sections below.

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

The Data Mining Query Language is actually based on the Structured Query Language (SQL). DMQL is also able to interact with databases and data warehouses. To describe data mining tasks, DMQL can be used.

In RETS, DQML is used to get the data from MLS server (Multiple-Listing Service). DMQL has two sections:

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

In Select Statement, field names with comma delimited can be passed. For instance:

ListingPrice,PictureCount,StreetName,Country,City

In Search Condition: It is similar to WHERE clause of SQL server queries language.

DMQL OR DMQL2 search conditions on numeric fields - Example:

In RETS, special characters are allowed depending on the type of field being searched. For Tiny, Small, Int, Long and Decimal fields, basically for numeric fields, special characters can be to indicate ranges of numbers. DMQL is space and case insensitive.

Suppose numeric_field, numeric_field1 and numeric_field2 are numeric fields of any Resource (similar as database or schema in SQL server) and class (similar as table in SQL server).

Example 1: where 'numeric_field' is equal to 2
DMQL query: (numeric_field=2)
Equivalent SQL server query: WHERE numeric_field = 2

Example 2: where 'numeric_field' is greater than or equal to 3
DMQL query: (numeric_field=3+)
Equivalent SQL server query: WHERE numeric_field >= 3

Example 3: where 'numeric_field' is less than or equal to 8
DMQL query:(numeric_field=8-)
 Equivalent SQL server query: WHERE numeric_field 

Example 4: where 'numeric_field' is between 5 and 9 (including 5 and 9)
DMQL query:(numeric_field=5-9)
Equivalent SQL server query: WHERE numeric_field between 5 and 9

Example 5: where 'numeric_field1' is equal to 10 and 'numeric_field2' is greater than or equal to 20
DMQL query: (numeric_field1=10),(numeric_field2=20+)
Equivalent SQL server query: WHERE numeric_field1 = 10 AND numeric_field2 >= 20

Example 6: where 'numeric_field1' is equal to 10 or 'numeric_field2' is greater than or equal to 20
DMQL query: (numeric_field1=10)|(numeric_field2=20+)
Equivalent SQL server query: WHERE numeric_field1 = 10 OR numeric_field2 >= 20

Example 7: where 'numeric_field1' is equal to 10 and 'numeric_field2' is not equal to 20
DMQL query: (numeric_field1=10),~(numeric_field2=20+)
Equivalent SQL server query: WHERE numeric_field1 = 10 AND numeric_field2 != 20

Example 8: where 'numeric_field1' is equal to null or 'numeric_field2' is not equal to 20
DMQL query: (numeric_field1=.EMPTY.)|~(numeric_field2=.EMPTY.)
Equivalent SQL server query: WHERE numeric_field1 IS NULL OR numeric_field2 IS NOT NULL

DMQL OR DMQL2 search conditions on characters or string fields - Example:

In RETS, the condition value allows for special characters depending on the type of field being searched. For Character fields, you're able to use wildcard characters that allow you to find character patterns in a field. DMQL string comparison is not case sensitive.

Example 1: where 'strfieldname' field equal to 'value'
DMQL query: (strfieldname=value)
Equivalent SQL server query: WHERE strfieldname = ‘value’

Example 2: where 'strfieldname' field starts with 'value'
DMQL query: (strfieldname=value*)
Equivalent SQL server query: WHERE strfieldname like ‘value%’

Example 3: where 'strfieldname' field contains 'value'
DMQL query: (strfieldname=*value*)
Equivalent SQL server query: WHERE strfieldname like ‘%value%’

Example 4: where 'strfieldname' field ends with 'value'
DMQL query: (strfieldname=*value)
Equivalent SQL server query: WHERE strfieldname like ‘%value’

Example 5: where 'strfieldname' field has only three characters and second character is 'v'
DMQL query: (strfieldname=?v?)
Equivalent SQL server query: WHERE strfieldname like ‘_v_’

Example 6: where 'strfieldname1' field is not equal to "value1" and  'strfieldname2' is equal to "value2"
DMQL query: ~(strfieldname1=value1),(strfieldname2=value2)
Equivalent SQL server query: WHERE strfieldname1 != ‘value1’ AND strfieldname2 = ‘value2’

Example 7: where 'strfieldname' field is any one of ‘value1’, ‘value2’, ‘value3’
DMQL query: (strfieldname=value1, value2, value3)
Equivalent SQL server query: WHERE strfieldname IN (‘value1’, ‘value2’, ‘value3’)

Example 8: where 'strfieldname' field is none of ‘value1’, ‘value2’, ‘value3’
DMQL query: ~(strfieldname=value1, value2, value3)
Equivalent SQL server query: WHERE strfieldname NOT IN (‘value1’, ‘value2’, ‘value3’)

Example 9: where 'strfieldname' field contains any one of ‘value1’, ‘value2’, ‘value3’
DMQL query: (strfieldname=*value1*, *value2*, *value3*)
Equivalent SQL server query: WHERE strfieldname LIKE ‘%value1%’ OR strfieldname LIKE ‘%value2%’ OR strfieldname LIKE ‘%value3%’

Example 10: where 'strfieldname' field contains none of any ‘value1’, ‘value2’, ‘value3’
DMQL query: ~(strfieldname=*value1*, *value2*, *value3*)
Equivalent SQL server query: WHERE strfieldname NOT LIKE ‘%value1%’ AND strfieldname NOT LIKE ‘%value2%’ AND strfieldname NOT LIKE ‘%value3%’

Example 11: where 'strfieldname' field equals 'val?e' where ? represents a single character
DMQL query: (strfieldname=val?e)
Equivalent SQL server query: WHERE strfieldname =  'val_e'

DMQL OR DMQL2 search conditions on date time fields - Example:

In RETS, the condition value allows for special characters depending on the type of field being searched. For Date, DateTime and Time fields, you're able to use special characters similar to Numeric fields (see above) to search for ranges of dates and times.

For Date fields, the value must be sent in YYYY-MM-DD format.

For Time fields, the value must be sent in HH:MM:SS format.

For DateTime fields, the value must be sent in YYYY-MM-DDTHH:MM:SS format (Date and Time separated by a 'T').

Example 1: where 'date_field' is on or after 2021-01-01
DMQL query: (date_field=2021-01-01+)
Equivalent SQL server query: WHERE date_field >= ‘2021-01-01’

Example 2: where 'date_field' is on or before 2021-01-01
DMQL query: (date_field=2021-01-01-)
Equivalent SQL server query: WHERE date_field 

Example 3: where 'date_field' is between 2021-01-01 and 2021-01-31 (including 2021-01-01 and 2021-01-21)
DMQL query: (date_field=2021-01-01-2021-01-31)
Equivalent SQL server query: WHERE date_field >= ‘2021-01-01’ AND date_field 

Example 4: where 'datetime_field' is on or after 2021-01-01T00:00:00
DMQL query: (datetime_field=2021-01-01T00:00:00+)
Equivalent SQL server query: WHERE date_field >= {ts ‘2021-01-01 00:00:00’}