Perforce 2007.3 P4Report User's Guide
<< Previous Chapter
Database Schema and SQL Keywords
Table of Contents
Index
Perforce on the Web
Next Chapter >>
Sample Crystal Report Queries

Appendix B
P4Report Functions

Connection Information Functions

The following table lists functions that return Perforce connection information.

Function
Description

P4CLIENT()

Returns the client specification currently in effect.

P4HOST()

Returns the name of the client host computer currently in effect, if specified in the definition of the client workspace.

P4PORT()

Returns the server/port setting currently in effect.

P4USER()

Returns the name of the Perforce user currently in effect.

Use the functions as shown in the following example:

String Functions

The following table lists the standard SQL string functions supported by P4Report.

Function
Description

ASCII(string)

Returns the ASCII code value of the leftmost character of string as an integer.

CHAR(code)

Returns the character that has the specified ASCII value.

CHAR_LENGTH(string)
CHARACTER_LENGTH(
string)

Returns the number of characters of the string.

CONCAT(string1, string2)

Returns a character string that is the result of concatenating string2 to string1.

INSERT(string1startlengthstring2)

Returns a character string where length characters have been deleted from string1, beginning at start, and where string2 has been inserted into string1, beginning at start.

LCASE(string)

Returns the specified string with all uppercase characters converted to lowercase.

LEFT(string, count)

Returns the leftmost count characters of string.

LOCATE(string1string2[, start])

Returns the starting position of the first occurrence of string1 within string2. The search for the first occurrence of string1 begins with the first character position in string2 unless start is specified. If start is specified, the search begins with the character position indicated by the value of start. The first character position in string2 is 1. If string1 is not found within string2, 0 is returned.

LTRIM(string)

Returns the characters of string, with leading white space removed.

REPEAT(stringcount)

Returns a character string composed of string repeated count times.

REPLACE(string1string2string3)

Search string1 for occurrences of string2 and replace with string3.

RIGHT(stringcount)

Returns the rightmost count characters of string.

RTRIM(string)

Returns the characters of string with trailing blanks removed.

SPACE(count)

Returns a character string consisting of count spaces.

SUBSTRING(stringstartlength)

Returns a character string that is derived from string, beginning at the character position specified by start for length characters.

UCASE(string)

Returns a string equal to that in string, with all lowercase characters converted to uppercase.

Numeric Functions

The following table lists the standard SQL numeric functions supported by P4Report.

Function
Description

ABS(numeric)

Returns the absolute value of numeric.

ACOS(numeric)

Returns the arccosine of numeric as an angle, expressed in radians.

ASIN(numeric)

Returns the arcsine of numeric as an angle, expressed in radians.

ATAN(numeric)

Returns the arctangent of numeric as an angle, expressed in radians.

ATAN2(numeric1numeric2)

Returns the arctangent of the x and y coordinates, specified by numeric1 and numeric2, respectively, as an angle expressed in radians.

CEILING(numeric)

Returns the smallest integer greater than or equal to numeric.

COS(numeric)

Returns the cosine of numeric.

DEGREES(numeric)

Returns the number of degrees converted from numeric radians.

EXP(numeric)

Returns the exponential value of numeric.

FLOOR(numeric)

Returns the largest integer less than or equal to numeric.

LOG(numeric)

Returns the natural logarithm of numeric.

LOG10(numeric)

Returns the base 10 logarithm of numeric.

MOD(numeric1numeric2)

Returns the remainder (modulus) of numeric1 divided by numeric2.

PI()

Returns the constant value of pi.

RADIANS(numeric)

Returns the number of radians converted from numeric degrees.

RAND([numeric])

Returns a random floating-point value, optionally using numeric as the seed value.

SIGN(numeric)

Returns an indicator of the sign of numeric. If numeric is less than zero, -1 is returned. If numeric equals zero, 0 is returned. If numeric is greater than zero, 1 is returned.

SIN(numeric)

Returns the sine of numeric.

SQRT(numeric)

Returns the square root of numeric.

TAN(numeric)

Returns the tangent of numeric, where numeric is an angle expressed in radians.

Date and Time Functions

The following table lists the standard SQL date and time functions supported by P4Report.

Function
Description

CURDATE()
CURRENT_DATE()

Returns the current date.

CURTIME()
CURRENT_TIME()

Returns the current local time.

CURTIMESTAMP()
CURRENT_TIMESTAMP()
NOW()

Returns the current local date and local time.

DAYNAME(date)

Returns a character string containing the name of the day.

DAYOFMONTH(date)

Returns the day of the month based on the month field in date as an integer value in the range of 1-31.

DAYOFWEEK(date)

Returns the day of the week based on the week field in date as an integer value in the range of 1-7, where 1 represents Sunday.

DAYOFYEAR(date)

Returns the day of the year based on the year field in date as an integer value in the range of 1-366.

HOUR(time)

Returns the hour based on the hour field in time as an integer value in the range of 0-23.

MINUTE(time)

Returns the minute based on the minute field in time as an integer value in the range of 0-59.

MONTH(date)

Returns the month based on the month field in date as an integer value in the range of 1-12.

MONTHNAME(date)

Returns a character string containing the name of the month.

QUARTER(date)

Returns the quarter in date as an integer value in the range of 1-4, where 1 represents January 1 through March 31.

SECOND(time)

Returns the second based on the second field in time as an integer value in the range of 0-59.

TIMESTAMPADD(unit, qty, sourcetime)

Modifies a timestamp by adding or subtracting time.

The unit parameter specifies the unit of time to be added as follows:

1 = seconds
2 = minutes
3 = hours
4 = days
5 = weeks
6 = months
7 = quarters
8 = years

The qty parameter specifies how many units (minutes, days or months etc.) are to be added. To subtract time, specify a negative value.

The sourcetime parameter specifies the timestamp expression to be modified.

Examples:

TIMESTAMPADD(1, 10, '1997-10-10 12:00:00')

Result: 1997-10-10 12:00:10

TIMESTAMPADD(3, 10, '1997-10-10 12:00:00')

Result: 1007-10-10 22:00:00

TIMESTAMPDIFF(interval, tstamp1, tstamp2)

Returns the integer number of intervals of type interval by which tstamp2 is greater than tstamp1. Specify interval as an integer indicating the desired unit, as follows:

1 = seconds
2 = minutes
3 = hours
4 = days
5 = weeks
6 = months
7 = quarters
8 = years

YEAR(date)

Returns the year based on the year field in date as an integer value.


Perforce 2007.3 P4Report User's Guide
<< Previous Chapter
Database Schema and SQL Keywords
Table of Contents
Index
Perforce on the Web
Next Chapter >>
Sample Crystal Report Queries
Please send comments and questions about this manual to manual@perforce.com.
Copyright 2002-2008 Perforce Software. All rights reserved.
Last updated: 03/01/07