9Functions

Functions

This section describes the operators that are available for building condition expressions and the functions that are specific to Siebel Personalization. It includes the following topics:

• String Functions

• Conditional Functions

• Lookup Functions

• Translation Functions

• Search Functions

• Math Functions

• Date and Time Functions

• Profile Functions

• Attribute Functions

• Other Functions

Note: Siebel Query Language parses literal strings incorrectly. For example, the expression " " = "X", where " " is an empty string, throws the following error: " " is not a field name.

String Functions

The following table lists string functions. See also Functions.

Function	Description
Left (string, integer)	Returns the left-most n characters in the text string or field. For example: Left ("Adams", 2) returns "Ad".
Mid (string, start [, integer])	Returns n characters from the string starting from the start location. For example: Mid ("Adams", 2, 2) returns "da" Mid ("Adams", 2) returns "dams".
Right (string, integer)	Returns the right-most n characters in the text string or field. For example: Right ("Adams", 2) returns "ms".
Len (string)	Returns the length of character string. For example: Len ("foo") returns 3.
InStr (string1, string2 [, start] [, compare])	The position of the first character of string2 in string1. [,start] is used to specify the position in string1 to begin the search, where the first character in the string is 1. If start is negative, InStr searches backwards. [,compare] is used to specify if the comparison of strings is case sensitive. Enter 0 for a case-sensitive search, enter 1 for a case-insensitive search. For example: InStr ("foo", "oo") returns 2. InStr ("bda", "Adams", 2) returns 2. InStr ("BDA", "Adams", 2, 1) returns 2. If the string is not found, the function returns 0.
InList (string1, comma-separated list)	Returns if string1 occurs one or more times in a comma-separated list. For example: InList("foo", "abc,boo,foo") returns TRUE.

Conditional Functions

The following table lists the conditional functions. See also Functions.

Function	Description
IfNull (expr1, expr2)	Returns expr1 if expr1 is not NULL or returns expr2 if expr1 is NULL. If Null is the return type of its first argument even if the first argument is NULL. For example: IfNull ("", "foo") returns "foo".
IIf (testExpr, expr1, expr2)	If testExpr is TRUE, returns expr1’s value, otherwise returns expr2’s value. IfNull is the return type of its first argument even if the first argument is NULL. The second argument is converted to the type of the first argument before its value is returned. For example: IIf ([Last Name] IS NULL, "foo", "boo") returns foo if [Last Name] is NULL.
IsPrimary ()	For MVGs, returns if the current record is the primary record. IsPrimary () returns TRUE if the current address in an MVG address field is the primary address.
BCHasRows (BO, BC, search_expr, visibility)	Returns TRUE if the business component BC that is a part of business object BO has any rows after applying the search_expr and visibility. Evaluates to TRUE or FALSE depending on whether a given business component would return any records given the search specification and visibility. For example: BCHasRows ("Contact", "Contact", "", "Organization") returns TRUE if there are 1 or more rows of contacts. For example, you could hide an applet based on how many records it displayed. However, hiding and showing detail applets using BCHasRows is not possible.

Lookup Functions

The following information lists lookup functions. See also Functions.

Note: The Lookup functionality is based on the LOV record's LANG_ID column matching the language code of the currently active language.

Function	Description
Lookup (type, value)	Finds a row in the List of Values table (S_LST_OF_VAL) where the TYPE column matches the type argument and the VALUE column matches the value argument. The function returns the value of the ORDER_BY column for that row. For example: Lookup ("MR_MS", "Ms.") returns "2".
LookupExpr (type, value_expr)	Searches the rows in the List of Values table (S_LST_OF_VAL) where the TYPE column matches the type argument. LookupExpr evaluates the contents of the VALUE column treated as an expression. Returns the value of the ORDER_BY column for the first row for which the expression evaluates to TRUE. For example: LookupExpr ("MR_MS", "M*") returns "1".
LookupName (type, lang_ind_code)	Finds a row in the List of Values table (S_LST_OF_VAL) where the TYPE column matches the type argument, the NAME column matches the lang_ind_code argument, and the LANG_ID column matches the language code of the currently active language. LookupName returns the language-independent code (the NAME column) for the row. For example: LookupName ("MR_MS", "Ms.") returns "Ms."
ParentFieldValue (field_name)	The value of the field_name field in the parent business component. The result does not change if the parent row is updated. The parent business component field must be exported by using Link Specification. For example: for the Opportunity business component ParentFieldValue("Account") returns "Name".
FieldValue (field_name)	Return value of field in the business component as a string. For example: for Contacts business component for Henry Kim. FieldValue ("Last Name") returns "Kim".
LookupValue (type, lang_ind_code)	Finds a row in the List of Values table (S_LST_OF_VAL) where the TYPE column matches the type argument, the NAME column matches the lang_ind_code argument, and the LANG_ID column matches the language code of the currently active language. LookupValue returns the display value for the specified lang_ind_code, and if not found, the lang_ind_code itself as the value. For example: LookupValue ("MR_MS", "Ms.") returns "Ms."

Translation Functions

The following table lists the translation functions. See also Functions.

Function	Description
LookupTransVal (lang_ind_code, table, type)	For a multilingual list of values, given a language independent code, translation table (must be S_LST_OF_VAL), and LOV type, returns the display value. For example: LookupTransVal ("Mr.", "S_LST_OF_VAL", "MR_MS" returns "Mr."
LookupTransCode (display_value, table, type)	For a multilingual list of values, given a display value, translation table (must be S_LST_OF_VAL), and LOV type, returns the language independent code. For example: LookupTransCode ("Mr.", "S_LST_OF_VAL", "MR_MS" returns "Mr."
LookupTranslation ([field])	For the new multilingual data feature. Takes a field as an argument, and if the field has a translation, returns the translation. Otherwise returns the value of the field. For example: LookupTranslation([Name]) returns "Sam" if the current language is English.
Preference (category, pref_name)	Finds the user preference for the category and preference name specified. For example: Preference ("User Interface", "StyleSheet") returns the user-specified style sheet preference.
LoginName()	Returns the login ID of the logged in user.

Search Functions

The following table lists the search functions. See also Functions.

Function	Description
FindOneOf (string1, string2)	Returns 1-based index of the first instance in string1 of a character in string2. For example: FindOneOf (“abcdef", "xyzc") returns 3.
FindNoneOf (string1, string2)	Returns 1-based index of the first instance in string1 which does not match any character in string2. For example: FindNoneOf ("abcdef", "xyzc") returns 1.

Math Functions

The following table lists the math function. See also Functions.

Function	Description
Sum (mvfield)	Sums the values from a field in child records. You must define the child record that is being summed from as a multivalue field that is part of a multivalue group. The multivalue group is associated with the business component of the field that is being summed. For example: Sum ([Number of Employees]) gives the sum of all the employees at different locations for a company.
Count (mvfield)	Returns the number of rows in the multi-value group defined by the mvfield. For example: Count([Number of Employees]) gives the number of employees at different locations for a company.
ToChar ([field_name], 'format')	Returns a string that represents a number or date in a format specified by the optional format parameter. For example: ToChar([Start Date], 'MM/DD/YYYY') returns the starting date of a record as a string in MM/DD/YYYY format.
Min (mvfield)	Returns the minimum value from a field in child records. You must define the child record being examined as a multivalue field that is part of a multivalue group. The mutlivalue group is associated with the business component of the field being evaluated. For example: Min ([Number of Employees]) gives the minimum number of employees of all the locations.
Max (mvfield)	Returns the Maximum value from a field in child records. You must define the child record being examined as a multivalue field that is part of a multivalue group. The mutlvalue group is associated with the business component of the field being evaluated. For example: Max ([Number of Employees]) gives the maximum number of employees of all the locations.
InvokeServiceMethod (name, method, context, returnProperty)	Returns the value of the return property from the returnProperty set of the specified business service, after invoking the method with the context. For example: InvokeServiceMethod ("BusServ", "PersonalizationMethod", "Key1=a,Key2=2", "ReturnProperty") invokes the business service method PersonalizationMethod in business service BusServ, passes it the context Key1=a,Key2=2, and returns the value set by the business service in the property ReturnProperty. To use the InvokeServiceMethod function in client mode, the business service name that is called by the InvokeServiceMethod function must be added as the BusinessServiceQueryAccessList parameter value under the [Siebel] section of the application configuration file as follows: [Siebel] BusinessServiceQueryAccessList = BusSvc1,BusSvc2,...Likewise, in Server mode, the component parameter BusinessServiceQueryAccessList must also be set to specify the list.
LN (num)	Returns the natural log of the num. For example: LN (10) returns 2.30.
GetNumBCRows (BO, BC, search_expr, visibility)	Returns the number of rows business component BC has, which is part of business object BO, after applying the search_expr and visibility. For example: GetNumBCRows ("Contact", "Contact", "A*", "Organization") returns the number of rows that match the criteria.

Date and Time Functions

The following information lists date and time functions. See also Functions.

Function	Description
JulianDay ()	Equal to the Oracle (and Sagent) Julian Day for all dates in the 20th and 21st centuries. Assigns an absolute numeric value to every date in order to perform calculations on the dates.
JulianMonth ()	Equal to the JulianYear() * 12 + currentMonth, where January = 1.
JulianQtr ()	Equal to the JulianYear () * 4 + currentQuarter, where currentQuarter = [(currentMonth -1) / 3+1] rounded down to the next integer.
JulianWeek ()	JulianDay () / 7, rounded down to the next integer.
JulianYear ()	Equal to the current year + 4713.
Today ()	Retuns the current date. For example: 2/15/2001 Today() does not do the UTC (universal time code) conversion and TimeStamp() does do the conversion. Note: Today() and Timestamp() functions return different results. For more information, see the row for Timestamp ().
Timestamp ()	Returns today’s date and time. For example: 2/15/2001 11:15:22 TimeStamp() does the UTC (universal time code) conversion and Today() does not do the conversion. Note: Today() and Timestamp() functions may return different results. For example: Add these fields to a standard report. On the machine that hosts the Report Server and Siebel Server, change the time zone to one different from the user machine. For example, 10/03/03 1:45 A.M. (EST) would be 10/02/03 10:45 P.M. on the user's machine. When running this report from the user's machine (Web client), while the Timestamp() field shows the correct time in the user's time zone, the Today() field displays 10/03/03, even though it is still 10/02/03 for the user.
UtcConvert (utc_date_time, time_zone	This function converts a local time (in the current user’s logged in user’s time zone) to another local time in the specified time zone. For example, if the user is on Pacific time the time is converted to eastern time: UtcConvert("12/14/2000 5:07:05 PM", "Eastern Standard Time") returns “12/14/2000 00:07:05 PM”

Profile Functions

The following table list the profile functions. See also Functions.

Function	Description
GetProfileAttr(‘profile’)	Returns the profile attribute of the current user. For example: GetProfileAttr ('Personal Country') returns the country of the user. GetProfileAttr only works for fields explicitly defined in the Personalization Profile business component. The function does not work with system fields, which are not explicitly defined in the business component; it returns a NULL value for them. An exception is the Id system field. This particular system field is available to GetProfileAttr even though it is not in the Personalization Profile business component. If this function is called for an MVG, it returns just the primary value of the MVG. For example, if the MVG State has the values CA, MA, and GA, where CA is primary: GetProfileAttr ('State') returns CA.
GetProfileAttr(“org.country”)	Returns one of the following: Where there is an account associated with the logged in user, GetProfileAttr("org.country") returns the Country of the contact account’s primary address. Where there is no account associated with the logged in user, then GetProfileAttr("org.country") returns the Country of the user organization’s primary address. Note: To ensure that you retrieve the organization name in instances where the employee is associated with an account as a contact, it is recommended that you map an additional attribute on the Personalization Profile business component (which you can use to retrieve the organization name).
GetProfileAttrAsInt(‘profile’)	Returns the profile attribute of the current user as an integer. For example: GetProfileAttrAsInt ('Age') returns the age of the user as an integer.
GetProfileAttrAsList (‘profile’)	Returns the MVG value as a list. You can use GetProfileAttrAsList in the EXISTS operator to create expressions that match MVG profile attributes with appropriate Siebel content. If GetProfileAttrAsList is used: Outside the Exists operator, then it returns a comma-separated list of the MVG values. For example, if the MVG State has the values CA, MA, and GA, where CA is primary it returns CA, MA, GA. GetProfileAttrAsList ('State') For a single value field, then it returns the profile attribute value. Within the EXISTS operator, then it returns the value profile attributes in the form expected by the user. For example, you could use the EXISTS operator this way: EXISTS ([Targeted States] = GetProfileAttrAsList ("State")] This action would match the MVG business component field Targeted State against the MVG profile attribute State.
GetProfileAttrAsNum (‘profile’)	Returns the profile attribute of the current user as a number. For example: GetProfileAttrAsNum ('Age') returns the age of the user as a number.
GetProfileAttrAsDate (‘profile’)	Returns the profile attribute of the current user as a date. For example: GetProfileAttr ('Birth Day') returns the birthday of the user as a date.
GetProfileAttrAsTime (‘profile’)	Returns the profile attribute of the current user as time. For example: GetProfileAttr ('Last Login Time') returns the last login time of the user as time.
GetProfileAttrAsDateTime (profile_attrib)	Returns the profile attribute of the current user as date and time. For example: GetProfileAttr ('Last Login Date') returns the last login date and time of the user as date and time.
GetProfileAttrAsUtcDateTime (profile_attrib)	Returns the profile_attrib formatted in the UtcDateTime type. For example: GetProfileAttrAsUtcDateTime ('Date') returns date time in UTC format.

Attribute Functions

The following table lists attribute functions. See also Functions.

Function	Description
XAIsClass (classname)	Returns TRUE if the current object belongs to the class classname including subclasses. For example: XAIsClass (""Car"") returns TRUE for Honda Accord.
GetXAVal (attributename)	Returns the value for the specified string-type attribute for the current object if any. For example: GetXAVal (""Seat Type"") returns leather for a luxury car with leather seats.
GetXAValAsInt (attributename)	Returns the value for the specified integer-type attribute for the current object if any. For example: GetXAValAsInt (""Doors"") returns 4 for a four-door sedan.
GetXAValAsNum (attributename)	Returns the value for the specified number-type attribute for the current object if any. For example: GetXAValAsNum (""Doors"") returns 4 for a four-door sedan.
GetXAValAsDate (attributename)	Returns the value for the specified date-type attribute for the current object if any. For example: GetXAValAsDate (""Release Date"") returns the release date attribute for the row as date.

Other Functions

The following table lists other functions. See also Functions.

Function	Description
SystemPreference (“Pref”)	Get value of system preference. These values are found in the Administration - Application screen > System Preferences view. For example: SystemPreference (""Auto Mgr Calendar Access"") returns TRUE.
LoginId ()	Returns the Login ID of the logged in user.
BCName ()	Returns the name of the active business component. For example: BCName () returns ““Account””.
Currency ()	Returns the currency for the position of the logged in user. For example: USD
LocalCurrency ()	Returns the currency for the machine. For example: JPY
Language ()	Language code (for example, ENU) that is the active client language setting. The language code is set by either the Language parameter in the .cfg file, or by the /L parameter when starting Oracle’s Siebel application.
RowidToRowidNum ()	Converts the Row ID of a record to a number. For example: RowidToRowidNum () returns the unique integer translation of Rowid.