SQL Concepts

SQL data is conceptualized differently from data in PRO/5 and other database management systems that are based on the concept of files and records. If you wish to attempt to relate the concepts you can only approach similarity. In SQL the "database" is close to the concept of splitting of data between functional programs - accounts payable, accounts receivable, etc., but may be closer to the concept of splitting data between organizations or groups within organizations.

Once a database is chosen you can access tables in the database. A TABLE in SQL is close in concept to the FILE in PRO/5 - a division of data below the database level. In SQL, however, a table does not have to map to a particular file and may be a group of data from multiple tables or part of another table (called a VIEW).

Inside tables are "rows", roughly analogous to records in files, and rows consist of "columns", again analogous to fields in records. The primary difference is that SQL will never allow multiple record types in a field, something common in file/record systems.

PRO/5 data access commands map to SQL commands as follows:

PRO/5 Command	SQL Command
READ	SELECT
WRITE	INSERT/UPDATE
REMOVE	DELETE
Create "file"	CREATE TABLE
Erase "file"	DROP TABLE

Database Selection

The SQLLIST() function returns a list of databases that PRO/5 is configured to handle.

To select a database use the SQLOPEN verb. This command associates a user-specified channel with a database selected from those listed in the SQLLIST() function.

ABS(numeric_exp)	Returns the absolute value of numeric_exp.
ACOS(numeric_exp)	Returns the arccosine of float_exp as an angle expressed in radians.
ASCII(string_exp)	Returns the ASCII code of the left-most character of string_exp as an integer.
ASIN(numeric_exp)	Returns the arcsine of float_exp as an angle expressed in radians.
ATAN(float_exp)	Returns the arctangent of float_exp as an angle expressed in radians.
ATAN2(double,double)	Returns the arctangent of the x and y coordinates, specified by float_exp1 and float_exp2, respectively, as an angle, expressed in radians.
CEILING(numeric_exp)	Returns the smallest integer greater than or equal to numeric_exp.
CHAR(code)	Returns the character that has the ASCII code value returns the character specified by code. The code value should be between 0 and 255; otherwise, the return value is data source-dependent.
CONCAT(string_expl, string_exp2)	Returns a character string that is the result of concatenating string_exp2 to string_exp1. The resulting string is DBMS dependent. For example, if the column represented by string string_exp1 contains a NULL value, DB2 would return NULL, but SQL Server would return the non-NULL string.
COS(float_exp)	Returns the cosine of the float_exp as an angle expressed in radians.
COT(numeric_exp)	Returns the cotangent of numeric_exp as an angle expressed in radians.
CURDATE	Returns the current date in the format yyyy-mm-dd, as a DATE.
CURTIME	Returns the current time as a CHAR.
DAYNAME(date_exp)	Returns a character string containing the name of the day of the week (e.g., Sunday) for the day portion of date_exp.
DAYOFMONTH(date_exp)	Returns the day of the month in date_exp as an integer value in the range of 1-31.
DAYOFWEEK(date_exp)	Returns the day of the week in date_exp as an integer value in the range of 1-7, where 1 represents Sunday.
DAYOFYEAR(date_exp)	Returns the day of the year in date_exp as an integer value in the range of 1-366.
DEGREES(numeric_exp)	Returns the number of degrees converted from numeric_exp radians.
DIFFERENCE(char,char)	Returns and integer value that indicates the difference between the values returned by the SOUNDEX function for string_exp1 and string_exp2.
EXP(numeric_exp)	Returns the exponential value of float_exp.
FLOOR(numeric_exp)	Returns the largest integer less than or equal to numeric_exp.
HOUR(time_exp)	Returns the hour based on the hour field in time_exp as an integer value in the range of 0-23.
INSERT(string_expl, start,length,string_exp2)	Returns a character string where length characters have been deleted from string_exp1 beginning at start and where string_exp2 has been inserted into string_exp1, beginning with start.
LCASE(string_exp)	Converts all upper case characters in string_exp to lower case.
LEFT(string_exp,count)	Returns the leftmost count characters of string_exp.
LENGTH(string_exp)	Returns the length of string_exp.
LOCATE(string_exp1, string_exp2[,start])	Returns the starting position of the first occurrence of string_exp1 within string_exp2. The search for the first occurrence of string_exp1 begins with the first character position in string_exp2, unless the optional start argument is specified. If start is specified, the search begins with the character position indicated by the value of start. The first character position string_exp2 is indicated by the value 1. If string_exp1 is not found within string_exp2, the value 0 is returned.
LOG(numeric_exp)	Returns the natural logarithm of float_exp.
LOG10(numeric_exp)	Returns the base 10 logarithm of float_exp.
LTRIM(string_exp)	Returns the characters of string_exp, with leading blanks removed.
MINUTE(time_exp)	Returns the minute based on the minute field in time_exp as an integer value in the range of 0-59.
MOD(integer_exp1, integer_exp2)	Returns the remainder (modulus) of integer_exp1 divided by integer_exp2.
MONTH(date_exp)	Returns the month in date_exp as an integer value in the range of 1-12.
MONTHNAME(date_exp)	Returns a character string containing the name of the month (e.g., January through December) for the month portion of date_exp.
NOW()	Returns the current date and time as a timestamp value.
PI()	The numeric literal for pi defined as:3.14159265358979323846264338327950288419716939937510.
POWER(numeric_exp)	Returns the value of numeric_exp to the power of integer_exp.
QUARTER(date_exp)	Returns the quarter in date_exp as an integer value in range of 1-4, where 1 represents January 1 through March 31.
RADIANS(numeric_exp)	Returns the number of radians concerted from numeric_exp degrees.
RAND(numeric_exp)	Generates a random number using numeric_exp as a seed.
REPEAT(string_exp,count)	Returns a character string composed of string_exp repeated count times.
REPLACE(string_exp1, string_exp2, string_exp3)	Replaces each occurrence of string_exp2 in string_exp1 with string_exp3.
RIGHT(string_exp,count)	Returns the last count characters of string_exp.
ROUND(numeric_exp, integer_exp)	Rounds numeric_exp to integer_exp decimal places.
RTRIM(string_exp)	Returns the characters of string_exp with trailing blanks removed.
SECOND(time_exp)	Returns the second based on the second field in time_exp as an integer value in the range of 0-59.
SIGN(numeric_exp)	If numeric_exp is less than zero, -1 is returned. If numeric_exp equals zero, 0 is returned. If numeric_exp is greater than zero, 1 is returned.
SIN(float_exp)	Returns the sine of the float_exp as an angle expressed in radians.
SOUNDEX(string_exp)	Returns a data source-dependent character string representing the sound of the words in string_exp. For example, SQL Server returns a four digit SOUNDEX code.
SPACE(count)	Returns a character string consisting of count spaces.
SQRT(numeric_exp)	Returns the square root of numeric_exp.
SUBSTRING(string_exp,start,length)	Returns a character string that is derived from string_exp beginning at the character position specified by start for length characters.
TAN(numeric_exp)	Returns the tangent of numeric_exp as an angle expressed in radians.
TIMESTAMPADD (interval,integer_exp, timestamp_exp)	Returns the timestamp calculated by adding integer_exp intervals of type interval to timestamp_exp.
TIMESTAMPADIFF (interval, timestamp _exp1, timestamp_exp2)	Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1.
TRIM(string_exp)	Returns the characters of string_exp with leading and trailing blanks removed.
TRUNCATE(numeric_exp)	Returns numeric_exp with the decimal portion removed.
UCASE(string_exp)	Converts all lower-case characters in string_exp to upper case.
YEAR(date_exp)	Returns the year in date_exp as an integer value.