Functions
hash< ColumnOperatorInfo >	SqlUtil::cop_append (auto column, string arg)
	returns a ColumnOperatorInfo hash for the `"append"` operator with the given argument More...

hash< ColumnOperatorInfo >	SqlUtil::cop_as (auto column, string arg)
	returns a ColumnOperatorInfo hash for the `"as"` operator with the given argument More...

hash< ColumnOperatorInfo >	SqlUtil::cop_avg (auto column)
	returns a ColumnOperatorInfo hash for the `"avg"` operator; returns average column values More...

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) More...

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

hash< ColumnOperatorInfo >	SqlUtil::cop_count (auto column='')
	returns a ColumnOperatorInfo hash for the `"count"` operator; returns row counts More...

hash< ColumnOperatorInfo >	SqlUtil::cop_cume_dist ()
	Analytic/window function: relative rank of the current row. More...

hash< ColumnOperatorInfo >	SqlUtil::cop_dense_rank ()
	Analytic/window function: rank of the current row without gaps. More...

hash< ColumnOperatorInfo >	SqlUtil::cop_distinct (auto column)
	returns a ColumnOperatorInfo hash for the `"distinct"` operator with the given argument; returns distinct column values More...

hash< ColumnOperatorInfo >	SqlUtil::cop_divide (auto column1, auto column2)
	returns a ColumnOperatorInfo hash for the `"/"` operator with the given arguments More...

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

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

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

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

hash< ColumnOperatorInfo >	SqlUtil::cop_max (auto column)
	returns a ColumnOperatorInfo hash for the `"max"` operator; returns maximum column values More...

hash< ColumnOperatorInfo >	SqlUtil::cop_min (auto column)
	returns a ColumnOperatorInfo hash for the `"min"` operator; returns minimum column values More...

hash< ColumnOperatorInfo >	SqlUtil::cop_minus (auto column1, auto column2)
	returns a ColumnOperatorInfo hash for the `"-"` operator with the given arguments More...

hash< ColumnOperatorInfo >	SqlUtil::cop_multiply (auto column1, auto column2)
	returns a ColumnOperatorInfo hash for the `"*"` operator with the given arguments More...

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

hash< ColumnOperatorInfo >	SqlUtil::cop_over (auto column, string partitionby, string orderby)
	returns a ColumnOperatorInfo hash for the `"over"` clause More...

hash< ColumnOperatorInfo >	SqlUtil::cop_percent_rank ()
	Analytic/window function: relative rank of the current row. More...

hash< ColumnOperatorInfo >	SqlUtil::cop_plus (auto column1, auto column2)
	returns a ColumnOperatorInfo hash for the `"+"` operator with the given arguments More...

hash< ColumnOperatorInfo >	SqlUtil::cop_prepend (auto column, string arg)
	returns a ColumnOperatorInfo hash for the `"prepend"` operator with the given argument More...

hash< ColumnOperatorInfo >	SqlUtil::cop_rank ()
	Analytic/window function: rank of the current row with gaps. More...

hash< ColumnOperatorInfo >	SqlUtil::cop_row_number ()
	Analytic/window function: number of the current row within its partition, counting from 1. More...

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

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

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

hash< ColumnOperatorInfo >	SqlUtil::cop_sum (auto column)
	returns a ColumnOperatorInfo hash for the `"sum"` operator; returns the total sum of a numeric column. More...

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) More...

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

hash< ColumnOperatorInfo >	SqlUtil::cop_value (auto arg)
	returns a ColumnOperatorInfo hash for the `"value"` (literal) operator with the given argument More...

hash< ColumnOperatorInfo >	SqlUtil::cop_year (auto column)
	returns a ColumnOperatorInfo hash for the `"year"` operator with the given argument More...

hash< ColumnOperatorInfo >	SqlUtil::cop_year_day (auto column)
	returns a ColumnOperatorInfo hash for the `"year_day"` operator with the given argument More...

hash< ColumnOperatorInfo >	SqlUtil::cop_year_hour (auto column)
	returns a ColumnOperatorInfo hash for the `"year_hour"` operator with the given argument More...

hash< ColumnOperatorInfo >	SqlUtil::cop_year_month (auto column)
	returns a ColumnOperatorInfo hash for the `"year_month"` operator with the given argument More...

hash< ColumnOperatorInfo >	SqlUtil::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:

cop_append(): append a string to the output of a column
cop_as(): rename columns
cop_avg(): return the averge value for the given column when grouping
cop_cast(): convert column value into another datatype
cop_coalesce(): returns the first non-NULL value in the given columns
cop_distinct(): add DISTINCT to the column name
cop_length(): return the length of the given text column
cop_lower(): return a column in lower case
cop_max(): return the maximum value for the given column when grouping
cop_min(): return the minimum value for the given column when grouping
cop_over(): return a hash for the "over" operator for windowing functions
cop_prepend(): prepend a string to the output of a column
cop_seq(): increments the sequence and returns the next value
cop_seq_currval(): returns the current value of the given sequence without changing the sequence
cop_substr(): returns a substring of a text column
cop_sum(): return the sum of all values for the given column when grouping
cop_upper(): return a column in upper case
cop_value(): return a constant value (SQL literal) in a column
cop_year(): return the year of the given date column
cop_year_day(): return the year to day value of the given date column
cop_year_hour(): return the year to hour value of the given date column
cop_year_month(): return the year and month value of the given date column
cop_trunc_date(): return truncated date regarding the mask
cop_cume_dist(): analytic/window function
cop_dense_rank(): analytic/window function
cop_first_value(): analytic/window function
cop_last_value(): analytic/window function
cop_ntile(): analytic/window function
cop_percent_rank(): analytic/window function
cop_rank(): analytic/window function
cop_row_number(): analytic/window function

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

column	the column specification for the column (string name or dot notation for use in joins)
arg	the 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

column	the column specification for the column (string name or dot notation for use in joins) or any other column "cop_..." function
arg	the 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

column the 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

column	the column specification for the column (string name or dot notation for use in joins) or any other column "cop_..." function
arg	the new datatype to cast the column value(s) to
arg1	optional, type dependent, specification (e.g. size or precision)
arg2	optional, 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

col1	the name or column operator hash for the first value
col2	the 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-ERROR the 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

column the 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

column1	the column specification for the first argument (string name or dot notation for use in joins)
column2	the 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

column the 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

column the 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

column the 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

column the 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

column1	the column specification for the first argument (string name or dot notation for use in joins)
column2	the 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

column1	the column specification for the first argument (string name or dot notation for use in joins)
column2	the 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

value an 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

column1	the column specification for the first argument (string name or dot notation for use in joins)
column2	the 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

column	the column specification for the column (string name or dot notation for use in joins)
arg	the 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

seq	the name of the sequence whose value should be returned
as	an 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

seq	the name of the sequence whose current value should be returned
as	an 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

column	the column specification for the column (string name or dot notation for use in joins)
start	position where the substring starts
count	length 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

column the 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

column	the column specification for the column (string name or dot notation for use in joins)
mask	the 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

column the 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

arg	the 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

column the 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

column the 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

column the 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

column the 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

cop	the column operator (one of SQL Column Operators)
column	the column name
arg	the 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

Functions

Detailed Description

Function Documentation

◆ cop_append()

◆ cop_as()

◆ cop_avg()

◆ cop_cast()

◆ cop_coalesce()

◆ cop_count()

◆ cop_cume_dist()

◆ cop_dense_rank()

◆ cop_distinct()

◆ cop_divide()

◆ cop_first_value()

◆ cop_last_value()

◆ cop_length()

◆ cop_lower()

◆ cop_max()

◆ cop_min()

◆ cop_minus()

◆ cop_multiply()

◆ cop_ntile()

◆ cop_over()

◆ cop_percent_rank()

◆ cop_plus()

◆ cop_prepend()

◆ cop_rank()

◆ cop_row_number()

◆ cop_seq()

◆ cop_seq_currval()

◆ cop_substr()

◆ cop_sum()

◆ cop_trunc_date()

◆ cop_upper()

◆ cop_value()

◆ cop_year()

◆ cop_year_day()

◆ cop_year_hour()

◆ cop_year_month()

◆ make_cop()