There are hundreds of functions built into Stonefield Query. However, most of them are relatively obscure or aren't relevant to querying. Here is a list of the more useful functions.
ALLTRIM( CHAR_VALUE )
Removes all blanks from both the beginning and the end of the parameter.
ALLTRIM(" This is a test ") returns "This is a test"
AT( CHAR_VALUE_TO_FIND, CHAR_VALUE_TO_SEARCH_IN )
ATC( CHAR_VALUE_TO_FIND, CHAR_VALUE_TO_SEARCH_IN )
Returns the position at which CHAR_VALUE_TO_FIND is found within CHAR_VALUE_TO_SEARCH_IN or 0 if CHAR_VALUE_TO_FIND is not found in CHAR_VALUE_TO_SEARCH_IN. With AT(), the search is case-sensitive; use ATC() for a case-insensitive search.
AT("soft", "Microsoft") returns 6, AT("soft", "MicroSoft") returns 0 (because of the capital "S"), and ATC("soft", "MicroSoft") returns 6.
BETWEEN( VALUE, START, END )
Returns True if the VALUE parameter is between the START and END parameters.
BETWEEN(10, 5, 20) returns True and BETWEEN(10, 15, 20) returns False.
CDOW( DATE_VALUE )
Returns the day part of the date parameter as the spelled-out day. "CDOW" is an abbreviation for "character day of week."
If SomeDate is 03/30/2004, CDOW(SomeDate) returns "Tuesday"
CHR( INTEGER_VALUE )
Returns the character whose numeric ASCII code is the parameter. The parameter must be between 0 and 255.
CHR(65) returns "A"
CMONTH( DATE_VALUE )
Returns the month part of the date parameter as the spelled-out month. "CMONTH" is an abbreviation for "character month."
If SomeDate is 03/30/2004, CMONTH(SomeDate) returns "March"
CTOD( CHAR_VALUE )
Converts a character value into a date value. "CTOD" is an abbreviation for "character to date."
CTOD("03/15/2004") returns 03/15/2004 as a date
Returns the current system date.
DATE( YEAR, MONTH, DAY)
Converts the year, month, and day (which are specified as numeric values) into a date value.
DATE(2004, 3, 30) returns 03/30/2004 as a date
DAY( DATE_VALUE )
Returns the day part of the date parameter as a numeric value from 1 to 31.
If SomeDate is 03/30/2004, DAY(SomeDate) returns 30.
DOW( DATE_VALUE )
Returns the day part of the date parameter as a numeric value representing the day of the week (1 for Sunday through 7 for Saturday). "DOW" is an abbreviation for "day of week."
If SomeDate is 03/30/2004, CDOW(SomeDate) returns 3 (the third day of the week, which is Tuesday)
DTOC( DATE_VALUE )
Converts a date value into a character value. "DTOC" is an abbreviation for "date to character."
If SomeDate is 03/30/2004, DTOC(SomeDate) returns "03/30/2004"
DTOS( DATE_VALUE )
Converts a date value into a character value formatted as "YYYYMMDD". "DTOS" is an abbreviation for "date to string."
If SomeDate is 03/30/2004, DTOC(SomeDate) returns "20040330"
EMPTY( VALUE )
Returns True if the parameter is an empty string or a string containing only spaces, is a numeric parameter with a value of 0, or is an empty date value.
If Address1 is an empty string, EMPTY(Address1) returns True.
GetConditionValue( FIELDNAME, INSTANCE )
Returns the value of the specified filter condition for the specified field.
If a report is filtered on Customers.Country = "Great Britain", "Sales for " + GetConditionValue('Customers.Country') returns "Sales for Great Britain". If there are two conditions for Customers.CompanyName, use GetConditionValue('Customers.CompanyName', 2) to get the value of the second condition.
GetValueForField( FIELDNAME, OPERATOR, DEFAULT )
Prompts the user for the value for a field. This is similar to the ask-at-runtime condition dialog except the value isn't used in a filter condition: it's used anywhere you wish in a report. If you call the function more than once with the same field name, the user isn't prompted again; instead, the value they entered the first time is returned. If OPERATOR isn't specified, "equals" is assumed. If the function returns NULL, the user clicked cancel.
If DEFAULT isn't specified, the default for the value is empty. If you want default values specified, pass a collection of values. For example:
loValues = SQApplication.GetValuesCollection() loValues.Add('1', date()) loValues.Add('2', date() + 7) lnMonth = GetValueForField('ORDERS.ORDERDATE', 'between', loValues)
This specifies that the default for the starting date should be today and today plus seven days for the ending date. Note that this can only be used in code, such as the AfterData script for a report, and not in an expression.
For example, in an advanced layout report, you might want a field that sums values only for a certain date. To ask the user for the value to use for the date, call GetValueForField('Table.Field'). You can use the return value both in the column header and in the field in the detail band, but the user will only be prompted once for the value.
GetValueForParameter( CAPTION, DATATYPE, LENGTH, DECIMALS, OPERATOR, DEFAULT )
Prompts the user for a value of the specified data type. This is similar to GetValueForField except rather than specifying a field, you indicate the caption, data type, etc. If OPERATOR isn't specified, "equals" is assumed. DATATYPE should be "C" for character, "N" for numeric, or "D" for date. LENGTH is the maximum length of the value and DECIMALS is the number of decimals (only necessary if DATATYPE is "N").
GOMONTH( DATE_VALUE, NUMBER_OF_MONTHS )
Returns a date the specified number of months from the date parameter. If NUMBER_OF_MONTHS is a positive value, a date after the specified date is returned; use a negative value to obtain a date before the specified date.
If SomeDate is 03/31/2004, GOMONTH(SomeDate, -1) returns 02/29/2004 and GOMONTH(SomeDate, 1) returns 04/30/2004
GoMonthDay( DATE_VALUE, NUMBER_OF_MONTHS, DAY )
Similar to GOMONTH() but returns a date of the specified day the specified number of months from the date parameter. If NUMBER_OF_MONTHS is a positive value, a date after the specified date is returned; use a negative value to obtain a date before the specified date. If DAY is greater than the number of days in the new month (for example, if the current date is in March and NUMBER_OF_MONTHS is 1, which gives April, and DAY is 31), the last day of the month is returned (in this example, April 30).
If SomeDate is 03/31/2004, GoMonthDay(SomeDate, -1, 15) returns 02/15/2004 and GoMonthDay(SomeDate, 1, 31) returns 04/30/2004
ICASE( EXPRESSION1, VALUE1, EXPRESSION2, VALUE2, ... EXPRESSIONn, VALUEn, OTHERWISEVALUE )
If EXPRESSION1 is True, ICASE (which stands for "Immediate CASE") returns VALUE1. Otherwise, it evaluates EXPRESSION2, and returns VALUE2 if it's True. The comparison continues until either one of the expressions is True or none of them is, in which ICASE return OTHERWISEVALUE. Note that expressions and values come in pairs except for OTHERWISEVALUE, which has no matching expression.
If SomeDate is 06/15/2004, ICASE(INLIST(MONTH(SomeDate), 1, 2, 3), "Quarter 1", INLIST(MONTH(SomeDate), 4, 5, 6), "Quarter 2", INLIST(MONTH(SomeDate), 7, 8, 9), "Quarter 3", "Quarter 4") returns "Quarter 2"
IIF( EXPRESSION, TRUE_RESULT, FALSE_RESULT )
If EXPRESSION is True, IIF (which stands for "Immediate IF") returns TRUE_RESULT. Otherwise, it returns FALSE_RESULT.
If SomeDate is 03/15/2004, IIF(INLIST(MONTH(SomeDate), 1, 2, 3), "Quarter 1", "Quarter 2") returns "Quarter 1"
INLIST( VALUE, ITEM1, ITEM2, ... )
Returns True if VALUE is contained in the specified list of items. Up to 24 items can be specified.
If SomeDate is 03/15/2004, INLIST(MONTH(SomeDate), 1, 2, 3) returns True.
LEFT( CHAR_VALUE, NUM_CHARS )
Returns the specified number of characters from the parameter, beginning at the first character on the left.
LEFT("Microsoft Windows", 5) returns "Micro"
LTRIM( CHAR_VALUE )
Removes any blanks from the beginning of the expression. "LTRIM" is an abbreviation for "left trim."
LTRIM(" This is a test ") returns "This is a test "
MOD( DIVIDEND, DIVISOR )
Divides one numeric expression by another and returns the remainder. This is known as the Modulus function.
If the current record in the data set for the report (obtained with the RECNO() function) is 11, MOD(RECNO(), 2) returns 1
MONTH( DATE_VALUE )
Returns the month part of the date parameter as a numeric value.
If SomeDate is 03/30/2004, MONTH(SomeDate) returns 3
This isn't a function, but a system variable that contains the current page number in a report.
This isn't a function, but a system variable that contains the total number of pages in a report.
Returns the total number of records in the data set for the report.
Returns the current record number in the data set for the report.
RunSQL( SQLStatement )
Executes the specified SQL statement, which should calculate a single value, and returns that value. See the Executing a SQL Statement During a Report Run topic for details.
SQLookup( TableToLookIn, FieldToLookIn, ValueToSearchFor, ExpressionToReturn )
Retrieves the value for the ExpressionToReturn expression from the TableToLookIn table for the record where FieldToLookIn has ValueToSearchFor as its value. ExpressionToReturn can be a single field name to return the value in that field. Normally, you need to put quotes around TableToLookIn, FieldToLookIn, and ExpressionToReturn. If ValueToSearchFor is the name of a field from the current data set, don't put quotes around it.
If you have a report that includes the Customer ID from the Orders table, SQLookup('Customers', 'CustomerID', CustomerID, 'CompanyName') returns the company name from the Customers table whose customer ID matches the current Customer ID.
STOD( VALUE, FORMAT )
Converts a character or date value in the specified format to a date value. Valid values for FORMAT are "YMD" to indicate that VALUE is in YYYYMMDD format, DMY for DDMMYYYY, or MDY for MMDDYYYY. If FORMAT isn't specified, "YMD" is assumed. If VALUE isn't a valid date, a blank date is returned.
STOD(20100323) returns 03/23/2010.
STR( NUMBER, LENGTH, DECIMALS )
Converts a numeric value into a character value. LENGTH is the number of characters in the new string, including the decimal point. DECIMALS is the number of decimal places desired. If the number is too big for the allotted space, asterisks are returned. "STR" is an abbreviation for "string conversion."
STR(5.7, 4, 2) returns "5.70"
STRTRAN( CHAR_VALUE, FIND_VALUE, REPLACE_VALUE )
Searches the first character parameter for occurrences of the second and replaces each occurrence with the third. Specify an empty string or omit the third parameter to remove FIND_VALUE from CHAR_VALUE. "STRTRAN" is an abbreviation for "string transformation."
STRTRAN("Microsoft Windows", "Micro", "Mega") returns "Megasoft Windows"
SUBSTR( CHAR_VALUE, START_POSITION, NUM_CHARS)
A substring of the character value, starting from START_POSITION and being NUM_CHARS long, is returned. "SUBSTR" is an abbreviation for "sub-string."
SUBSTR("Microsoft Windows", 6, 4) returns "soft"
TRANSFORM( VALUE )
Converts the parameter into the equivalent character value. This is useful for concatenating character and non-character values together.
If the data set for the report has 20 records in it, "There are " + TRANSFORM(RECCOUNT()) + " records" returns "There are 20 records." TRANSFORM() is needed since RECCOUNT() returns a numeric value.
TRIM( CHAR_VALUE )
Removes any blanks from the end of the expression.
TRIM(" This is a test ") returns " This is a test"
UPPER( CHAR_VALUE )
Converts a character string to uppercase.
UPPER("Microsoft Windows") returns "MICROSOFT WINDOWS"
VAL( CHAR_VALUE )
Converts a character value to a numeric value. "VAL" is an abbreviation for "value conversion."
If SomeString is "10", VAL(SomeString) returns 10
YEAR( DATE_VALUE )
Returns the year part of the date parameter as a numeric value.
If SomeDate is 03/30/2004, YEAR(SomeDate) returns 2004
You can perform certain types of math on date values. Adding a numeric value to a date adds that many days to the date. Subtracting one date from another gives the number of days between those dates.
For example, if SomeDate1 is 03/30/2004 and SomeDate2 is 04/01/2004, SomeDate1 + 1 gives 03/31/2004 and SomeDate2 - SomeDate1 gives 2.