sql#

swordfish.function.sql()#

Create a SQL statement dynamically. To execute the generated SQL statement, use function eval.

Parameters:

  • select (Constant) – A metacode indicating the columns to be selected. Each column is generated by either function sqlCol or sqlColAlias . Use a tuple to select multiple columns.

  • from (Constant) – A table object or table name.

  • where (Constant, optional) – Indicates the “where” conditions. In case of multiple “where” conditions, use an ANY vector with each element corresponding to the metacode of a condition, by default DFLT.

  • groupBy (Constant, optional) – Indicates “group by” or “context by” column(s). In case of multiple “group by” columns, use an ANY vector with each element corresponding to the metacode of a column name, by default DFLT.

  • groupFlag (Constant, optional) – 1 means “group by”; 0 means “context by”; 2 means “pivot by”, by default DFLT.

  • csort (Constant, optional) – A metacode or a tuple of metacode that specifies the column name(s) followed by csort, by default DFLT. This parameter only works when contextBy is specified.

  • ascSort (Constant, optional) – A scalar or vector indicating whether each csort column is sorted in ascending or descending order, by default DFLT. 1 (default) means ascending and 0 means descending.

  • having (Constant, optional) – A metacode or a tuple of metacode that specifies the having condition(s), by default DFLT. This parameter only works when contextBy is specified.

  • orderBy (Constant, optional) – Indicates “order by” column(s). In case of multiple “order by” columns, use a tuple with each element corresponding to the metacode of a column name, by default DFLT.

  • ascOrder (Constant, optional) – A scalar or vector indicating whether each “order by” column is sorted in ascending or descending order, by default DFLT. 1 means sorting in ascending order; 0 means sorting in descending order.

  • limit (Constant, optional) –

    An integer or an integral pair indicating the number of rows to select from the result starting from the first row, by default DFLT. If groupBy is specified and groupFlag=0, select limit rows from each group starting from the first row in each group. It corresponds to “top” clause in “select” statements.

    • If limit is a integer n, it returns the first n rows.

    • If limit is a pair “start:end”, it returns rows from the start-th row (inclusive) to the end-th row (exclusive).

    If groupBy is specified and groupFlag = 0, the limit is applied within each group independently.

  • hint (Constant, optional) –

    A constant that can take the following values:

    • HINT_HASH: use Hashing algorithm to execute “group by” statements.

    • HINT_SNAPSHOT: query data from snapshot engine.

    • HINT_KEEPORDER: the records in the result after executing “context by” statements are in the same order as in the input data.

  • exec (Constant, optional) –

    Indicates whether to use the exec clause, by default DFLT.

    If set to be true, a scalar or a vector will be generated. If the “pivot by” is used in the exec clause, a matrix can be generated.

  • map (Constant, optional) – A Boolean scalar specifying whether to use the map keyword, by default DFLT.

On this page