SQL Column Operator Functions

Functions
hash	SqlUtil::cop_append (any column, string arg)
	returns a hash for the "append" operator with the given argument More...
hash	SqlUtil::cop_as (any column, string arg)
	returns a hash for the "as" operator with the given argument More...
hash	SqlUtil::cop_avg (any column)
	returns a hash for the "avg" operator; returns average column values More...
hash	SqlUtil::cop_count (string column="")
	returns a hash for the "count" operator; returns row counts More...
hash	SqlUtil::cop_distinct (any column)
	returns a hash for the "distinct" operator with the given argument; returns distinct column values More...
hash	SqlUtil::cop_divide (any column1, any column2)
	returns a hash for the "/" operator with the given arguments More...
hash	SqlUtil::cop_lower (any column)
	returns a hash for the "lower" operator with the given argument; returns a column value in lower case More...
hash	SqlUtil::cop_max (any column)
	returns a hash for the "max" operator; returns maximum column values More...
hash	SqlUtil::cop_min (any column)
	returns a hash for the "min" operator; returns minimum column values More...
hash	SqlUtil::cop_minus (any column1, any column2)
	returns a hash for the "-" operator with the given arguments More...
hash	SqlUtil::cop_multiply (any column1, any column2)
	returns a hash for the "*" operator with the given arguments More...
hash	SqlUtil::cop_over (any column, string arg)
	returns a hash for the "over" clause More...
hash	SqlUtil::cop_plus (any column1, any column2)
	returns a hash for the "+" operator with the given arguments More...
hash	SqlUtil::cop_prepend (any column, string arg)
	returns a hash for the "prepend" operator with the given argument More...
hash	SqlUtil::cop_seq (string seq)
	returns a hash for the "seq" operator with the given argument giving the sequence name whose value should be returned More...
hash	SqlUtil::cop_upper (any column)
	returns a hash for the "upper" operator with the given argument; returns a column value in upper case More...
hash	SqlUtil::cop_value (any arg)
	returns a hash for the "value" operator with the given argument More...
hash	SqlUtil::cop_year (any column)
	returns a hash for the "year" operator with the given argument More...
hash	SqlUtil::cop_year_day (any column)
	returns a hash for the "year_day" operator with the given argument More...
hash	SqlUtil::cop_year_hour (any column)
	returns a hash for the "year_hour" operator with the given argument More...
hash	SqlUtil::cop_year_month (any column)
	returns a hash for the "year_month" operator with the given argument More...
hash	SqlUtil::make_cop (string cop, any column, any arg)
	returns a hash with cop, column, and arg keys More...

Detailed Description

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

• cop_as(): rename columns
• cop_prepend(): prepend a string to the output of a column
• cop_append(): append a string to the output of a column
• cop_value(): return a constant value in a column
• cop_upper(): return a column in upper case
• cop_lower(): return a column in lower case

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

Example:

my *hash $rows = t.selectRows(("columns": cop_as(cop_lower("permission_type"), "perm"), "where": ("permission_type": "USER"), "limit": 100, "offset": 200));

Function Documentation

hash SqlUtil::cop_append	(	any	column,
		string	arg
	)

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

Example:

my *list $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 column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_as	(	any	column,
		string	arg
	)

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

Example:

my *list $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)
arg	the new name of the output column

Returns

a column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_avg

(

any

column

)

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

Example:

my *list $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 column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_count

(

string

column = ""

)

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

Example:

my *list $rows = $table.selectRows(("columns": ("account_type", cop_count()), "where": ("type": "user"), "groupby": "account_type"));

Returns

a column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_distinct

(

any

column

)

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

Example:

my *list $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 column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_divide	(	any	column1,
		any	column2
	)

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

Example:

my *list $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)
arg	the new name of the output column

Returns

a column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_lower

(

any

column

)

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

Example:

my *list $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 column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_max

(

any

column

)

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

Example:

my *list $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 column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_min

(

any

column

)

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

Example:

my *list $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 column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_minus	(	any	column1,
		any	column2
	)

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

Example:

my *list $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)
arg	the new name of the output column

Returns

a column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_multiply	(	any	column1,
		any	column2
	)

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

Example:

my *list $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)
arg	the new name of the output column

Returns

a column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_over	(	any	column,
		string	arg
	)

returns a hash for the "over" clause

Example:

my *list $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 column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_plus	(	any	column1,
		any	column2
	)

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

Example:

my *list $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)
arg	the new name of the output column

Returns

a column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_prepend	(	any	column,
		string	arg
	)

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

Example:

my *list $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 column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_seq

(

string

seq

)

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

Example:

my *list $rows = $table.selectRows(("columns": ("id", "name", cop_seq("xid")), "where": ("type": "user")));

Parameters

seq	the name of the sequence whose value should be returned

Returns

a column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_upper

(

any

column

)

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

Example:

my *list $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 column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_value

(

any

arg

)

returns a hash for the "value" operator with the given argument

Example:

my *list $rows = $table.selectRows(("columns": ("id", "name", cop_value(100)), "where": ("type": "user")));

Parameters

arg	the value to be returned in the column

Returns

a column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_year

(

any

column

)

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

Example:

my *list $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 column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_year_day

(

any

column

)

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

Example:

my *list $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 column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_year_hour

(

any

column

)

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

Example:

my *list $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 column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::cop_year_month

(

any

column

)

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

Example:

my *list $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 column operator description hash corresponding to the arguments for use in the columns argument of a select option hash

hash SqlUtil::make_cop	(	string	cop,
		any	column,
		any	arg
	)

returns a hash with cop, column, and arg keys

Parameters

cop	the column operator (one of SQL Column Operators)
column	the column name
arg	the argument to the operator

Returns

a column operator description 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