OpenSearch:SELECT statement

Overview

SELECT statements select data from tables.

SELECT statements are the core of the query syntax. All advanced query syntax is used based on SELECT statements. The first code block in this topic describes the simple syntax of SELECT statements supported by HA3. For information about how to use advanced SELECT syntax, see the code blocks in the CASE WHEN expressions section.

Supported versions

OpenSearch Retrieval Engine Edition whose HA3 version is V3.7.0 or later

Syntax

SELECT:
  SELECT [ DISTINCT ]
   { * | projectItem [, projectItem ]* }
  FROM tableExpression
   [ WHERE booleanExpression ]
   [ GROUP BY { groupItem [, groupItem ]* } ]
    [ ORDER BY { orderByItem [, OrderByItem ]* }]
   [ HAVING booleanExpression ]
    [ LIMIT number]
    [ OFFSET number]
  
projectItem:
  expression [ [ AS ] columnAlias ]
  | tableAlias . *

Example

SELECT * FROM table;

SELECT f1, f2 AS ff FROM table;

SELECT DISTINCT f FROM table;

SELECT * FROM (
      SELECT f1, count(*) AS num
      FROM t1
      GROUP BY f1
  ) tt
  WHERE tt.num > 100;

Feature description

CASE WHEN expressions are similar to if...else statements. If the condition in the WHERE clause is met, the value in the THEN expression is used. Otherwise, the value in the ELSE expression is used.

​

Supported versions

OpenSearch Retrieval Engine Edition whose HA3 version is V3.7.5 or later

​

Syntax

CASE 
WHEN condition_1 THEN expression_1
WHEN condition_2 THEN expression_2
...
ELSE expression_n
END

Examples

1. CASE WHEN is used as an output expression.

SELECT  
    CASE    
        WHEN warehouse_id=48 THEN warehouse_id
        WHEN warehouse_id=24 THEN id 
        ELSE wave_status 
    END AS aa
FROM    s_wmp_package_wave
WHERE   wave_status = 0
LIMIT   10;

2. CASE WHEN is used as a conditional expression.

SELECT * FROM
(
  SELECT  
  CASE    
    WHEN warehouse_id=48 THEN warehouse_id
    WHEN warehouse_id=24 THEN id 
    ELSE wave_status 
  END AS aa
  FROM    s_wmp_package_wave
  WHERE   wave_status = 0
) t
WHERE t.aa > 10
LIMIT   10;

SELECT *
FROM s_wmp_package_wave
WHERE CASE 
        WHEN warehouse_id=48 THEN warehouse_id
  WHEN warehouse_id=24 THEN id
        ELSE buyer_id
END > 10
AND wave_status = 0
LIMIT   10;

Note:

1. The values of all THEN and ELSE expressions must be of the same type.

2. CASE WHEN can be used only as an independent expression. CASE WHEN cannot be nested in other expressions or user-defined functions (UDFs). For example, the following CASE WHEN expression is not supported:

SELECT *
FROM s_wmp_package_wave
WHERE CASE 
        WHEN warehouse_id=48 THEN warehouse_id
  WHEN warehouse_id=24 THEN id
        ELSE buyer_id
END + wave_status> 10
LIMIT   10;

3. CASE WHEN cannot be used for multi-value fields.

4. CASE WHEN must include an ELSE expression.