컴퓨터관련

function reference... 함수 참조... mod modulus...펌

맘편한넘 2012. 2. 8. 06:42

Function Reference


A function can be used as part of an ｅxpression. Functions are specified as the function name (case is unimportant) followed by left and right parentheses. Parameters (values for the function to work on) may be specified inside the parentheses, and may be constant values (such as "Hello" or the number 10), fields from a database (such as CUSTOMERS.COMPANY), or another ｅxpression (such as YEAR(DATE())). You can surround strings with either single (') or double (") quotes.

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

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

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 )
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( ｅxpression1, VALUE1, ｅxpression2, VALUE2, ... ｅxpressionn, VALUEn, OTHERWISEVALUE )
If ｅxpression1 is True, ICASE (which stands for "Immediate CASE") returns VALUE1. Otherwise, it evaluates ｅxpression2, and returns VALUE2 if it's True. The comparison continues until either one of the ｅxpressions is True or none of them is, in which ICASE return OTHERWISEVALUE. Note that ｅxpressions and values come in pairs except for OTHERWISEVALUE, which has no matching ｅxpression.

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( ｅxpression, TRUE_RESULT, FALSE_RESULT )
If ｅxpression 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 ｅxpression. "LTRIM" is an abbreviation for "left trim."

LTRIM(" This is a test ") returns "This is a test "

MOD( DIVIDEND, DIVISOR )
Divides one numeric ｅxpression 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

_ReportPageNo
This isn't a function, but a system variable that contains the current page number in a report.

_ReportPageTotal
This isn't a function, but a system variable that contains the total number of pages in a report.

RECCOUNT()
Returns the total number of records in the data set for the report.

RECNO()
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, ｅxpressionToReturn )
Retrieves the value for the ｅxpressionToReturn ｅxpression from the TableToLookIn table for the record where FieldToLookIn has ValueToSearchFor as its value. ｅxpressionToReturn can be a single field name to return the value in that field. Normally, you need to put quotes around TableToLookIn, FieldToLookIn, and ｅxpressionToReturn. 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 ｅxpression.

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

Date Math

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.

Some Useful ｅxpressions

  • DATE() - DAY(DATE()) and GoMonthDaye(DATE(), -1, 31) both give the last day of the previous month

  • DATE() - DAY(DATE()) + 1 and GoMonthDay(DATE(), 0, 1) both give the first day of the current month

  • GOMONTH(DATE() - DAY(DATE()) + 1, 1) - 1 and GoMonthDay(DATE(), 0, 31) both give the last day of the current month

  • GOMONTH(DATE(), -1) is this day last month

  • GOMONTH(DATE(), 12) is this day next year

  • A range of dates between DATE() - DAY(DATE()) + 1 (which gives the first day of the current month) and DATE() is the month-to-date.

  • A range of dates between GoMonthDay(DATE(), -1, 1) (which gives the first day of last month) and GOMONTH(DATE(), -1) is last month-to-date.

  • A range of dates between DATE(YEAR(DATE()), 1, 1) (which gives the first day of the current year) and DATE() is the year-to-date.

  • A range of dates between GOMONTH(DATE(YEAR(DATE()), 1, 1), -12) (which gives the first day of last year) and GOMONTH(DATE(), -12) is last year-to-date.

  • MOD(RECNO(), 2) alternates between 0 (for even-numbered records) and 1 (for odd-numbered records), so using MOD(RECNO(), 2) = 1 as the Print When ｅxpression for a colored rectangle can be used to print alternating bars on a report such as shown below.