Qore SqlUtil Module Reference  1.3.3
 All Classes Namespaces Functions Variables Groups Pages
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_cast (any column, string arg, *any arg1, *any arg2)
 returns a hash for the "cast" operator with the given argument(s) More...
 
hash SqlUtil::cop_coalesce (any col1, any col2)
 returns a hash for the "coalesce" operator with the given column arguments; the first non-NULL column value will be returned More...
 
hash SqlUtil::cop_count (any 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_length (any column)
 returns a hash for the "len" operator with the given argument; returns the length of the given text field 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, *string as)
 returns a hash for the "seq" operator with the given argument giving the sequence name whose value should be returned More...
 
hash SqlUtil::cop_seq_currval (string seq, *string as)
 returns a hash for the "seq_currval" operator with the given argument giving the sequence name whose current value should be returned More...
 
hash SqlUtil::cop_substr (any column, int start, *int count)
 returns a hash for the "substr" operator with the given arguments; returns a substring of a column value More...
 
hash SqlUtil::cop_sum (any column)
 returns a hash for the "sum" operator; returns the total sum of a numeric column. 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" (literal) 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:

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

Example:
1 *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:
1 *list rows = table.selectRows(("columns": ("id", cop_append("name", "-migrated")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
argthe text to append (ie concatenate) to the row values in the output column
Returns
a 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:
1 *list rows = table.selectRows(("columns": ("id", "name", cop_as("errors", "error_count")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins) or any other column "cop_..." function
argthe new name of the output column
Returns
a column operator description 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
hash SqlUtil::cop_avg ( any  column)

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

Example:
1 *list rows = table.selectRows(("columns": (cop_avg("quantity")), "where": ("order_type": "wholesale")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a column operator description hash corresponding to the arguments for use in the columns argument of a select option hash
hash SqlUtil::cop_cast ( any  column,
string  arg,
*any  arg1,
*any  arg2 
)

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

Example:
1 *list rows = table.selectRows(("columns": ("id", cop_cast("id", "string")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins) or any other column "cop_..." function
argthe new datatype to cast the column value(s) to
arg1optional, type dependent, specification (e.g. size or precision)
arg2optional, type dependent, specification (e.g. scale)
Returns
a column operator description 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
hash SqlUtil::cop_coalesce ( any  col1,
any  col2 
)

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

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

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

Example:
1 *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:
1 *list rows = table.selectRows(("columns": ("id", cop_distinct("name")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a 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:
1 *list rows = table.selectRows(("columns": ("id", "name", cop_divide("complete_count", "error_count")), "where": ("type": "user")));
Parameters
column1the column specification for the first argument (string name or dot notation for use in joins)
column2the column specification for the second argument (string name or dot notation for use in joins)
Returns
a column operator description hash corresponding to the arguments for use in the columns argument of a select option hash
hash SqlUtil::cop_length ( any  column)

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

Example:
1 *list rows = table.selectRows(("columns": ("id", cop_length("product_code")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a column operator description hash corresponding to the arguments for use in the columns argument of a select option hash
Since
SqlUtil 1.3.1
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:
1 *list rows = table.selectRows(("columns": ("id", cop_lower("name")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a 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:
1 *list rows = table.selectRows(("columns": (cop_max("id")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a 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:
1 *list rows = table.selectRows(("columns": (cop_min("id")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a 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:
1 *list rows = table.selectRows(("columns": ("id", "name", cop_minus("complete_count", "error_count")), "where": ("type": "user")));
Parameters
column1the column specification for the first argument (string name or dot notation for use in joins)
column2the column specification for the second argument (string name or dot notation for use in joins)
Returns
a 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:
1 *list rows = table.selectRows(("columns": ("id", "name", cop_multiply("complete_count", "error_count")), "where": ("type": "user")));
Parameters
column1the column specification for the first argument (string name or dot notation for use in joins)
column2the column specification for the second argument (string name or dot notation for use in joins)
Returns
a 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:
1 *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:
1 *list rows = table.selectRows(("columns": ("id", "name", cop_plus("complete_count", "error_count")), "where": ("type": "user")));
Parameters
column1the column specification for the first argument (string name or dot notation for use in joins)
column2the column specification for the second argument (string name or dot notation for use in joins)
Returns
a 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:
1 *list rows = table.selectRows(("columns": ("id", cop_prepend("name", "migrated-")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
argthe text to prepend to the row values in the output column
Returns
a 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,
*string  as 
)

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

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

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

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

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

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

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

Example:
1 *list rows = table.selectRows(("columns": (cop_sum("quantity")), "where": ("order_type": "wholesale")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a 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:
1 *list rows = table.selectRows(("columns": ("id", cop_upper("name")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a 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" (literal) operator with the given argument

Example:
1 *list rows = table.selectRows(("columns": ("id", "name", cop_value(100)), "where": ("type": "user")));
Parameters
argthe 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

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.getRowIterator(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 op_value 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 nice and 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

1 %new-style
2 %requires SqlUtil
3 
4 DatasourcePool ds("oracle:pvanek_omq/omq@xbox(al32utf8)");
5 Database db(ds);
6 Table t(ds, "dual");
7 
8 hash sh = (
9  "columns" : (
10  cop_as(cop_value(1), "as_int"),
11  cop_as(cop_value(1.2), "as_float"),
12  cop_as(cop_value(3.2n), "as_numeric"),
13  cop_as(cop_value(now()), "as_date"),
14  cop_as(cop_value("foo bar"), "as_string"),
15  cop_as(cop_value("sysdate"), "as_function_call"),
16  cop_as(cop_value(True), "as_bool"),
17  cop_as(cop_value(NULL), "as_null"),
18  cop_as(cop_value(NOTHING), "as_nothing"),
19  ),
20 );
21 string sql;
22 on_exit { printf("sql: %s\n", sql); }
23 printf("%N\n", t.selectRow(sh, \sql));
24 
25 # results in:
26 hash: (9 members)
27  as_int : 1
28  as_float : 1.2
29  as_numeric : 3.2
30  as_date : 2015-04-21 10:56:57.000000 Tue +02:00 (CEST)
31  as_string : "foo bar"
32  as_function_call : "sysdate"
33  as_bool : 1
34  as_null : <NULL>
35  as_nothing : <NULL>
36 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
hash SqlUtil::cop_year ( any  column)

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

Example:
1 *list rows = table.selectRows(("columns": ("id", "name", cop_year("error_time")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a 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:
1 *list rows = table.selectRows(("columns": ("id", "name", cop_year_day("error_time")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a 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:
1 *list rows = table.selectRows(("columns": ("id", "name", cop_year_hour("error_time")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a 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:
1 *list rows = table.selectRows(("columns": ("id", "name", cop_year_month("error_time")), "where": ("type": "user")));
Parameters
columnthe column specification for the column (string name or dot notation for use in joins)
Returns
a 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
copthe column operator (one of SQL Column Operators)
columnthe column name
argthe 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