All Products
Search

This Product

    Dataphin:API SQL script editor guide

    Document Center

    Dataphin:API SQL script editor guide

    Last Updated:Mar 05, 2026

    This topic describes how to edit scripts for query and manipulation operations.

    Get/List (query operations)

    • Supported query patterns:

      • Supports single-table queries, table joins (JOIN), and nested queries (subqueries) within the same data source.

      • Supports Advanced SQL mode (MyBatis style).

      • Supports configuring parameters in the SELECT clause. For example: SELECT id_card, SUM(CASE WHEN id_card LIKE ${id_card} THEN 1 ELSE 0 END) AS proj_score FROM table WHERE c LIKE ${id_card} GROUP BY id_card.

    • Unsupported syntax and usage:

      • Multiple SQL query statements: Multiple SQL statements are not supported in a single API, such as SELECT ...; SELECT ...;.

      • Non-DQL statements: In Get and List APIs, non-DQL syntax such as INSERT, UPDATE, CREATE, and DELETE is not supported.

      • SELECT *: The SELECT * query is not supported. Explicitly specify the columns to query.

      • Column prefixes: If a column name includes a table prefix, such as t.name, use an alias as the returned parameter name. For example: SELECT t.name AS name FROM table.

      • Aggregate functions: When using aggregate functions such as SUM(), COUNT(), MAX(), and MIN(), use an alias as the returned parameter name. For example: SELECT SUM(num) AS total_num FROM table.

      • Paging statements: Paging statements are not supported in SQL. Use paging parameters to implement paging.

        Note

        • If the data source is SAP HANA, use a paging statement in the SQL and define paging parameters to implement paging.

        • If the data source is TDengine, use a paging statement in the SQL. If you do not pass paging parameters (PageStart and PageSize), full data is returned. If you pass paging parameters, data is returned based on the defined parameters.

    • Dynamic field query (Advanced SQL mode): Dynamically specify returned fields using parameters in the query statement.

      • Parameter format: The parameter must start with var_cols, in the format var_cols_xxx, such as var_cols_args. For example: SELECT id, ${var_cols_args} FROM table1.

      • Returned parameters: Add all supported dynamic query fields as returned parameters.

      • API calls: When calling the API, pass the fields to query as dynamic parameters. Fields that are not passed have a null value in the result. For example: var_cols_args=name,age,dept.

      • Actual execution: The SQL statement is: SELECT id, name, age, dept FROM table1.

    • When using Advanced SQL mode, use escape characters for comparison operators in MyBatis SQL.

      Original symbol

      Escaped symbol

      <

      &lt;

      >

      &gt;

      <=

      &lt;=

      >=

      &gt;=

    • Data source-specific limitations:

      Data Source

      Limitation

      TDengine

      • Use LIMIT/OFFSET in SQL to implement paging.

      • Optional parameters are not supported.

      SAP HANA

      • Paging is not supported. The PageStart and PageSize parameters have no effect.

      • Optional parameters are not supported.

      Elasticsearch

      • Use ScrollId to implement paged queries. If you do not specify a ScrollId, you can query a maximum of 10,000 data entries. When setting the PageStart and PageSize parameters, do not query more than 10,000 data entries. Otherwise, the call fails.

        For example, if you set PageStart to 9998, the maximum value for PageSize is 2.

      • To query data beyond the first 10,000 entries, use a WHERE clause to specify the scrollId. You can specify a scrollId request parameter during API development and pass the corresponding scrollId value in the query.

        Note

        When you query using a specified scrollId value, you cannot use paging. Do not pass values for PageStart and PageSize. Otherwise, the call fails.

      • In a WHERE clause, only the scrollId field is supported for conditional queries. Other fields have no effect.

        For example: select a from table where scrollId=${scrollId}.

      • Subquery statements are not supported.

      HBase

      • Only single rowkey queries are supported, where `rowkey` is a required request parameter, with the following syntax:

        select info1.id as name from table where rowkey = ${rowkey}

      • The ORDER BY sorting syntax is not supported.

      • Paging is not supported. The PageStart and PageSize parameters have no effect.

      • The JOIN statement is not supported.

      PostgreSQL

      Cross-schema queries are not supported. You can only query data within the schema of the current connection.

      Impala

      Advanced SQL mode (MyBatis style) is not supported.

      Lindorm

      The JOIN statement is not supported.

      DolphinDB

      Only the SQL compatibility modes for Oracle and MySQL are supported. The native DolphinDB mode is not supported. When you create a data source, set the SQL compatibility mode to Oracle or MySQL to use it for API creation.

      Important

      Modifying the compatibility mode of a data source may cause unpredictable errors in existing APIs. Adjust the settings as needed for your business scenario.

      GBase 8a

      The QUALIFY statement is not supported in basic SQL mode.

      OpenSearch

      • Simple subqueries are supported. Multilayer nested IN subqueries may not execute correctly.

        For example: SELECT ... WHERE age IN (SELECT age FROM ... WHERE age IN (18,39));.

      • When you query using Java Database Connectivity (JDBC), a JOIN in a subquery may result in an empty result set.

        For example: SELECT ... FROM (SELECT ... FROM t1 LEFT JOIN t2 ON ...) e1 WHERE ...;.

      • The WITH syntax for common table expressions (CTEs) is not supported.

        For example: WITH test_with AS (...) SELECT ...;.

      • UNION and UNION ALL statements are not supported, even within subqueries.

        For example: SELECT id FROM (SELECT id FROM t1 UNION SELECT id FROM t2) t;.

      • The COALESCE syntax has compatibility issues with the current JDBC driver. Use the CASE WHEN syntax instead.

        Replace SELECT COALESCE(id, 0) FROM ...; with SELECT CASE WHEN id IS NULL THEN 0 ELSE id END FROM ...;.

      • Using a CASE WHEN expression with a JOIN may cause field parsing to fail or columns to not be found. Before you build complex queries, verify syntax compatibility.

        For example: SELECT CASE WHEN a.id=... END FROM t1 a JOIN t2 b ON ……;.

    Create/Update/Delete (manipulation operations)

    • Supported SQL modes:

      • Basic SQL mode: Supports standard INSERT, UPDATE, and DELETE statements. Use the ${param} placeholder to pass parameters. This mode is suitable for simple scenarios.

      • Advanced SQL mode (MyBatis style): Use this mode for complex logic such as conditional judgments, dynamic fields, and batch operations. The following tags are supported:

        <if>, <choose>, <when>, <otherwise>, <trim>, <foreach>

    • When you use Advanced SQL mode for batch processing, each data entry is executed separately, which results in poor performance. Avoid using Advanced SQL mode in batch create, update, and delete APIs.

    • If an SQL statement contains an in conditional parameter, each data entry is executed separately, which results in poor performance. Avoid using in conditional parameters.

    • Only PostgreSQL supports the RETURNING syntax.