OpenSearch:SELECT

SELECT

Description

The SELECT statement retrieves data from a table.

All advanced queries are built upon the SELECT statement. This section covers the basic SELECT syntax supported by HA3. For advanced usage, see the related topics.

Supported versions

>= Ha3 3.7.0

Syntax format

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 . *

Examples

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;

CASE WHEN

Description

The CASE WHEN expression works like an if...else if...else statement. If a WHEN condition is met, it returns the value of the corresponding THEN expression. If no WHEN condition is met, it returns the value of the ELSE expression.

​

Supported versions

>= Ha3 3.7.5

​

Syntax format

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

Examples

1. Using CASE WHEN 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. Using CASE WHEN 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;

Notes

1. The data types of all values in the THEN and ELSE expressions must be the same.

2. CASE WHEN can only be used as a standalone expression. You cannot nest it within other expressions or user-defined functions (UDFs). The following is an example of unsupported syntax:

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. Do not use CASE WHEN with multi-value fields.

4. You must include an ELSE branch.