Search these areas:
Help
Support Forums
Product Info



Introduction
DataWeb: What's New
Creating an Application
The DataWeb Designer
Application Management
Data Tables
Data Tables
Views
-View Basics
-Converting HTML Pages to Views
-Incorporating Script in a View
-View Property Reference
-View XML Reference
-URL Parameters Reference
-SQL Expression Reference
SQL Reserved Words Reference
Templates
Components
Data Analysis
Members
Security
Importing
Exporting
FTP
JavaScript
Object Model
Full-Text Search
Domain and Email Hosting
HotBlock Modules
Account Management
Glossary
New Responsive UI Module





DataWeb Help
Support Forums
Tutorial
Script Library
Knowledge Base

Home > Support

DataWeb User's Guide

SQL Expression Reference
The following SQL functions and expressions can be used for (1) filtering and for (2) building calculated fields in data regions.

(1) Filtering with SQL Expressions

Filtering a data region

You can filter a data region by placing a SQL expression in the data region's Filter Preset property. For example, to display all records where the LastName field has the value 'Smith' or where the FirstName field has the value 'Bill', place the following SQL expression within the Filter Preset property:

   =LastName='Smith' OR FirstName='Bill'
Setting this property is equivalent to setting the filter attribute of the ws:datasource tag, as below:
   <ws:datasource filter="=LastName='Smith' OR   
     FirstName='Bill'" />
The following filter string returns all records where the value of the UnitPrice field is between 10.00 and 20.00:
   =UnitPrice BETWEEN 10.00 AND 20.00
A SQL function can be included in the filter string to perform some operation on field values. For example, the following filter string returns records where the ShippedDate field is 10 days or more after the OrderedDate field:
   =datediff(day, OrderedDate, ShippedDate) > 10
Dates can be included in the filter string as quoted strings. For example, the following filter string returns records where the value of the OrderDate field falls within February of 2000:
   =OrderDate > '1/31/98' and OrderDate < '3/1/98'
Note that date fields should be converted to strings when used with the comparison operator LIKE.
   =string(OrderDate) LIKE '1976%'
Parentheses can be used to specify precedence of operations:
   =NOT ((DATEDIFF(day, Orderdate, Requireddate) < 10) 
      OR (DATEDIFF(day, Requireddate, Shippeddate) < 10))

Filtering in script

You can include SQL expressions in filters in script. The following example uses a SQL expression to filter a Resultset object to retrieve only those records where the FirstName field has the value 'Mary' or the LastName field has the value 'Jones'.

   var filt = "LastName='Jones' OR FirstName='Mary'";
   var rs = Resultset("AddressBook", "*", filt);
The following example creates a resultset filtered for items which shipped 10 days or more after they were ordered; that is, for records where the ShippedDate field is 10 or more days after the OrderedDate field.
   var rs = Resultset("AddressBook", "*", "datediff(day, 
      OrderedDate, ShippedDate) > 10");

Filtering via the URL

To include a SQL-compatible function within the URL, use the following syntax:

   _filter = filterstring



(2) Building Calculated Fields with SQL Expressions

Calculated fields in views

The following example uses a SQL expression to control the field values for the calculated field 'expr1'.

   <ws:field name="expr1" expr="
   CASE   
     WHEN [Email] IS NULL   
       THEN 'No email provided'
     WHEN [Email] IS NOT NULL
       THEN [Email]
   END">
       <ws:caption>New Calculated Field</ws:caption>
   </ws:field>

Calculated fields in script

You can include SQL expressions in script to create a resultset that includes a calculated field. The following script outputs the value of the RowID field of a data table, and places parentheses around any value less than 3:

   var rs = Table.select("AddressBook", 
   "calcField=
   CASE 
     WHEN [RowID] < 3 
       THEN '(' + String([RowID]) + ')' 
     ELSE String([RowID]) 
   END", "*");
   while (rs.next())
       {
       response.write(rs.calcField, "<br>");
       }
Output:
  (1)
  (2)
  (3)
   4
   5
   6
   .
   .
   .

You can use the following SQL functions in filters and calculated fields:
abs(arg) Returns the absolute value of arg
left(arg, length) Returns length number of characters from the left of arg
length(arg) Returns the length of arg in characters, excluding any trailing white space
lcase(arg) Returns the text of arg in lower case
ltrim(arg) Returns the text of arg, without any leading spaces
right(arg, length) Returns length number of characters from the right of arg
rtrim(arg) Returns the text of arg, without any trailing spaces
substr(arg, position, length) Returns a substring from arg, beginning at position and extending length number of characters. The position argument is 1-based.
string(arg) Converts arg to a string. Decimal values are padded to 9 digits to the right of the decimal place. Boolean values are converted to '0' and '1'.
ucase(arg) Returns the text of arg in upper case
date() Returns the current date.
dateadd(intervaltype, interval, startdate) Returns a date with interval added to startdate. The intervaltype argument may be one of the following constants: year, quarter, month, week, day, hour, minute, second, millisecond
datediff(intervaltype, earlydate, latedate) Returns the number of intervals between earlydate and latedate. The type of interval is specified by intervaltype. The intervaltype argument may be one of the following constants: year, quarter, month, week, day, hour, minute, second, millisecond.
year(date) Returns the year portion of date
quarter(date) Returns the quarter into which date falls
month(date) Returns the month portion of date
dayofyear(date) Returns the number of the day of the year specified by date
dayofweek(date) Returns the number of the day of the week specified by date. (Sunday = 1.)
dayofmonth(date) Returns the number of the day of the month specified by date
hour(date) Returns the hour specified by date
minute(date) Returns the minute specified by date
second(date) Returns the second specified by date


The following arithmetic operators are supported: +, -, *, /.

The following comparison operators are supported: =, <, >, <=, >=, <>, !=

The following SQL keywords are supported: AND, BETWEEN, CASE, ELSE, ESCAPE, END, IN, IS, LIKE, NOT, NULL, OR, THEN, WHEN.

Note that SQL functions are not processed by DataWeb script, but are passed to the SQL database directly, so they are not JavaScript compatible.

Affiliate | Partner | Terms of Use | Privacy Policy | Contact Us | Pricing | Bring DataWeb In-House    
DataWeb, 720 North 10th Street, A #145, Renton, Washington 98057 *425-583-5970* Fax 484-770-4706* Email Us