Build expressions for SQL Query

SQL statements are standard computer query statements, and many query functions in SuperMap are completed by constructing SQL statements. In general, the syntax for SQL Expression is "Select …" (Field name to be output) from … (Dataset name) where … （Query Conditions）(order by … Ascending/descending) (result sort field, optional). The parameters after Select, from, order by, etc. Can be directly selected from the list or drop-down list in the SQL dialog box, while the Query Conditions (Where – Clause statement) needs to be constructed by ourselves. This article will focus on building some common Query Conditions.

It should be noted that since Attributes in File Database are stored in Access format, the wildcard characters used when querying file-type data may not be consistent with the wildcard characters commonly used in queries in databases such as SQL or Oracle. The Province _ R Dataset in the Sample Data Population AndEconomy Workspace is taken as an example for detailed introduction.

  1. A query for a numeric value

    Numeric values can be queried using the "=", "", ",", "=", "=", "Between.. And "wait.".

    For example:

    Province_R.Pop_2014 Between 5000 and 10000

    The

    query is for provinces where the Pop _ 2014 field value (population in 2014) is between 50 million and 100 million.

  2. Fuzzy Searching

    Fuzzy Searching uses like, and different types of Datasources use different matching characters.

    For example:

    (1) Partial match, use "*" (Note, the wildcard in Database and UDB/UDBX Datasource is%, use single or double quotation marks)

    Province _ R. Name like "Mountain% ', Province _ R. Name like'Mountain%'

    The

    query is the province whose name begins with "mountain" in the Name field of the Province _ R Dataset.

    (2) Exact match ( Note that only single quotes are allowed in Database-type Datasource; single or double quotes are allowed for UDB/UDBX Datasource)

    Province _ R. Name like "Beijing, Province _ R. Name like 'Beijing

    The administrative region whose Name field value is "Beijing" in the Province _ R Dataset is

    queried.

    (3) For single word matching, use "_" (Note: the wildcard in Database-type Datasource and UDB/UDBX Datasource is _).

    Province _ R. Name like 'River _ _'.

    The

    query is the province in the Province _ R Dataset where the Name field value is "river" followed by two characters.

  3. Query a specific value

    Using in, determines whether the value of the expression is equal to any one of several values in the specified list.

    For example:

    Province _ R. Name in ( "Beijing", "Shanghai")

    What is

    queried is one or more administrative regions in the Province _ R Dataset where the value of the Name field is "Beijing" or "Shanghai".

    Province_R.ColorID in (1,4)

    The

    query is the province in the Province _ R Dataset where the ColorID field value is 1 or 4.

    Note: For a query of a character field, the query value needs to be enclosed in single quotation marks ( "") or double quotation marks ( ""), but not for a query of a numeric field. For Database-type Datasource, only single quotes are allowed for the value of a character field.

  4. Query whether a field value is null

    Use is NULL (is not NULL)

    For example:

    Province_R.Name Is NULL

    The provinces in the Province _ R Dataset are

    queried if the value of the Name field is null. (It is possible that these provinces forgot to assign a value to this field.)

  5. Query by construction statement. Arbitrary arithmetic expressions

    can be constructed, for example:

    Province_R.GDP_2014 > Province_R.GDP_2009 * 2

    In

    the Province _ R Dataset, the provinces whose GDP in 2014 is more than twice that in 2009.

    Of course, when setting the query statement, we can also use () to set its priority. For example:

    (Province_R.GDP_2014-Province_R.GDP_2013) > (Province_R.GDP_2013-Province_R.GDP_2012)

    In the

    Province _ R Dataset, the provinces whose GDP growth in 2014 is more than that in 2013.

  6. Combining statements

    You can use the "and", "or", and "not" keywords to combine any legal conditional expressions.

    For example:

    Province_R.GDP_2014 > 10000 and Province_R.IncomeLevel > 20000

    Inquire about provinces with GDP of more than 1 trillion and per capita income of more than 20,000 in 2014.

  7. The Application of Comparison Operator in Character Field

    Comparison operators such as, =, =, Wait.

    For example:

    Province_R.Name > = "Finland"

    The

    query is for those provinces in the Province _ R Dataset where the first letter of the Country field value is between F and Z. For Database-type Datasource, only single quotes are allowed for the value of a character field.

  8. Query of date type field Create a date field DataDate

    for the Province _ RDataset to store the statistical time of each piece of data. The following is an example of a common Database-type Datasource query on a date-type field:

    • In
    • SQL (SQL +) Datasource, query the record whose DataDate field is 2019-08-10 in the Province _ R Dataset. Query statement: Province _ R. DataDate = '2019-08-10 12:25:00';
    • In ORACLE Datasource, query the records with the DataDate field greater than 2019-08-10 in the Province _ R Dataset. The query statement is: Province _ R. DataDate & gt; TO_DATE('2019-08-10','YYYY-MM-DD')；
    • In PostGIS Datasource, query the records with the DataDate field less than 2019-08-10 in the Province _ R Dataset, and the query statement is: "Province _ R". Datadate & lt; '2019-08-10 12:25:00'；
    • In the
    • PostgreSQL Data source, query the records of the whole year of 2019 in the Province _ R Dataset. Query statement: Province _ R. Datadate between '2019-01-01 0:0:0' and '2020-01-01 0:0:0';
    • In UDB/UDBX Datasource, query the record containing 2019 in the DataDate field in the Province _ R Dataset, and the query statement is: Province _ R. DataDate Like '2019%'; Query the records with the DataDate field less than 2019-08-10 in the Province _ R Dataset, and the query statement is: Province _ R ". Datadate '2019-08-10 ', date format is YYYY-MM-DD.
  9. Boolean field query The attribute value of a Boolean

    field is True or False. In SQL Query, 1 represents True and 0 represents False.

    For example:

    Province_R.Coastal = 1

    What is

    queried is the coastal cities in the Province _ R. Dataset, and Coastal is a new Boolean field.

  10. Derived field

    For example:

    Province_R.SmArea / Province_R.SmArea

    The temporary field is listed

    in the query Result Table. In addition, you can also give an alias to the temporary field expression as required. You only need to type a space after the original field expression, and then add "as" and the alias. The format is as follows:

    Province_R.SmArea / Province_R.SmArea as Pop_Density

    Aliases are optional. If you give a field expression an alias, it will appear as the temporary field name at the top of the corresponding column when Tabular displays it. If no alias is given, the system uses the expression content itself as the temporary field name. If multiple derived field expressions are specified, they can be separated by commas.

Precautions

When the "=" symbol is used in a comparison query on a Double numeric value, the result may not be available due to the precision problem. It is not recommended to use "=" directly for query.