Sage 100 ProvideX SQL - Revision history

WikiSysopdi at 21:38, 25 January 2024

2024-01-25T21:38:26Z

← Older revision		Revision as of 21:38, 25 January 2024
Line 37:		Line 37:

	{FN CONCAT('<nowiki>https://',ac.URLAddress)}</nowiki> AS URLAddress,		{FN CONCAT('<nowiki>https://',ac.URLAddress)}</nowiki> AS URLAddress,

			5. Get the Month name (DateUpdated is the field name)

			EG

			{FN MONTHNAME(DateUpdated)}

			6. Substring

			EG

			{FN SUBSTR("This is a long string but we just want a bit of it",1,4)}

WikiSysopdi at 21:35, 25 January 2024

2024-01-25T21:35:25Z

← Older revision		Revision as of 21:35, 25 January 2024
Line 31:		Line 31:

	where {fn TIMESTAMPDIFF(SQL_TSI_MONTH, {fn CURDATE()}, DateCreated)} < -3		where {fn TIMESTAMPDIFF(SQL_TSI_MONTH, {fn CURDATE()}, DateCreated)} < -3

			5. Concatonate strings

			EG

			{FN CONCAT('<nowiki>https://',ac.URLAddress)}</nowiki> AS URLAddress,

WikiSysopdi at 21:34, 25 January 2024

2024-01-25T21:34:27Z

← Older revision		Revision as of 21:34, 25 January 2024
Line 9:		Line 9:

	select {FN RTRIM(CustomerNo)} as CustomerNo from AR_CustomerContact		select {FN RTRIM(CustomerNo)} as CustomerNo from AR_CustomerContact

			EG 2

			{FN LTRIM({FN RTRIM(ac.CustomerName)})} as CustomerName,

	2. Getting the curent date		2. Getting the curent date

WikiSysopdi: examples of using providex sql

2024-01-25T21:33:37Z

examples of using providex sql

New page

This page is to assist in how queries are written in ProvideX and specifically references Sage 100.

We do this with some examples.This is not definitive by any means and there may be other ways to achieve the same thing. Please contact us if you would like to provide any feedback.

1.Triming text {FN RTRIM(FIELDNAME)}

EG

select {FN RTRIM(CustomerNo)} as CustomerNo from AR_CustomerContact

2. Getting the curent date

EG

{FN CURRENT_DATE()} as acdatadatetime

3. Display a number as Money

'$' + {FN CONVERT(BalanceDue, SQL_VARCHAR )} as BalanceDue,

4. Date Comparrison.

Ref: https://learn.microsoft.com/en-us/sql/odbc/reference/appendixes/time-date-and-interval-functions?view=sql-server-ver16

a. This example return the number of months between the field "DateCreated" and the current date.

where {fn TIMESTAMPDIFF(SQL_TSI_MONTH, {fn CURDATE()}, DateCreated)} < -3