Sage 100 ProvideX SQL: Difference between revisions

From Data Islands
No edit summary
No edit summary
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,

Revision as of 21:35, 25 January 2024

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

EG 2

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

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

5. Concatonate strings

EG

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