WynSQL

You can use the native SQL dialect supported by the relational database you use. For other non-relational data sources, Wyn provides WynSQL, which is a subset of SQL used to query non-relational data sources like Excel, CSV, JSON, XML, MongoDB, and OData.

Furthermore, WynSQL can be used in the following designers:

CustomSQLTable in the dataset designer.
Create an entity in the cached model designer.
SQLExpression in the dataset designer.
SQL expression used to create attributes in the cached model designer.
SQL is used to create datasets based on a DataSource in the report designer.
Custom SQL in the data query endpoint API.
PreQuery in Web JSON DataSource editor.

WynSql emulates the SQLite SQL syntax standard with the following limitations:

Supports only SELECT statements, which also support the LIMIT clause to limit the number of rows returned, e.g., SELECT * FROM Demo_OrderDetail LIMIT 5.
You can only reference columns in the GROUP BY and the ORDER BY clauses, but not expressions.
WynSQL supports groups of built-in functions that you can find below. You cannot use SQLite's built-in functions.

WynSql Function Reference

Note: If a function requires an enum value as an argument, use the literal value of the enum directly without single/double quotes.
Example: StrLike(column1, 'start%', Text)

Mathematics Functions

Function	Syntax	Description
Abs	`Abs(number)`	Returns the absolute value of the specified number
Acos	`Acos(number)`	Returns the angle (in radians) whose cosine is the specified number
Asin	`Asin(number)`	Returns the angle (in radians) whose sine is the specified number
Atan	`Atan(number)`	Returns the angle (in radians) whose tangent is the specified number
Atn2	`Atn2(y, x)`	Returns the angle (in radians) whose tangent is the quotient of two specified numbers
Ceiling	`Ceiling(number)`	Returns the smallest integer greater than or equal to the specified number
Cos	`Cos(number)`	Returns the cosine of the specified angle
Cot	`Cot(number)`	Returns the cotangent of the specified angle (in radians)
Degrees	`Degrees(radians)`	Converts radians to degrees
Exp	`Exp(number)`	Returns e raised to the specified power
Floor	`Floor(number)`	Returns the largest integer less than or equal to the specified number
Log	`Log(number)`	Returns the natural logarithm (base e) of the specified number
Log10	`Log10(number)`	Returns the base-10 logarithm of the specified number
PI	`PI()`	Returns the value of π
Power	`Power(x, y)`	Returns the specified number raised to the specified power
Radians	`Radians(degrees)`	Converts degrees to radians
Rand	`Rand()` or `Rand(maxValue)`	Returns a random floating-point number between 0 and 1, or a random integer within a specified range
Round	`Round(number)` or `Round(number, digits)`	Rounds a number to the nearest integer or specified decimal places
Sign	`Sign(number)`	Returns the sign of the specified number (-1, 0, or 1)
Sin	`Sin(number)`	Returns the sine of the specified angle
Sqrt	`Sqrt(number)`	Returns the square root of the specified number
Square	`Square(number)`	Returns the square of the specified number
Tan	`Tan(number)`	Returns the tangent of the specified angle

Date and Time Functions

Function	Syntax	Description
DateAdd	`DateAdd(EDatePart datepart, number, dateTime)`	Adds a time interval to a date
DateDiff	`DateDiff(EDatePart datepart, dateTime1, dateTime2)`	Returns the difference between two dates
DateFromParts	`DateFromParts(year, month, day)`	Creates a date from year, month, and day components
DatePart	`DatePart(EDatePart datepart, dateTime)`	Returns a specific part of a date
EOMonth	`EOMonth(dateTime, [addMonth])`	Returns the last day of the month
GetDate	`GetDate()`	Returns the current system date and time
GetUtcDate	`GetUTCDate()`	Returns the current UTC date and time
FiscalYear	`FiscalYear(dateTime, startMonthOfFiscalYear)`	Returns the fiscal year

Text Functions

Function	Syntax	Description
Concat	`Concat(string1, string2...)`	Concatenates strings
Format	`Format(value, format)`	Formats a value with the specified format
IndexOf	`IndexOf(value, search)`	Returns the position of a substring
IsNull	`IsNull(value)`	Checks if a value is null
Left	`Left(value, length)`	Returns the left part of a string
Len	`Len(value)`	Returns the length of a string
Lower	`Lower(value)`	Converts to lowercase
LTrim	`LTrim(value)`	Removes leading spaces
Replace	`Replace(value, pattern, replacement)`	Replaces occurrences of a pattern
Replicate	`Replicate(value, times)`	Repeats a string
Reverse	`Reverse(value)`	Reverses a string
Right	`Right(value, length)`	Returns the right part of a string
RTrim	`RTrim(value)`	Removes trailing spaces
SubString	`SubString(value, startIndex, length)`	Extracts a substring
ToString	`ToString(value)`	Converts to string
Trim	`Trim(value)`	Removes leading and trailing spaces
Upper	`Upper(value)`	Converts to uppercase

Other Functions

Function	Syntax	Description
Array	`Array(object1, object2...)`	Creates an array
Category	`Category(value, rangeOption, outsideRangeOption, min, max, step)`	Categorizes a value into a range
CustomCategory	`CustomCategory(value, rangeOption, categoryDefinitions)`	Custom categorization

Type Conversion Functions

Function	Syntax	Description
CBool	`CBool(value)`	Converts to a boolean
CByte	`CByte(value)`	Converts to a byte
CChar	`CChar(value)`	Converts to char
CDate	`CDate(value)`	Converts to DateTime
CDbl	`CDbl(value)`	Converts to double
CDec	`CDec(value)`	Converts to decimal
CInt	`CInt(value)`	Converts to int
CLng	`CLng(value)`	Converts to long
CShort	`CShort(value)`	Converts to short
CSng	`CSng(value)`	Converts to float
CStr	`CStr(value)`	Converts to string

Financial Functions

Function	Syntax	Description
DDB	`DDB(Cost, Salvage, Life, Period, [Factor])`	Double-declining balance depreciation
FV	`FV(Rate, NPer, Pmt, [PV], [Due])`	Future value of an annuity
IPMT	`IPmt(Rate, Per, NPer, PV, [FV], [Due])`	Interest payment for a period
NPER	`NPer(Rate, Pmt, PV, [FV], [Due])`	Number of periods for an annuity
PMT	`Pmt(Rate, NPer, PV, [FV], [Due])`	Payment for an annuity
PPMT	`PPmt(Rate, Per, NPer, PV, [FV], [Due])`	Principal payment for a period
PV	`PV(Rate, NPer, Pmt, [FV], [Due])`	Present value of an annuity
RATE	`Rate(NPer, Pmt, PV, [FV], [Due], [Guess])`	Interest rate per period
SLN	`SLN(Cost, Salvage, Life)`	Straight-line depreciation
SYD	`SYD(Cost, Salvage, Life, Period)`	Sum-of-years' digits depreciation

CASE...WHEN

WynSQL supports the Case...When the SQL statement. It can be used in the following cases:

Use Case...When, when evaluating a list of conditions, and returns one of multiple possible result expressions.

The CASE expression has two formats:

The simple CASE expression compares an expression to a set of simple expressions to determine the result.
The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument.

Format 1: Simple CASE expression: The result is determined by comparing an expression to a set of simple expressions:

  CASE input_expression 
    WHEN when_expression 
    THEN result_expression [ ...n ] 
    [ ELSE else_result_expression ] 
   END

Example: In this example, we have added a Custom SQL Table in the dataset designer using the following Case..When SQL expression

  SELECT ProductName, 
     CASE 1 
     WHEN ProductName = 'Chai' THEN  'Beverage' 
     WHEN ProductName = 'Tofu' THEN 'Dairy' 
     END as casewhen 
  From Products

case when simple expression example

When you preview, the result is as shown below.

preview simple expression

Format 2: Searched CASE expression: The result is determined by evaluating a set of Boolean expressions.

   CASE WHEN Boolean_expression 
      THEN result_expression [ ...n ] 
      [ ELSE else_result_expression ] 
   END

Example: In this example, we have added a Custom SQL Table in the dataset designer using the following Case..When SQL expression

    SELECT ProductName, SupplierID, 
            CASE WHEN UnitPrice <100 THEN 'Under 100' 
                 WHEN UnitPrice >=100 AND UnitPrice<200 THEN 'Under 200' 
                 ELSE 'Over 200' 
            END 
    FROM Products

boolean expression-searched expression example

When you preview, the result is as shown below.

preview bollean expression

Case..When Limitations

WynSQL does not support the use of subqueries in the Case...When syntax.
WynSQL does not support the use of Case...When expression in Order By.
WynSQL does not support the use of Case...When expression in Group By.