Qore SqlUtil Module Reference  1.5.2
SQL Column Operator Functions

Functions

hash< ColumnOperatorInfoSqlUtil::cop_append (auto column, string arg)
 returns a ColumnOperatorInfo hash for the "append" operator with the given argument More...
 
hash< ColumnOperatorInfoSqlUtil::cop_as (auto column, string arg)
 returns a ColumnOperatorInfo hash for the "as" operator with the given argument More...
 
hash< ColumnOperatorInfoSqlUtil::cop_avg (auto column)
 returns a ColumnOperatorInfo hash for the "avg" operator; returns average column values More...
 
hash< ColumnOperatorInfoSqlUtil::cop_cast (auto column, string arg, auto arg1, auto arg2)
 returns a ColumnOperatorInfo hash for the "cast" operator with the given argument(s) More...
 
hash< ColumnOperatorInfoSqlUtil::cop_coalesce (auto col1, auto col2)
 returns a ColumnOperatorInfo hash for the "coalesce" operator with the given column arguments; the first non-NULL column value will be returned More...
 
hash< ColumnOperatorInfoSqlUtil::cop_count (auto column='')
 returns a ColumnOperatorInfo hash for the "count" operator; returns row counts More...
 
hash< ColumnOperatorInfoSqlUtil::cop_cume_dist ()
 Analytic/window function: relative rank of the current row. More...
 
hash< ColumnOperatorInfoSqlUtil::cop_dense_rank ()
 Analytic/window function: rank of the current row without gaps. More...
 
hash< ColumnOperatorInfoSqlUtil::cop_distinct (auto column)
 returns a ColumnOperatorInfo hash for the "distinct" operator with the given argument; returns distinct column values More...
 
hash< ColumnOperatorInfoSqlUtil::cop_divide (auto column1, auto column2)
 returns a ColumnOperatorInfo hash for the "/" operator with the given arguments More...
 
hash< ColumnOperatorInfoSqlUtil::cop_first_value (any column)
 Analytic/window function: value evaluated at the row that is the first row of the window frame. More...
 
hash< ColumnOperatorInfoSqlUtil::cop_last_value (any column)
 Analytic/window function: value evaluated at the row that is the last row of the window frame. More...
 
hash< ColumnOperatorInfoSqlUtil::cop_length (auto column)
 returns a ColumnOperatorInfo hash for the "len" operator with the given argument; returns the length of the given text field More...
 
hash< ColumnOperatorInfoSqlUtil::cop_lower (auto column)
 returns a ColumnOperatorInfo hash for the "lower" operator with the given argument; returns a column value in lower case More...
 
hash< ColumnOperatorInfoSqlUtil::cop_max (auto column)
 returns a ColumnOperatorInfo hash for the "max" operator; returns maximum column values More...
 
hash< ColumnOperatorInfoSqlUtil::cop_min (auto column)
 returns a ColumnOperatorInfo hash for the "min" operator; returns minimum column values More...
 
hash< ColumnOperatorInfoSqlUtil::cop_minus (auto column1, auto column2)
 returns a ColumnOperatorInfo hash for the "-" operator with the given arguments More...
 
hash< ColumnOperatorInfoSqlUtil::cop_multiply (auto column1, auto column2)
 returns a ColumnOperatorInfo hash for the "*" operator with the given arguments More...
 
hash< ColumnOperatorInfoSqlUtil::cop_ntile (int value)
 Analytic/window function: integer ranging from 1 to the argument value, dividing the partition as equally as possible. More...
 
hash< ColumnOperatorInfoSqlUtil::cop_over (auto column, *string partitionby, *string orderby)
 returns a ColumnOperatorInfo hash for the "over" clause More...
 
hash< ColumnOperatorInfoSqlUtil::cop_percent_rank ()
 Analytic/window function: relative rank of the current row. More...
 
hash< ColumnOperatorInfoSqlUtil::cop_plus (auto column1, auto column2)
 returns a ColumnOperatorInfo hash for the "+" operator with the given arguments More...
 
hash< ColumnOperatorInfoSqlUtil::cop_prepend (auto column, string arg)
 returns a ColumnOperatorInfo hash for the "prepend" operator with the given argument More...
 
hash< ColumnOperatorInfoSqlUtil::cop_rank ()
 Analytic/window function: rank of the current row with gaps. More...
 
hash< ColumnOperatorInfoSqlUtil::cop_row_number ()
 Analytic/window function: number of the current row within its partition, counting from 1. More...
 
hash< ColumnOperatorInfoSqlUtil::cop_seq (string seq, *string as)
 returns a ColumnOperatorInfo hash for the "seq" operator with the given argument giving the sequence name whose value should be returned More...
 
hash< ColumnOperatorInfoSqlUtil::cop_seq_currval (string seq, *string as)
 returns a ColumnOperatorInfo hash for the "seq_currval" operator with the given argument giving the sequence name whose current value should be returned More...
 
hash< ColumnOperatorInfoSqlUtil::cop_substr (auto column, int start, *int count)
 returns a ColumnOperatorInfo hash for the "substr" operator with the given arguments; returns a substring of a column value More...
 
hash< ColumnOperatorInfoSqlUtil::cop_sum (auto column)
 returns a ColumnOperatorInfo hash for the "sum" operator; returns the total sum of a numeric column. More...
 
hash< ColumnOperatorInfoSqlUtil::cop_trunc_date (auto column, string mask)
 Truncates a date column or value regarding the given mask. The resulting value remains Qore::date (no conversion to eg. string) More...
 
hash< ColumnOperatorInfoSqlUtil::cop_upper (auto column)
 returns a ColumnOperatorInfo hash for the "upper" operator with the given argument; returns a column value in upper case More...
 
hash< ColumnOperatorInfoSqlUtil::cop_value (auto arg)
 returns a ColumnOperatorInfo hash for the "value" (literal) operator with the given argument More...
 
hash< ColumnOperatorInfoSqlUtil::cop_year (auto column)
 returns a ColumnOperatorInfo hash for the "year" operator with the given argument More...
 
hash< ColumnOperatorInfoSqlUtil::cop_year_day (auto column)
 returns a ColumnOperatorInfo hash for the "year_day" operator with the given argument More...
 
hash< ColumnOperatorInfoSqlUtil::cop_year_hour (auto column)
 returns a ColumnOperatorInfo hash for the "year_hour" operator with the given argument More...
 
hash< ColumnOperatorInfoSqlUtil::cop_year_month (auto column)
 returns a ColumnOperatorInfo hash for the "year_month" operator with the given argument More...
 
hash< ColumnOperatorInfoSqlUtil::make_cop (string cop, auto column, auto arg)
 returns a ColumnOperatorInfo hash More...
 

Detailed Description

These are functions that can be used in the "columns" argument for select statements:

Column operator functions can be nested as in the following example:

Example:
*list<auto> rows = t.selectRows(("columns": cop_as(cop_lower("permission_type"), "perm"), "where": ("permission_type": "USER"), "limit": 100, "offset": 200));

Function Documentation

◆ cop_append()

hash<ColumnOperatorInfo> SqlUtil::cop_append ( auto  column,
string  arg 
)

returns a ColumnOperatorInfo hash for the "append" operator with the given argument

Example:
*list<auto> rows = table.selectRows(("columns": ("id", cop_append("name", "-migrated")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
argthe text to append (ie concatenate) to the row values in the output column
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_as()

hash<ColumnOperatorInfo> SqlUtil::cop_as ( auto  column,
string  arg 
)

returns a ColumnOperatorInfo hash for the "as" operator with the given argument

Example:
*list<auto> rows = table.selectRows(("columns": ("id", "name", cop_as("errors", "error_count")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins) or any other column "cop_..." function
argthe new name of the output column
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash
See also
SqlUtil::cop_value for SQL literals handling

◆ cop_avg()

hash<ColumnOperatorInfo> SqlUtil::cop_avg ( auto  column)

returns a ColumnOperatorInfo hash for the "avg" operator; returns average column values

Example:
*list<auto> rows = table.selectRows(("columns": (cop_avg("quantity")), "where": ("order_type": "wholesale")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_cast()

hash<ColumnOperatorInfo> SqlUtil::cop_cast ( auto  column,
string  arg,
auto  arg1,
auto  arg2 
)

returns a ColumnOperatorInfo hash for the "cast" operator with the given argument(s)

Example:
*list<auto> rows = table.selectRows(("columns": ("id", cop_cast("id", "string")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins) or any other column "cop_..." function
argthe new datatype to cast the column value(s) to
arg1optional, type dependent, specification (e.g. size or precision)
arg2optional, type dependent, specification (e.g. scale)
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash
See also
SqlUtil::cop_value for SQL literals handling

◆ cop_coalesce()

hash<ColumnOperatorInfo> SqlUtil::cop_coalesce ( auto  col1,
auto  col2 
)

returns a ColumnOperatorInfo hash for the "coalesce" operator with the given column arguments; the first non-NULL column value will be returned

Example:
*list<auto> rows = table.selectRows(("columns": ("first_name", "last_name", cop_colesce("first_name", "last_name")), "where": ("type": "user")));
Parameters
col1the name or column operator hash for the first value
col2the name or column operator hash for the second value, additional values should follow this argument
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash
Exceptions
COALESCE-ERRORthe arguments must be either string column designators or column operator hashes

◆ cop_count()

hash<ColumnOperatorInfo> SqlUtil::cop_count ( auto  column = '')

returns a ColumnOperatorInfo hash for the "count" operator; returns row counts

Example:
*list<auto> rows = table.selectRows(("columns": ("account_type", cop_count()), "where": ("type": "user"), "groupby": "account_type"));
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_cume_dist()

hash<ColumnOperatorInfo> SqlUtil::cop_cume_dist ( )

Analytic/window function: relative rank of the current row.

Example:
# with table structure (Schema module syntax)
const T_TestAnalyticFunctions = (
"columns" : (
"id" : c_number(),
"row_type" : c_varchar(10),
"row_value" : c_number(),
),
);
# select hash
hash<auto> sh = (
"columns" : cop_as(cop_over(cop_cume_dist(), "row_type", "id"), "cume_dist"),
);
# rendered SQL statement
# select cume_dist() over (partition by row_type order by id) as "cume_dist" test_analytic_functions;

Analytic/window function. Must be used with cop_over() with partitionby and orderby arguments

Note
MySQL DB family: This analytic function is available only in MariaDB 10.2 and later only.
Returns
relative rank of the current row: (number of rows preceding or peer with current row) / (total rows)
Since
SqlUtil 1.4.0

◆ cop_dense_rank()

hash<ColumnOperatorInfo> SqlUtil::cop_dense_rank ( )

Analytic/window function: rank of the current row without gaps.

Example:
# with table structure (Schema module syntax)
const T_TestAnalyticFunctions = (
"columns" : (
"id" : c_number(),
"row_type" : c_varchar(10),
"row_value" : c_number(),
),
);
# select hash
hash<auto> sh = (
"columns" : cop_as(cop_over(cop_dense_rank(), "row_type", "id"), "dense_rank"),
);
# rendered SQL statement
select dense_rank() over (partition by row_type order by id) as "dense_rank" from test_analytic_functions;

Analytic/window function. Must be used with cop_over() with partitionby and orderby arguments

Note
MySQL DB family: This analytic function is available only in MariaDB 10.2 and later only.
Returns
rank of the current row without gaps; this function counts peer groups
Since
SqlUtil 1.4.0

◆ cop_distinct()

hash<ColumnOperatorInfo> SqlUtil::cop_distinct ( auto  column)

returns a ColumnOperatorInfo hash for the "distinct" operator with the given argument; returns distinct column values

Example:
*list<auto> rows = table.selectRows(("columns": ("id", cop_distinct("name")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_divide()

hash<ColumnOperatorInfo> SqlUtil::cop_divide ( auto  column1,
auto  column2 
)

returns a ColumnOperatorInfo hash for the "/" operator with the given arguments

Example:
*list<auto> rows = table.selectRows(("columns": ("id", "name", cop_divide("complete_count", "error_count")), "where": ("type": "user")));
Parameters
column1the column specification for the first argument (string name or dot notation for use in joins)
column2the column specification for the second argument (string name or dot notation for use in joins)
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_first_value()

hash<ColumnOperatorInfo> SqlUtil::cop_first_value ( any  column)

Analytic/window function: value evaluated at the row that is the first row of the window frame.

Example:
# with table structure (Schema module syntax)
const T_TestAnalyticFunctions = (
"columns" : (
"id" : c_number(),
"row_type" : c_varchar(10),
"row_value" : c_number(),
),
);
# select hash
hash<auto> sh = (
"columns" : cop_as(cop_over(cop_first_value("row_value"), "row_type", "id"), "first_value"),
);
# rendered SQL statement
select first_value(row_value) over (partition by row_type order by id) as "first_value" from test_analytic_functions;

Analytic/window function. Must be used with cop_over() with partitionby and orderby arguments

Note
MySQL DB family: This analytic function is available only in MariaDB 10.2 and later only.
Returns
returns value evaluated at the row that is the first row of the window frame
Since
SqlUtil 1.4.0

◆ cop_last_value()

hash<ColumnOperatorInfo> SqlUtil::cop_last_value ( any  column)

Analytic/window function: value evaluated at the row that is the last row of the window frame.

Example:
# with table structure (Schema module syntax)
const T_TestAnalyticFunctions = (
"columns" : (
"id" : c_number(),
"row_type" : c_varchar(10),
"row_value" : c_number(),
),
);
# select hash
hash<auto> sh = (
"columns" : cop_as(cop_over(cop_last_value("row_value"), "row_type", "id"), "last_value"),
);
# rendered SQL statement
select last_value(row_value) over (partition by row_type order by id) as "last_value" from test_analytic_functions;

Analytic/window function. Must be used with cop_over() with partitionby and orderby arguments

Note
MySQL DB family: This analytic function is available only in MariaDB 10.2 and later only.
Returns
returns value evaluated at the row that is the last row of the window frame
Since
SqlUtil 1.4.0

◆ cop_length()

hash<ColumnOperatorInfo> SqlUtil::cop_length ( auto  column)

returns a ColumnOperatorInfo hash for the "len" operator with the given argument; returns the length of the given text field

Example:
*list<auto> rows = table.selectRows(("columns": ("id", cop_length("product_code")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash
Since
SqlUtil 1.3.1

◆ cop_lower()

hash<ColumnOperatorInfo> SqlUtil::cop_lower ( auto  column)

returns a ColumnOperatorInfo hash for the "lower" operator with the given argument; returns a column value in lower case

Example:
*list<auto> rows = table.selectRows(("columns": ("id", cop_lower("name")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_max()

hash<ColumnOperatorInfo> SqlUtil::cop_max ( auto  column)

returns a ColumnOperatorInfo hash for the "max" operator; returns maximum column values

Example:
*list<auto> rows = table.selectRows(("columns": (cop_max("id")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_min()

hash<ColumnOperatorInfo> SqlUtil::cop_min ( auto  column)

returns a ColumnOperatorInfo hash for the "min" operator; returns minimum column values

Example:
*list<auto> rows = table.selectRows(("columns": (cop_min("id")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_minus()

hash<ColumnOperatorInfo> SqlUtil::cop_minus ( auto  column1,
auto  column2 
)

returns a ColumnOperatorInfo hash for the "-" operator with the given arguments

Example:
*list<auto> rows = table.selectRows(("columns": ("id", "name", cop_minus("complete_count", "error_count")), "where": ("type": "user")));
Parameters
column1the column specification for the first argument (string name or dot notation for use in joins)
column2the column specification for the second argument (string name or dot notation for use in joins)
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_multiply()

hash<ColumnOperatorInfo> SqlUtil::cop_multiply ( auto  column1,
auto  column2 
)

returns a ColumnOperatorInfo hash for the "*" operator with the given arguments

Example:
*list<auto> rows = table.selectRows(("columns": ("id", "name", cop_multiply("complete_count", "error_count")), "where": ("type": "user")));
Parameters
column1the column specification for the first argument (string name or dot notation for use in joins)
column2the column specification for the second argument (string name or dot notation for use in joins)
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_ntile()

hash<ColumnOperatorInfo> SqlUtil::cop_ntile ( int  value)

Analytic/window function: integer ranging from 1 to the argument value, dividing the partition as equally as possible.

Example:
# with table structure (Schema module syntax)
const T_TestAnalyticFunctions = (
"columns" : (
"id" : c_number(),
"row_type" : c_varchar(10),
"row_value" : c_number(),
),
);
# select hash
hash<auto> sh = (
"columns" : cop_as(cop_over(cop_ntile(10), "row_type", "id"), "ntile"),
);
# rendered SQL statement
select ntile(10) over (partition by row_type order by id) as "ntile" from test_analytic_functions;

Analytic/window function. Must be used with cop_over() with partitionby and orderby arguments

Note
MySQL DB family: This analytic function is available only in MariaDB 10.2 and later only.
Parameters
valuean integer value used as count of sp;it buckets
Returns
integer ranging from 1 to the argument value, dividing the partition as equally as possible
Since
SqlUtil 1.4.0

◆ cop_over()

hash<ColumnOperatorInfo> SqlUtil::cop_over ( auto  column,
*string  partitionby,
*string  orderby 
)

returns a ColumnOperatorInfo hash for the "over" clause

Example:
*list<auto> rows = table.selectRows(("columns": ("account_type", cop_count(), cop_as(cop_over(cop_max("qty"), "account_id"), "max_qty_per_account")), "where": ("type": "user"), "groupby": "account_type"));
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_percent_rank()

hash<ColumnOperatorInfo> SqlUtil::cop_percent_rank ( )

Analytic/window function: relative rank of the current row.

Example:
# with table structure (Schema module syntax)
const T_TestAnalyticFunctions = (
"columns" : (
"id" : c_number(),
"row_type" : c_varchar(10),
"row_value" : c_number(),
),
);
# select hash
hash<auto> sh = (
"columns" : cop_as(cop_over(cop_percent_rank(), "row_type", "id"), "percent_rank"),
);
# rendered SQL statement
select percent_rank() over (partition by row_type order by id) as "percent_rank" from test_analytic_functions;

Analytic/window function. Must be used with cop_over() with partitionby and orderby arguments

Note
MySQL DB family: This analytic function is available only in MariaDB 10.2 and later only.
Returns
relative rank of the current row: (rank - 1) / (total rows - 1)
Since
SqlUtil 1.4.0

◆ cop_plus()

hash<ColumnOperatorInfo> SqlUtil::cop_plus ( auto  column1,
auto  column2 
)

returns a ColumnOperatorInfo hash for the "+" operator with the given arguments

Example:
*list<auto> rows = table.selectRows(("columns": ("id", "name", cop_plus("complete_count", "error_count")), "where": ("type": "user")));
Parameters
column1the column specification for the first argument (string name or dot notation for use in joins)
column2the column specification for the second argument (string name or dot notation for use in joins)
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_prepend()

hash<ColumnOperatorInfo> SqlUtil::cop_prepend ( auto  column,
string  arg 
)

returns a ColumnOperatorInfo hash for the "prepend" operator with the given argument

Example:
*list<auto> rows = table.selectRows(("columns": ("id", cop_prepend("name", "migrated-")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
argthe text to prepend to the row values in the output column
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_rank()

hash<ColumnOperatorInfo> SqlUtil::cop_rank ( )

Analytic/window function: rank of the current row with gaps.

Example:
# with table structure (Schema module syntax)
const T_TestAnalyticFunctions = (
"columns" : (
"id" : c_number(),
"row_type" : c_varchar(10),
"row_value" : c_number(),
),
);
# select hash
hash<auto> sh = (
"columns" : cop_as(cop_over(cop_rank(), "row_type", "id"), "rank"),
);
# rendered SQL statement
select rank() over (partition by row_type order by id) as "rank" from test_analytic_functions;

Analytic/window function. Must be used with cop_over() with partitionby and orderby arguments

Note
MySQL DB family: This analytic function is available only in MariaDB 10.2 and later only.
Returns
rank of the current row with gaps; same as row_number of its first peer
Since
SqlUtil 1.4.0

◆ cop_row_number()

hash<ColumnOperatorInfo> SqlUtil::cop_row_number ( )

Analytic/window function: number of the current row within its partition, counting from 1.

Example:
# with table structure (Schema module syntax)
const T_TestAnalyticFunctions = (
"columns" : (
"id" : c_number(),
"row_type" : c_varchar(10),
"row_value" : c_number(),
),
);
# select hash
hash<auto> sh = (
"columns" : cop_as(cop_over(cop_row_number(), "row_type", "id"), "row_number"),
);
# rendered SQL statement
select row_number() over (partition by row_type order by id) as "row_number" from test_analytic_functions;

Analytic/window function. Must be used with cop_over() with partitionby and orderby arguments

Note
MySQL DB family: This analytic function is available only in MariaDB 10.2 and later only.
Returns
number of the current row within its partition, counting from 1
Since
SqlUtil 1.4.0

◆ cop_seq()

hash<ColumnOperatorInfo> SqlUtil::cop_seq ( string  seq,
*string  as 
)

returns a ColumnOperatorInfo hash for the "seq" operator with the given argument giving the sequence name whose value should be returned

Example:
*list<auto> rows = table.selectRows(("columns": ("id", "name", cop_seq("xid", "xis")), "where": ("type": "user")));
Parameters
seqthe name of the sequence whose value should be returned
asan optional column name that should be returned for the sequence value (so that SqlUtil::cop_as() need not be used)
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_seq_currval()

hash<ColumnOperatorInfo> SqlUtil::cop_seq_currval ( string  seq,
*string  as 
)

returns a ColumnOperatorInfo hash for the "seq_currval" operator with the given argument giving the sequence name whose current value should be returned

Example:
*list<auto> rows = table.selectRows(("columns": ("id", "name", cop_seq("xid", "xid_val"), cop_seq_currval("xid", "xid_current")), "where": ("type": "user")));
Parameters
seqthe name of the sequence whose current value should be returned
asan optional column name that should be returned for the sequence value (so that SqlUtil::cop_as() need not be used)
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_substr()

hash<ColumnOperatorInfo> SqlUtil::cop_substr ( auto  column,
int  start,
*int  count 
)

returns a ColumnOperatorInfo hash for the "substr" operator with the given arguments; returns a substring of a column value

Example:
*list<auto> rows = table.selectRows(("columns": ("id", cop_substr("name", 1, 1)), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
startposition where the substring starts
countlength of the substring in characters
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_sum()

hash<ColumnOperatorInfo> SqlUtil::cop_sum ( auto  column)

returns a ColumnOperatorInfo hash for the "sum" operator; returns the total sum of a numeric column.

Example:
*list<auto> rows = table.selectRows(("columns": (cop_sum("quantity")), "where": ("order_type": "wholesale")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_trunc_date()

hash<ColumnOperatorInfo> SqlUtil::cop_trunc_date ( auto  column,
string  mask 
)

Truncates a date column or value regarding the given mask. The resulting value remains Qore::date (no conversion to eg. string)

Example:
*list<auto> rows = table.selectRows(("columns": cop_trunc_date("mydate", DF_MINUTE));
# input: 2017-02-01 14:22:37
# output 2017-02-01 14:22:00
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
maskthe string with one of specified values rederenced in cop_trunc_date formats
Returns
a column operator description hash corresponding to the arguments for use in the columns argument of a select option hash
Since
SqlUtil 1.4.0

◆ cop_upper()

hash<ColumnOperatorInfo> SqlUtil::cop_upper ( auto  column)

returns a ColumnOperatorInfo hash for the "upper" operator with the given argument; returns a column value in upper case

Example:
*list<auto> rows = table.selectRows(("columns": ("id", cop_upper("name")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_value()

hash<ColumnOperatorInfo> SqlUtil::cop_value ( auto  arg)

returns a ColumnOperatorInfo hash for the "value" (literal) operator with the given argument

Example:
*list<auto> rows = table.selectRows(("columns": ("id", "name", cop_value(100)), "where": ("type": "user")));
Parameters
argthe value to be returned in the column
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

SQL literals can be useful in some cases - as dummy values for select statements where there is exact columns required, unions, expected values for arc.insertFromIterator(src.getStatement(sh)) "insert as select", etc.

The term literal refers to a fixed data value. For example, 123, 'foobar' etc.

Mapping of Qore values to literals:

Qore Type SQL Type Qore Example SQL interpretation
Integer NUMBER as it is 123 123
Float NUMBER as it is 12.3 12.3
Numeric NUMBER as it is 1.2n 1.2
Date String representation of the date using DB native implementation like TO_TIMESTAMP for Oracle. now() to_timestamp('20150421104825000000', 'YYYYMMDDHH24MISSFF6')
Bool Internal representation of the bool value using DB native implementation True 1
String Standard and escaped string literal. No additional literal methods like Oracle's nq{foobar} are supported now "foo bar" 'foo bar'
NULL Direct null literal NULL null
NOTHING Direct null literal NOTHING null
Note
Passing an existing SQL function name as a value to the cop_value() function does not result in function call. The string value is escaped as it is. Example: sysdate becomes 'sysdate'. See example below.

The most useful SqlUtil::cop_value() usage is with cooperation of SqlUtil::cop_as() which allows human readable column name aliases.

Warning
Using SqlUtil::cop_value() without SqlUtil::cop_as() can result in errors depending on the DB backend. For example Oracle's use of cop_value(1), cop_value(True) ends with ORA-00918: column ambiguously defined because both values are interpreted as 1 in the resulting SQL.

Example

%new-style
%requires SqlUtil
DatasourcePool ds("oracle:pvanek_omq/omq@xbox(al32utf8)");
Database db(ds);
Table t(ds, "dual");
hash<auto> sh = (
"columns" : (
cop_as(cop_value(1), "as_int"),
cop_as(cop_value(1.2), "as_float"),
cop_as(cop_value(3.2n), "as_numeric"),
cop_as(cop_value(now()), "as_date"),
cop_as(cop_value("foo bar"), "as_string"),
cop_as(cop_value("sysdate"), "as_function_call"),
cop_as(cop_value(True), "as_bool"),
cop_as(cop_value(NULL), "as_null"),
cop_as(cop_value(NOTHING), "as_nothing"),
),
);
string sql;
on_exit { printf("sql: %s\n", sql); }
printf("%N\n", t.selectRow(sh, \sql));
# results in:
hash: (9 members)
as_int : 1
as_float : 1.2
as_numeric : 3.2
as_date : 2015-04-21 10:56:57.000000 Tue +02:00 (CEST)
as_string : "foo bar"
as_function_call : "sysdate"
as_bool : 1
as_null : <NULL>
as_nothing : <NULL>
sql: select 1 as as_int,1.2 as as_float,3.2 as as_numeric,to_timestamp('20150421105657000000', 'YYYYMMDDHH24MISSFF6') as as_date,'foo bar' as as_string,'sysdate' as as_function_call,1 as as_bool,null as as_null,null as as_nothing from pvanek_omq.workflows fetch next %v rows only

◆ cop_year()

hash<ColumnOperatorInfo> SqlUtil::cop_year ( auto  column)

returns a ColumnOperatorInfo hash for the "year" operator with the given argument

Example:
*list<auto> rows = table.selectRows(("columns": ("id", "name", cop_year("error_time")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_year_day()

hash<ColumnOperatorInfo> SqlUtil::cop_year_day ( auto  column)

returns a ColumnOperatorInfo hash for the "year_day" operator with the given argument

Example:
*list<auto> rows = table.selectRows(("columns": ("id", "name", cop_year_day("error_time")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_year_hour()

hash<ColumnOperatorInfo> SqlUtil::cop_year_hour ( auto  column)

returns a ColumnOperatorInfo hash for the "year_hour" operator with the given argument

Example:
*list<auto> rows = table.selectRows(("columns": ("id", "name", cop_year_hour("error_time")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ cop_year_month()

hash<ColumnOperatorInfo> SqlUtil::cop_year_month ( auto  column)

returns a ColumnOperatorInfo hash for the "year_month" operator with the given argument

Example:
*list<auto> rows = table.selectRows(("columns": ("id", "name", cop_year_month("error_time")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

◆ make_cop()

hash<ColumnOperatorInfo> SqlUtil::make_cop ( string  cop,
auto  column,
auto  arg 
)

returns a ColumnOperatorInfo hash

Parameters
copthe column operator (one of SQL Column Operators)
columnthe column name
argthe argument to the operator
Returns
a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash
Note
Normally this function is not called directly, but rather by the other column operator functions