SQL Column Operator Functions

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

Detailed Description

These are static methods 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 methods
• 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 method
• cop_dense_rank(): analytic/window method
• cop_first_value(): analytic/window method
• cop_last_value(): analytic/window method
• cop_ntile(): analytic/window method
• cop_percent_rank(): analytic/window method
• cop_rank(): analytic/window method
• cop_row_number(): analytic/window method

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("permission_type", "USER");
    put("limit", 100);
    put("offset", 200);
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_as(AbstractTable.cop_lower("permission_type"), "perm"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

Function Documentation

cop_append()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_append ( Object column, String arg ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_append("name", "-migrated"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_as ( Object column, String arg ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("permission_type", "USER");
    put("limit", 100);
    put("offset", 200);
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_as(AbstractTable.cop_lower("permission_type"), "perm"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

Parameters

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

cop_value for SQL literals handling

cop_avg()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_avg ( Object column ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_avg("quantity"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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() [1/3]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_cast ( Object column, String arg ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> ch = new HashMap<String, Object>() {
    put("id", AbstractTable.cop_cast("id", "string"));
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", ch);
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

Parameters

column	the column specification for the column (String name or dot notation for use in joins) or any other column "cop_..." method
arg	the new datatype to cast the column value(s) to

Returns

a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

See also

cop_value for SQL literals handling

cop_cast() [2/3]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_cast ( Object column, String arg, Object arg1 ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> ch = new HashMap<String, Object>() {
    put("id", AbstractTable.cop_cast("id", "string"));
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", ch);
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

Parameters

column	the column specification for the column (String name or dot notation for use in joins) or any other column "cop_..." method
arg	the new datatype to cast the column value(s) to
arg1	optional, type dependent, specification (e.g. size or precision)

Returns

a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

See also

cop_value for SQL literals handling

cop_cast() [3/3]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_cast ( Object column, String arg, Object arg1, Object arg2 ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> ch = new HashMap<String, Object>() {
    put("id", AbstractTable.cop_cast("id", "string"));
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", ch);
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

Parameters

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

cop_value for SQL literals handling

cop_coalesce() [1/2]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_coalesce ( Object col1, Object col2 ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_colesce("first_name", "last_name"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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_coalesce() [2/2]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_coalesce ( Object col1, Object col2, Object... args ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_colesce("first_name", "last_name"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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
args	other column names or column operator hashes for subsequent values

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() [1/2]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_count ( ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_count());
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

Returns

a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

cop_count() [2/2]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_count ( Object column ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_count());
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

Returns

a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

cop_cume_dist()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_cume_dist ( ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_as(cop_over(cop_cume_dist(), "row_type", "id"), "cume_dist"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);
// rendered SQL statement
// select cume_dist() over (partition by row_type order by id) as "cume_dist" from test_analytic_methods where type = 'user';

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

Note

MySQL DB family: This analytic method 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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_dense_rank ( ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_as(cop_over(cop_dense_rank(), "row_type", "id"), "dense_rank"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);
// rendered SQL statement
select dense_rank() over (partition by row_type order by id) as "dense_rank" from test_analytic_methods where type = 'user';

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

Note

MySQL DB family: This analytic method is available only in MariaDB 10.2 and later only.

Returns

rank of the current row without gaps; this method counts peer groups

Since

SqlUtil 1.4.0

cop_distinct()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_distinct ( Object column ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_distinct("name"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_divide ( Object column1, Object column2 ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_divide("complete_count", "error_count"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_first_value ( Object column ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_as(cop_over(cop_first_value("row_value"), "row_type", "id"), "first_value"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);
// rendered SQL statement
select first_value(row_value) over (partition by row_type order by id) as "first_value" from test_analytic_methods where test = 'user';

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

Note

MySQL DB family: This analytic method 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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_last_value ( Object column ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_as(cop_over(cop_last_value("row_value"), "row_type", "id"), "last_value"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);
// rendered SQL statement
select last_value(row_value) over (partition by row_type order by id) as "last_value" from test_analytic_methods where type = 'user';

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

Note

MySQL DB family: This analytic method 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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_length ( Object column ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_length("product_code"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_lower ( Object column ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_lower("name"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_max ( Object column ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_max("id"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_min ( Object column ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_min("id"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_minus ( Object column1, Object column2 ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_minus("complete_count", "error_count"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_multiply ( Object column1, Object column2 ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_multiply("complete_count", "error_count"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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() [1/2]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_ntile ( int value ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_as(cop_over(cop_ntile(10), "row_type", "id"), "ntile"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);
// rendered SQL statement
select ntile(10) over (partition by row_type order by id) as "ntile" from test_analytic_methods where type = 'user';

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

Note

MySQL DB family: This analytic method 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_ntile() [2/2]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_ntile ( long value ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_as(cop_over(cop_ntile(10), "row_type", "id"), "ntile"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);
// rendered SQL statement
select ntile(10) over (partition by row_type order by id) as "ntile" from test_analytic_methods where type = 'user';

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

Note

MySQL DB family: This analytic method 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() [1/3]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_over ( Object column ) throws Throwable

inlinestatic

returns a ColumnOperatorInfo hash for the "over" clause

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_as(AbstractTable.cop_over(AbstractTable.cop_max("qty"), "account_id"), "max_qty_per_account")));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

Returns

a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

cop_over() [2/3]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_over ( Object column, String partitionby ) throws Throwable

inlinestatic

returns a ColumnOperatorInfo hash for the "over" clause

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_as(AbstractTable.cop_over(AbstractTable.cop_max("qty"), "account_id"), "max_qty_per_account")));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

Returns

a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

cop_over() [3/3]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_over ( Object column, String partitionby, String orderby ) throws Throwable

inlinestatic

returns a ColumnOperatorInfo hash for the "over" clause

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_as(AbstractTable.cop_over(AbstractTable.cop_max("qty"), "account_id"), "max_qty_per_account")));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

Returns

a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

cop_percent_rank()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_percent_rank ( ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_as(cop_over(cop_percent_rank(), "row_type", "id"), "percent_rank"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);
// rendered SQL statement
select percent_rank() over (partition by row_type order by id) as "percent_rank" from test_analytic_methods where type = 'user';

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

Note

MySQL DB family: This analytic method 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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_plus ( Object column1, Object column2 ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_plus("complete_count", "error_count"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_prepend ( Object column, String arg ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_prepend("name", "migrated-"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_rank ( ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_as(cop_over(cop_rank(), "row_type", "id"), "rank"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);
// rendered SQL statement
select rank() over (partition by row_type order by id) as "rank" from test_analytic_methods where type = 'user';

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

Note

MySQL DB family: This analytic method 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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_row_number ( ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_as(cop_over(cop_row_number(), "row_type", "id"), "row_number"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);
// rendered SQL statement
select row_number() over (partition by row_type order by id) as "row_number" from test_analytic_methods where type = 'user';

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

Note

MySQL DB family: This analytic method 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() [1/2]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_seq ( String seq ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_seq("xid", "xis"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

Parameters

seq	the name of the sequence whose value should be returned

Returns

a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

cop_seq() [2/2]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_seq ( String seq, String as ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_seq("xid", "xis"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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 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() [1/2]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_seq_currval ( String seq ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_seq_currval("xid", "xis"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

Parameters

seq	the name of the sequence whose current value should be returned

Returns

a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

cop_seq_currval() [2/2]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_seq_currval ( String seq, String as ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_seq_currval("xid", "xis"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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 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() [1/4]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_substr ( Object column, int start ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_substr("name", 1));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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_substr() [2/4]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_substr ( Object column, int start, int count ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_substr("name", 1, 1));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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_substr() [3/4]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_substr ( Object column, long start ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_substr("name", 1));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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_substr() [4/4]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_substr ( Object column, long start, long count ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_substr("name", 1, 1));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_sum ( Object column ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_sum("quantity"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_trunc_date ( Object column, String mask ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_trunc_date("mydate", AbstractTable.DT_MINUTE));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);
// 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 sql_cop_trunc_date_enum

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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_upper ( Object column ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_upper("name"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_value ( Object arg ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_value(100));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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:

|!Java Type|!SQL Type|!Qore Example|!SQL interpretation |int|NUMBER as it is|123|123 |float|NUMBER as it is|12.3|12.3 |java.math.BigDecimal|NUMBER as it is|1.2n|1.2 |java.time.ZonedDateTime|String representation of the date using DB native implementation like TO_TIMESTAMP for Oracle.|now()|to_timestamp('20150421104825000000', 'YYYYMMDDHH24MISSFF6') |boolean|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

Note

Passing an existing SQL method name as a value to the cop_value() method does not result in method call. The string value is escaped as it is. Example: sysdate becomes 'sysdate'. See example below.

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

Warning

Using cop_value() without 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.

cop_year()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_year ( Object column ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_year("error_time"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_year_day ( Object column ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_year_day("error_time"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_year_hour ( Object column ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_year_hour("error_time"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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()

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.cop_year_month ( Object column ) throws Throwable

inlinestatic

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

Example:

HashMap<String, Object> wh = new HashMap<String, Object>() {
    put("type", "user");
};
 
HashMap<String, Object> sh = new HashMap<String, Object>() {
    put("columns", AbstractTable.cop_year_month("error_time"));
    put("where", wh);
};
 
HashMap<String, Object> rows = t.selectRows(sh);

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() [1/2]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.make_cop ( String cop, Object column ) throws Throwable

inlinestatic

returns a ColumnOperatorInfo hash

Parameters

cop	the column operator (one of sql_cops)
column	the column name

Returns

a ColumnOperatorInfo hash corresponding to the arguments for use in the columns argument of a select option hash

Note

Normally this method is not called directly, but rather by the other column operator methods

make_cop() [2/2]

HashMap< String, Object > org.qore.lang.sqlutil.AbstractTable.make_cop ( String cop, Object column, Object arg ) throws Throwable

inlinestatic

returns a ColumnOperatorInfo hash

Parameters

cop	the column operator (one of sql_cops)
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 method is not called directly, but rather by the other column operator methods