Sage 100 Providex SQL Syntax | DataSelf Knowledge Base

https://www.manual.pvxplus.com/ Help file with the language reference for PxPlus and past versions of ProvideX.

On this page

Filtering by Date

Example:

SQL

...
FROM AR_InvoiceHistoryHeader
WHERE InvoiceDate>={d'2020-09-25'}

Converting Date to Character

Sage 100 providex bad dates

Apply when running into MS SQL Server invalid date values (valid dates: 01/01/1753 to 12/31/999).

SQL

{fn convert(OrderDate,SQL_VARCHAR)} as OrderDate,

and then back again for the valid dates

Join Syntax

SQL

    INNER JOIN ij

    LEFT OUTER JOIN oj

    RIGHT OUTER JOIN oj

Parent1 → Child1

SQL

{oj P1 LEFT OUTER JOIN C12 ON (PK1=CK1)}

SQL

FROM { ij(AR_Customer Cust INNER JOIN AR_InvoiceHistoryHeader H 
   ON Cust.ARDivisionNo = H.ARDivisionNo
 AND Cust.CustomerNo = H.CustomerNo) }

Parent1 → Parent2 → Child1

SQL

{oj P1 LEFT OUTER JOIN P2 ON (PK1=PK2)  LEFT OUTER JOIN C1 ON (PK2a=CK1)}

SQL

SELECT D.InvoiceNo,D.HeaderSeqNo
,D.DetailSeqNo,H.ARDivisionNo
,H.TermsCode,Cust.CustomerNo
,Cust.ARDivisionNo
FROM { ij
  (AR_Customer Cust 
  INNER JOIN AR_InvoiceHistoryHeader H ON Cust.ARDivisionNo = H.ARDivisionNo AND
     Cust.CustomerNo = H.CustomerNo)
  INNER JOIN AR_InvoiceHistoryDetail D ON D.InvoiceNo=H.InvoiceNo AND
      D.HeaderSeqNo=H.HeaderSeqNo }

Parent1 → Child1, Parent2 → Child1

SQL

{oj P1 LEFT OUTER JOIN C1 ON (PK1=CK1)},
{oj P2 LEFT OUTER JOIN C1 ON (PK2=CK1a)}

Example:

SQL

FROM 
  {oj AR_InvoiceHistoryHeader 
    LEFT OUTER JOIN AR_InvoiceHistoryDetail ytdSales ON 
      (AR_Customer.ARDivisionNo=ytdSales.ARDivisionNo) AND (AR_Customer.CustomerNo=ytdSales.CustomerNo)},
  {oj AR_Customer custPy LEFT OUTER JOIN AR_CustomerSalesHistory pySales ON
    (custPy.ARDivisionNo=pySales.ARDivisionNo) AND (custPy.CustomerNo=pySales.CustomerNo)}

Comments

The PxPlus SQL ODBC Driver also allows comments within SQL queries. Both block and line comments are supported. (added in PxPlus 2016)

Block Comment:	/* This is a comment */
Line Comment:	-- This is also a comment

Example:

SQL

       /* This is an example SQL query with comments */
       SELECT * --Get all rows
       FROM 'Customer' --List all customers

Documentation source: https://manual.pvxplus.com/PXPLUS/odbc/using_odbc_driver/sql_syntax_table.htm

Providex Scalar Functions

Scalar functions can be included right in the SQL query in ODBC connections anywhere a simple term is supported or included through the use of the escape sequence:

scalar function | {fn scalar function}

Where:

scalar function can be any of the String, Numeric, Time and Date or System functions listed below.

The following tables list the supported scalar functions with descriptions of their results:

String Functions

This table lists the string functions:

Function	Description
ASCII(string)	Integer representing the ASCII code value of the leftmost character of string.
BIT_LENGTH(string)	Length in bits of string.
CHAR(num)	Character that has the ASCII code value specified by num. The value of num should be between 0 and 255.
CHAR_LENGTH(string) or CHARACTER_LENGTH(string)	Length in characters of string if string is of a character data type.
CONCAT(string1, string2)	Character string that is the result of concatenating string2 to string1.
DIFFERENCE(string1, string2)	Integer value that indicates the difference between the values returned by the SOUNDEX function for string1 and string2.
INSERT(string1, start, length, string2)	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)	String equal to that in string, with all uppercase characters converted to lowercase.
LEFT(string, count)	Leftmost count characters of string.
LENGTH(string) or LEN(string)	Number of characters in string, excluding trailing blanks.
LOCATE(string1, string2[, start])	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 the optional argument 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 indicated by the value 1. If string1 is not found within string2, the value 0 is returned.
LTRIM(string)	Characters of string with leading blanks removed.
OCTET_LENGTH(string)	Returns the length in bytes of string.
POSITION(string1 IN string2)	Position of string1 in string2. The result is an exact numeric with precision of double and a scale of 0.
REPEAT(string, count)	Character string composed of string repeated count times.
REPLACE(string1, string2, string3)	Search string1 for occurrences of string2 and replace with string3.
RIGHT(string, count)	Rightmost count characters of string.
RTRIM(string)	Characters of string with trailing blanks removed.
SOUNDEX(string)	4-digit SOUNDEX code.
SPACE(count)	Character string consisting of count spaces.
SUBSTRING(string, start, length) or SUBSTR(string, start, length)	Character string that is derived from string, beginning at the character position specified by start for length characters.
UCASE(string)	String equal to that in string, but with all lowercase characters converted to uppercase.

Numeric Functions

This table lists the numeric functions:

Note:
Almost all of the numeric functions accept both numbers 1234 and string numbers '1234' as arguments, with the exceptions being the second arguments for the MOD, POWER, ROUND and TRUNCATE functions, which must be numbers.

Function	Description
ABS(num)	Absolute value of num.
ACOS(float)	Arc-cosine of float as an angle expressed in radians.
ASIN(float)	Arc-sine of float as an angle expressed in radians.
ATAN(float)	Arc-tangent of float as an angle expressed in radians.
ATAN2(float1, float2)	Arc-tangent of the x and y coordinates, specified by float1 and float2 respectively as an angle expressed in radians.
CEILING(num)	Smallest integer greater than or equal to num. The return value is of the same data type as the input parameter.
COS(float)	Cosine of float where float is an angle expressed in radians.
COT(float)	Cotangent of float where float is an angle expressed in radians.
DEGREES(num)	Number of degrees converted from num radians.
EXP(float)	Exponential value of float.
FLOOR(num)	Largest integer less than or equal to num. The return value is of the same data type as the input parameter.
LOG(float)	Natural logarithm of float.
LOG10(float)	Base 10 logarithm of float.
MOD(int1, int2)	Remainder (modulus) of int1 divided by int2.
NUM(string) or NUMBER(string)	Converts string into a number.
PI( )	Constant value of "pi" as a floating-point value. Pi is defined internally as 3.14159265358979323846264338327950288419716939937510.
POWER(num, int)	Value of num to the power of int.
RADIANS(num)	Number of radians converted from num degrees.
RAND([int])	Random floating-point value using int as the optional seed value.
ROUND(num, int)	Returns num rounded to int places right of the decimal point. If int is negative, num is rounded int places to the left of the decimal point.
SIGN(num)	Returns an indicator of the sign of num. Where: If num is less than zero, -1 is returned. If num equals zero, 0 is returned. If num is greater than zero, 1 is returned.
SIN(float)	Sine of float where float is an angle expressed in radians.
SQRT(float)	Square root of float.
TAN(float)	Tangent of float where float is an angle expressed in radians.
TRUNCATE(num, int)	Returns num truncated to int places right of the decimal point. If int is negative, num is truncated int places to the left of the decimal point.

Time and Date Functions

This table lists the time and date functions:

Note:
A date_exp can be in the escaped date format {d '2015-07-28'} or in a simple string format '2015-07-28'.

A time_exp must be in a simple string format '12:00:00'.

A timestamp_exp must be in a simple string format '2015-07-28 12:00:00'.

Function

Description

CURRENT_DATE( ) or

CURDATE( )

Current date.

CURRENT_TIME( ) or

CURTIME( )

Current local time.

CURRENT_TIMESTAMP( )

Current local date and local time as a timestamp value.

DAYNAME(date_exp)

Character string containing the name of the day for the day portion of date_exp. Only long English names are returned (e.g. Monday through Sunday).

DAYOFMONTH(date_exp)

Day of the month based on the month field in date_exp as an integer value in the range of 1 - 31.

DAYOFWEEK(date_exp)

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

DAYOFYEAR(date_exp)

Day of the year based on the year field in date_exp as an integer value in the range of 1 - 366.

EXTRACT(extract-field FROM extract-source)

Returns the extract-field portion of the extract-source.

Where:

extract-field	The extract-field argument can be one of the following keywords: YEAR, MONTH, DAY, HOUR, MINUTE or SECOND.
extract-source	The extract-source argument is a date time or interval expression.

The precision of the returned value is implementation defined. The scale is 0 unless SECOND is specified, in which case, the scale is not less than the fractional seconds precision of the extract-source field.

HOUR(time_exp)

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

MINUTE(time_exp)

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

MONTH(date_exp)

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

MONTHNAME(date_exp)

Character string containing the name of the month for the month portion of date_exp. Only long English names are returned (e.g. January through December).

NOW( )

Current date and time as a timestamp value.

QUARTER(date_exp)

Quarter in date_exp as an integer value in the range of 1 - 4, where 1 represents January 1 through March 31.

SECOND(time_exp)

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

TIMESTAMPADD(interval, integer_exp, timestamp_exp)

Returns the timestamp calculated by adding integer_exp intervals of type interval to timestamp_exp. Mmmmm

Valid values of interval include the following keywords:

SQL_TSI_DAY
SQL_TSI_HOUR
SQL_TSI_MINUTE
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_SECOND
SQL_TSI_WEEK
SQL_TSI_YEAR

Example:

The following SQL statement returns the name of each employee and his/her one year anniversary date:

SELECT NAME, {fn TIMESTAMPADD(SQL_TSI_YEAR, 1, HIRE_DATE)} FROM EMPLOYEES

If timestamp_exp is a time value and interval specifies days, weeks, months, quarters or years, the date portion of timestamp_exp is set to the current date before calculating the resulting timestamp.

If timestamp_exp is a date value and interval specifies seconds, minutes or hours, the time portion of timestamp_exp is set to 0 before calculating the resulting timestamp.

TIMESTAMPDIFF(interval, timestamp_exp1, timestamp_exp2)

Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1. Mmmm

Valid values of interval include the following keywords:

SQL_TSI_DAY
SQL_TSI_HOUR
SQL_TSI_MINUTE
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_SECOND
SQL_TSI_WEEK
SQL_TSI_YEAR

Example:

The following SQL statement returns the name of each employee and the number of years he/she has been employed:

SELECT NAME, {fn TIMESTAMPDIFF(SQL_TSI_YEAR, {fn CURDATE( )}, HIRE_DATE)} FROM EMPLOYEES

If either timestamp expression is a time value and interval specifies days, weeks, months, quarters or years, the date portion of that timestamp is set to the current date before calculating the difference between the timestamps.

If either timestamp expression is a date value and interval specifies seconds, minutes or hours, the time portion of that timestamp is set to 0 before calculating the difference between the timestamps.

WEEK(date_exp)

Week of the year based on the week field in date_exp as an integer value in the range of 1-53.

YEAR(date_exp)

Year based on the year field in date_exp as an integer value.

System Functions

This table lists the system functions:

Function	Description
CASE expression WHEN expression THEN expression [.n] [ELSE expression] END	Simple Case function. Evaluate a list of WHEN expressions for equality with the CASE expression and return the related result expression. If no WHEN expression is equal to the CASE expression, return the ELSE expression if present. If there is no ELSE expression, then return NULL.
CASE WHEN boolean THEN expression [.n] [ELSE expression] END	Searched Case function. Evaluate a list of Booleans and return the related result expression of the first Boolean in the list that is true. If no WHEN expression is equal to the CASE expression, return the ELSE expression if present. If there is no ELSE expression, then return NULL.
CAST(expression AS data_type [( integer1 [, integer2] )] )	Convert expression into data_type. Optionally specify the length or precision or precision and scale of the converted to data_type. (Added in PxPlus 2016) For details on supported data types, see Data Types.
CONVERT(expression, data_type)	Convert expression into data_type. For details on supported data types, see Data Types.
DATABASE( )	Return the current Data Source Name (DSN) description text.
IFNULL(expression1, expression2)	If expression1 evaluates to NULL, then the result of expression2 is returned; otherwise, the result of expression1 is returned.

Data Types

This table lists the data types:

Data Type

Description

SQL_BIGINT

BIGINT

Exact numeric value with precision 19 (if signed) or 20 (if unsigned) and scale 0.

SQL_BINARY [(length)]

BINARY [(length)]

Binary data of fixed length. The length can be optionally overridden using the CAST function. Binary data is returned as two characters per byte Hex representation.

Example:

"hello world" is "68656C6C6F20776F726C64".

SQL_VARBINARY [(length)]

VARBINARY [(length)]

Variable length binary data. The maximum length can be optionally overridden using the CAST function. Binary data is returned as two characters per byte Hex representation.

Example:

"hello world" is "68656C6C6F20776F726C64".

SQL_BIT

BIT

Single bit binary data; i.e. 0 or 1.

SQL_CHAR [(length)]

CHAR [(length)]

CHARACTER [(length)]

Character string of fixed length. The length can be optionally overridden using the CAST function.

SQL_VARCHAR [(length)]

VARCHAR [(length)]

CHAR VARYING [(length)]

CHARACTER VARYING [(length)]

Variable length character string. The maximum length can be optionally overridden using the CAST function.

SQL_DATE

SQL_TYPE_DATE

DATE

A calendar date in the format YYYYMMDD.

SQL_DECIMAL [(precision[, scale])]

DECIMAL [(precision[, scale])]

DEC [(precision[, scale])]

Signed, exact, numeric value with a precision of 15 and scale of 3. The precision and/or scale can be optionally overridden using the CAST function.

SQL_DOUBLE

DOUBLE

DOUBLE PRECISION

Signed, approximate, numeric value with a precision of 15.

SQL_FLOAT [(precision)]

FLOAT [(precision)]

Signed, approximate, numeric value with precision of 15. The precision can be optionally overridden using the CAST function.

SQL_INTEGER

INTEGER

INT

Exact numeric value with precision 10 and scale 0.

SQL_NUMERIC [(precision[, scale])]

NUMERIC [(precision[, scale])]

Signed, exact, numeric value with a precision of 15 and scale of 3. The precision and/or scale can be optionally overridden using the CAST function.

SQL_REAL

REAL

Signed, approximate, numeric value with a precision of 7.

SQL_SMALLINT

SMALLINT

Exact numeric value with precision of 5 and scale of 0.

SQL_TINYINT

TINYINT

Exact numeric value with precision of 3 and scale of 0.

terms: comment, SQL comment, SQL comments

Filtering by Date

Converting Date to Character

Join Syntax

Parent1 → Child1

Parent1 → Parent2 → Child1

Parent1 → Child1, Parent2 → Child1

Comments

Providex Scalar Functions

String Functions

Numeric Functions

Note: Almost all of the numeric functions accept both numbers 1234 and string numbers '1234' as arguments, with the exceptions being the second arguments for the MOD, POWER, ROUND and TRUNCATE functions, which must be numbers.

Time and Date Functions

Note: A date_exp can be in the escaped date format {d '2015-07-28'} or in a simple string format '2015-07-28'. A time_exp must be in a simple string format '12:00:00'. A timestamp_exp must be in a simple string format '2015-07-28 12:00:00'.

System Functions

Data Types

Related Pages

Note:
Almost all of the numeric functions accept both numbers 1234 and string numbers '1234' as arguments, with the exceptions being the second arguments for the MOD, POWER, ROUND and TRUNCATE functions, which must be numbers.

Note:
A date_exp can be in the escaped date format {d '2015-07-28'} or in a simple string format '2015-07-28'.

A time_exp must be in a simple string format '12:00:00'.

A timestamp_exp must be in a simple string format '2015-07-28 12:00:00'.