SQL Syntax

This guide provides theAppCubeObject Query LanguageSOQL2. Object search languageSOSLUsage Guide

Function List

Category	statement	degree of support
DDL	Creating a table	Not supported
	Deleting a Table	Not supported
	Modifying a table	Not supported
	Adding an index	Not supported
	Viewing Indexes	support
	Recreating Indexes	support
DML	New data	Not supported
	Delete Data	Not supported
	Update Data	Not supported
DCL	Assigning Permissions to Users	Not supported
	Canceling User Rights	Not supported
DQL	Querying the Table List in the System	Partially supported
	Queries the table structure.	support
	Querying Table Data	support

Object data query

Function Description

AppCube provides an object query language.SOQL. It can be invoked in scripts and flows.

SOQL Syntax

• SOQL query:

SELECT
   [/*+OSQL_NATIVE | +OSQL_NOSYSFIELD*/]
   [ALL | DISTINCT ]
   select_expr [, select_expr] ...
   {FROM table_references [FORCE INDEX(index_name)]}
   [WHERE where_condition]
   [GROUP BY {col_name | expr}]
   [HAVING where_condition]
   [ORDER BY {col_name | expr }
     [ASC | DESC]]
   [LIMIT {[offset,] row_count}]
   [FOR {UPDATE }

• SOQL search:

SEARCH
   [ALL ]
   select_expr [, select_expr] ...
   {FROM table_name}
   [WHERE where_condition]
   [LIMIT {[offset,] row_count}]

### Query Syntax

#### DISTINCT

• Function Description:

Object records are deduplicated.

• Example:

SELECT DISTINCT f1 FROM t1

#### Wildcard%

• Function Description:

Fuzzy query of object attributes.

• Example:

SELECT f1 FROM t1 WHERE f1 like 'abc%'

• Constraints:

The wildcard character % is supported. Exercise caution when using the wildcard character when there is a large amount of data, which affects the performance.

GROUP BY clause

• Function Description:

The GROUP BY statement groups the result set according to one or more columns, working with aggregate functions (refer to the aggregate function list).

• Example:

SELECT f1, count(1) FROM t1 WHERE f1=1 GROUP BY f1

ORDER BY clause

• Function Description:

The ORDER BY statement sorts the result set of specified columns in ascending (ASC) or descending (DESC) order.

• Example:

SELECT f1, f2 FROM t1  WHERE f1 = 1  ORDER BY f1 DESC

Hanting clause

• Function Description:

It is used in the OSQL aggregation function. The HAVING function cannot use aliases and can filter the aggregation result again.

• Example:

select f1, count(f2) cnt from t1 where f1=1 group by f2 having count(f2) >10

limit operator

• Function Description:

The LIMIT clause is used to specify the number of records to be returned. There are two types of LIMIT clauses:
1. If there is only one parameter `limit M`, the first M records are obtained.
2. If there are two parameters `limit M,N`, data is obtained from the Mth record (the program index starts from 0). A maximum of N records can be obtained.

• Example:

SELECT f1 as f1, f2 as f2 FROM t1 where f1 >1 limit 0, 100

IN operator

• Function Description:

The IN operator allows us to specify multiple values in the WHERE clause.

• Example:

SELECT f1 as f1 FROM t1 where f1 in(1,2,3,4)

Inner Join Keyword

• Function Description:

The INNER JOIN keyword returns a row when at least one match is found in the table.

• Example:

SELECT t1.f1 as f1 FROM t1 INNER JOIN t2 ON t1.f1=t2.f1

• sConstraints:

You are advised not to associate more than four tables. Otherwise, the query performance may be affected.

LEFT JOIN keyword

• Function Description:

The LEFT JOIN keyword returns all rows from the left table (table_name1) even if there is no matched row in the right table (table_name2).

• Example:

SELECT t1.f1 as f1 FROM t1 LEFT JOIN t2  ON t1.f1=t2.f2

• Constraints:

The query performance may be affected.

UNION query

• Function Description:

The results of the statement are combined into a result set. Multiple SELECT statements remove duplicate data.

• Example

select name, id from cmc__unique__CST  union select name, id from cmc__v__CST

Subquery

in subquery

• Function Description:

Select subqueries are supported.

Select subqueries are supported.

• Example:

1. In subquery
select b.f1 from t1 b where b.f1 not in (select f1 from t2 where f1 <8 and f2>10);
select b.f1 from t1 b where b.f1 in (select f1 from t2 where f1 <8 and f2>10);
2. Join In subquery
select a.f1, a.f2 from t1 b inner join (select f1 ,f2 from t2 where f1 <8 and f2>10)a on a.f1=b.f1 where b.f2 >1;
3. Cross Info subquery
select a.f1, a.f2,b.NS__f1__CST from t1 b,(select f1,f2 from t2 where f1 <8 and f2>10 group by f1)a where a.f1=b.NS__NO__CST ;
4. from subquery
select a.NS__f1__CST from (select NS__f1__CST from t1) a where a.NS__f1__CST = 'a';

exists subquery

• Function Description

Supports the EXACT subquery. The EXACT subquery is used to check whether the subquery returns at least one row of data. The subquery does not actually return any data, but returns the value True or False. The EXEC specifies a subquery to check the existence of a row.

• Use Example

select id, name from cmc__unique__CST where exists (select id from cmc__v__CST)

Hint Syntax

• Function Description:

The hint syntax can be used to improve performance in specific scenarios.

1. Specify an index using hints("force index")
   select t1.name,t1.f2, b.id from t1 left join t2 force index(t2_f2_UniqueIndex) on t1.id = t2.f1;
2. Specifies whether to return system attributes (OSQL_NOSYSFIELD) through hints.
   select /*+OSQL_NOSYSFIELD*/ from t1 where f1=1
3. Use hints to specify whether Name needs to be returned for the Lookup attribute(OSQL_NATIVE)
   select /*+OSQL_NATIVE*/ from t1 where f1=1

Function

• aggregate function

Function	Description
AVG(column)	Returns the average value of a column.
COUNT(column)	Returns the number of rows in a column (excluding NULL values).
COUNT(*)	Returns the number of rows in a column (excluding NULL values).
MAX(column)	Returns the highest value in a column.
MIN(column)	Returns the lowest value of a column
SUM(column)	Returns the sum of a column.

• String processing function

Function	Description
CONCAT (str1, str2, ...)	String concatenation function
CONCAT_WS (delimiter, str1, str2, ...)	String concatenation function for specifying delimiters
SUBSTR(str, start, length)	Truncates a character string. In the preceding command, str indicates a character string. start indicates the start position and length indicates the length.
SUBSTRING_INDEX(str, delimiter, number)	Returns a substring of a string before the specified number of delimiters appear.
UPPER(str)	Returns the character string str. All characters are uppercase based on the current character set.
LOWER(str)	Returns the character string str converted to lowercase letters based on the current character set mapping.

• Time processing function

Function	Description
NOW()	Returns the current date and time
CURDATE()	The current date
CURTIME()	The current time.
DATE(date)	Extracts the date part of a date or date/time expression
QUARTER(date)	Function Returns the quarter of the year for the given date value (a number between 1 and 4)
DAYOFWEEK(date)	Returns an integer ranging from 1 to 7, representing Sunday through Saturday.
DAYOFMONTH(date)	Returns the day in the month of the date, ranging from 0 to 31.
DAYOFYEAR(date)	Returns the day of the year as a date, in the range of 1 to 366
DAYNAME(date)	Gets the date of its workday name
MONTHNAME(date)	Date: full name of the month in English
MINUTE(date)	Return the minute of the time, ranging from 0 to 59
HOUR(date)	Extraction time
WEEK(date)	Returns the week sequence number.
DAY(date)	Equivalent to DAYOFMONTH()
MONTH(date)	Returns the month sequence number corresponding to the transferred date.
YEAR(date)	Return year
DATE_FORMAT(date, format)	Formats the date as specified, for example DATE_FORMAT(date, '%y-%m-%d')
DATE_ADD(date,'INTERVAL expr type')	Adds a specified time interval to the date, for example DATE_ADD(OrderDate,'INTERVAL 2 DAY')
DATE_SUB(date,'INTERVAL expr type')	Subtracts the specified time interval from the date, for example DATE_SUB(OrderDate,'INTERVAL 2 DAY')

OSQL does not support the following keywords:

RIGHT JOIN 、FULL JOIN、BETWEEN、SELECT INTO、USING Operator。

SOQL

A search statement can be regarded as a subset of an SQL statement. Most common SQL query statements are supported. You only need to change the select keyword at the beginning to search.
The limitations and features of the search statement are as follows:

1. Currently, the search statement does not support aggregation, grouping, wildcards, and distinct. These functions will be optimized and opened in later iterations.
2. The search statement must contain the where clause. Otherwise, an error is reported.
3. Strings are converted to the text type in es by default. Therefore, inverted indexes can be implemented for word segmentation. Character string types cannot be sorted because Fielddata=on is not set by default.
4. In the search statement, the WHERE clause cannot contain fields that cannot be searched for. For example, search * from myobject where t1 = 'abc' is not supported (t1 is a field that cannot be searched for).
5. Similar to the SQL statement, the search statement is case insensitive.
6. Currently, the search statement can only be used to search a single table.
7. The null value and empty string cannot be matched because the empty value does not create an inverted index.
8. English query words are separated by space, and Chinese query words are separated by character. For example, if the document content is "Business Application Platform", you can query the business and the flat can be matched, but the bus cannot be matched.
9. The text type uses the English word segmenter. Therefore, the text type is case-insensitive. For example,'movie' matches'Movies'.

Index

Function Description

The console provides the functions of viewing table index information and recreating indexes. However, this function does not apply to adding indexes to specific fields. Index rebuilding is to reduce fragmented data storage and improve data query efficiency.

Grammar

• Query the table index information.

show index <tableName>

• Rebuild the index.

rebuild index <tableName>

Search engines

Function Description

Accelerate the efficiency of full-text search and field value matching. When the data volume is large, the performance is greatly improved compared with the SQL query using the select keyword.

The console provides functions such as displaying, creating, deleting, and recreating search engine indexes. To add search engine indexes to a specific field, set the IsSearchable attribute to true. The following syntax can work properly only if the fields in the table are indexed by the search engine.

Grammar

• To create a search engine index:

searchindex create <tableName>

• Delete Search Engine Index:

searchindex delete <tableName>

• To refresh the data from the database to the search engine index:

searchindex rebuild <tableName>

• To display the index information of the re-created search engine, run the following command:

searchindex show