Create Data Set > SQL Data Set > Special Case for SQL Data Set

❖Limit Function

When querying Data Mart database (connected over Yonghong driver) through SQL statements, you can use limit n to return the records of the first n rows.

Example: select sum(COGS) COGS , STATE from "Coffee.sqry" group by STATE order by COGS desc limit 3

❖Latitude and Longitude Data of Data Sets

You can use SQL statements to query longitude and latitude. Longitude range is 0-180 degrees. A negative number indicates west longitude while a positive number indicates east longitude. Latitude range is 0-90 degrees. A negative number indicates south latitude while a positive number indicates a north latitude. You can find data within the given circular or rectangular range through the longitude and latitude values given.

For example: select * from "GIS.sqry" where pos incircle (-83.2215,42.1407, 200) indicates the data in a round scope with a radius of 200 meters at the longitude of -83.2215 and latitude of 42.1407 in the data set list GIS. Yonghong driver is required when SQL statements are used to query longitude and latitude.

The column Expression indicates the position of the central point. It combines longitude and latitude with a specific algorithm. The column needs to be added in the way of adding an expression to the data set GIS before it can be used. The screenshot of creating expression interface is shown as follows:

clip0178

The parameter incircle means to query data in a round scope. You need to enter: longitude, latitude and radius (m).

The parameter inrect means to query data in a rectangular scope. You need to enter: longitude, latitude, width and height (m).

The following figure shows how to query longitude and latitude using SQL statements.

clip0179

❖Case When function

The Case When statement can be supported by querying the DATA MART database through SQL statements (via Yonghong's driver connection).

For example：select CASE MARKET WHEN 'Central' then '1'WHEN 'East' then '2'endfrom "coffee.sqry"