Where clauses

# Where Clauses

reviewed: 7 March 2025

Basics

Where clause are, with expressions, the scripting language and property bags, the 4th (and last) area of a special CaseMaster syntax.

Where clauses map to a large extend onto SQL where clauses but with the typical twists you may have come to expect from Casemaster.

The following shows a CaseMaster where clause in action:

set( 'numberOfActiveClients', bo.count( 'client', 'status=3' ) )

The simplest where clause has a left-hand-side, an operator and a right-hand-side. In the example above the LHS is an attribute and is compared to be equal with a literal RHS.

The following has the exact same result:

set( 'numberOfActiveClients', bo.count( 'client', '3=status' ) )

You can use an attribute only when it has a column; you cannot use dynamic attributes in a where clause.

As always, CaseMaster tries to be helpful and will cast literals to match the datatype of the attribute it is compared with. The following examples show a number of variations:

Where clause	Usage
`3=3`	All rows
`3=1`	No rows
`id=1`	Where id=1
`id="1"`	Where id=1
`id='1'`	Where id=1
`id=\`1``	Where id=1
`id="1 "`	Where id=1

Operators

The following operators are supported:

Operator	Usage	Example
`=`	Equals	id=1
		name='Dispa'
		orderDate=#3feb25#
`>`	Greater than	id>1
`>=`	Greater than or equal	id>=1
`<`	Smaller than	id<1
`<=`	Greater than or equal	id<=1
`<>`	Unequal	id<>1
`!=`	Unequal	id!=1
`%`	Starts with	name%'a'
`%%`	Contains	name%'ltd'
`!%`	Does not start with	name!%'a'
`!%%`	Does not contain	name!%%'ltd'

Note that CaseMaster does not support ends-with or does-not-ends-with.

Operands, Precedence and Negations

You can combine simple where clauses as explained in the previous paragraph into a more complex where clause by using & (meaning AND) or | (meaning OR).

set( 'c', bo.count( 'client', 'status=3 & name%"a"' ) )
set( 'c', bo.count( 'client', 'status=3 | name%"a"' ) )

You can use parentheses to force precedence of evaluation:

set( 'c', bo.count( 'client', 'status=3 & ( name%"a" | name%"b" )' ) )

You can use the exclamation mark ! to negate a sub-expression:

set( 'c', bo.count( 'client', 'status=3 & !( name%"a" | name%"b" )' ) )

Special Literals

There are a number of special literals that all start with a hash (#).

Literal	Usage	Example
`#true`	True	isActive=#true
		isActive<>#true
`#false`	False	isActive=#false
`#null`	Null	dob=#null
		dob<>#null
`#date`	Current date	dueDate<#date
`#now`	Current timestamp	cmCreatedWhen<#now

Pay special attention to the #null literal. Unlike SQL, yuo do not use is or not is, instead you simply use =, != or <>.

Boolean Functions: #isTrue(), #isFalse(), #isNotTrue(), #isNotFalse()

There are 4 boolean functions that are useful when dealing with boolean attributes that are optional (i.e. can have a null value).

Compare the following two where clauses:

set( 'c', bo.count( 'audit', 'primary=#true' ) )
// Results in
// SELECT COUNT(*) FROM "audit" WITH(NOLOCK) WHERE "audit"."primary" = 1

set( 'c', bo.count( 'audit', '#isTrue( primary )' ) )
// Results in
// SELECT COUNT(*) FROM "audit" WITH(NOLOCK) WHERE ("audit"."primary" <> 0 AND "audit"."primary" IS NOT NULL)

Note the subtle difference. You can safely use the boolean functions even when the attribute is not optional. The outcome may be more in line with your expectations if the database does contain null values.

Function	True equivalent
`#isTrue( primary )`	primary<>#false & primary<>#null
`#isFalse( primary )`	primary=#false
`#isNotTrue( primary )`	primary=#false
`#isNotFalse( primary )`	primary<>#false

Ranges and Series: #in(), #notIn(), #between(), #notBetween()

These four functions are fairly straight forward and can be used to test an attribute against a lower / upper range (#between en #notBetween) or against a fixed list of values (#in and #notIn).

bo.count( 'client', '#in( id, 1, 2 )' )
bo.count( 'client', '#notIn( id, 2, 3, 4 )' )

bo.count( 'client', '#between( id, 1, 8 )' )    
bo.count( 'order', '#notBetween( orderDate, #date, #31dec2029# )' )

Embedded Expressions: the Moustache Syntax

A powerful feature of CaseMaster where clauses is the ability to embed expressions in your where clause. This is done using the single-moustache syntax:

bo.count( 'order', 'orderDate < { addDay( today(), -50 ) }' )

In the above example, the expression inside the moustaches is addDay( today(), -50 ) and will be resolved before the where clause is applied.

Other examples:

bo.count( 'order', 'cmCreatedBy = { user() }' )
// Count orders that have been created by the current users

set( 'var', 3 )
bo.count( 'order', 'status = { [var] }' )    
// Count orders where status has the value of the variable var

Sub-selects: #exists[], #notExists[], #in[], #notIn[]

There are 4 powerful constructs (these are not functions) that can be used in where clauses on entities that have a foreign key relationship with other entities.

This is probably best explained using examples.

Client has zeto or more orders
Order has zero or more order lines
Order line is for a product
Product has product categori

bo.count( 'client', '#exists[ order ]' )
// Count clients that have an order

bo.count( 'client', '#exists[ order ] & status=3' )
// Count clients that have status 3 and that have at least one order

bo.count( 'client', '#notExists[ order ]' )
// Count clients that no orders

bo.count( 'client', 'status=3 & #exists[ order, orderStatus=2 ]' )
// Count clients that have status 3 and that have at least one order where 
// the order status=2

bo.count( 'client', '#exists[ order, #notExists[ orderLine ] ]' )
// Count clients that have at least one order without order lines

bo.count( 'client', '#exists[ order, #exists[ orderLine, #exists[ product, #exists[ productCategory, name="parts" ] ] ] ]' )    
// Count clients that have at least one order wit at least one order line 
// where the product has a category with the name parts

The difference between #exists and #in and #notExists and #notIn is merely technical and is related to the SQL that is generated. The following examples are applied to the client entity:

bo.count( 'client', '#exists[ order ]' )
// Generates the following
// SELECT COUNT(*) 
// FROM "client" WITH(NOLOCK) 
// WHERE  EXISTS (
//      SELECT 1 
//      FROM "order" WITH(NOLOCK) 
//      WHERE ("order"."client" = "client"."id") 
// )

bo.count( 'client', '#in[ order ]' )
// SELECT COUNT(*) 
// FROM "client" WITH(NOLOCK)
// WHERE "client"."id" IN (
//      SELECT "order"."client" AS "order_client"
//      FROM "order" WITH(NOLOCK)
// )

Depending on the RDBMS optimizer, the #exists can be faster than the #in but it is possible that for very complex queries (and mainly where null values are involved), the #in gives the correct result.

In practive I advice to use #exists (and #notExists) unless you have a pressing reason to use #in (and the presing reason is typically a highly advanced edge scenario).

Using Sub-selects without Foreign Key Relations (!Pitfall)

Take the following example:

bo.count( 'client', '#exists[ orderLine ]' )
// Generate SQL
// SELECT COUNT(*) 
// FROM "client" WITH(NOLOCK)
// WHERE EXISTS (
//      SELECT 1  
//      FROM "orderLine" WITH(NOLOCK)
// )

The expression (and thus the where clause) is correct but does probably not result in the desired result. This is because there is no foreign from client directly to order line nor from order line to client.

The sub-select thus is not very meaningful.

Note that the following yields an error:

bo.count( 'client', '#in[ orderLine ]' )
// Results in the error
// No FK found for sub-select IN construct 'orderLine' <- -> 'client'

Direction

Compare the following two examples:

// FK from order -> client 
bo.count( 'client', '#exists[ order ]' )

bo.count( 'order', '#exists[ client ]' )

CaseMaster will figure out how the outer and inner entity are related. This can be through a foreign key from the inner to the outer or from the outer to the inner.

Foreign Key Attributes

Sometimes Casemaster needs a bit more information to come up the correct way join two entities. Imagine a forex entity that has two foreign keys both to currency: from and to.

bo.count( 'forex', '#exists[ currency, id="USD" & #isTrue( available ) ]' )

The problem is that there are two paths between forex and currency namely forex.from -> currency and forex.to -> currency. This ambiguity can be sorted by adding the foreign attribute as a hint to the where clause.

bo.count( 'forex', '#exists[ currency:from, id="USD" & #isTrue( available ) ]' )

Aliases

You can also aliases to have more control over how sub-selects are interpreted. The alias @me will always exist and will refer to the main entity that the where clause is for. Imagine that client has an attribute introductionPeriodEndDate, any orders within the intoduction period are subject to a discount.

The following example shows how the use of the @me alias can be used to refer to an attribute of client in the where clause of the sub-select for order.

bo.count( 'client', '#exists[ order, orderDate<@me:introductionPeriodEndDate ]' )

You can also introduce your own aliases:

bo.count( 'product', '#exists[ orderLine, #exists[ @order:order, #exists[ client, @order:orderDate<@client:introductionPeriodEndDate ] ] ]' )

#sum[], #max[], #min[], #avg[]

These constructs (again, no functions hence the square brackets rather than parentheses) can only be used on entities that have a foreign key connection with another entity.

Construct	Usage	Example
`#sum[]`	Return sum of a numeric attribute (or null)	`#sum[ order, netAmount, status=3 ] > 100`
`#max[]`	Return max (or null)	`#max[ order, netAmount ] > 100`
`#min[]`	Return min (or null)	`#min[ order, vat ] = 0`
`#avg[]`	Return average of a numeric attribute (or null)	`#avg[ order, vat, status=3 ] > 50`

Special Functions: #soundex, #jsonValue

Soundex is currently only supported for SQL Server and allow you to filter on the soundex value of a string. See Google / ChatGPT for the details on the soundex algorithm.

The following example shows how soundex can be used. Note that soundex can only be used meaningfully with the operands = and <>.

bo.count( 'client', '#soundex( name ) = { soundex( "smith" ) }' )
bo.count( 'client', '#soundex( name ) = { soundex( "smyth" ) }' )

The jsonValue function is designed for use on attributes that contain JSON (dataType: dataType.JSON). A typical use of JSON values is for multi-language attributes.

The following shows the raw data in the database:

Id	Name
4	{ "EN": "Single", "AR": "أعزب" } "
5	{ "EN": "Married", "AR": "متزوج/ة" }
6	{ "EN": "Divorced", "AR": "منفصل/ة" }
7	{ "EN": "Widow", "AR": "أرمل\ة" }

You can use the #jsonValue function as follows:

bo.count( 'rd/cd/maritalStatus', '#jsonValue( name, "EN" ) = "single" ' )

Quick Search: #qsearch()

See here.

Conditions: #is()

See here.

Disconnected Where Clauses

There are (advanced) scenarios where a complex where clause cannot be used as-is. This is because the where clause contains references to the specific runtime context that will cause the where clause to fail without the required context.

Take the following example:

bo.count( 'client', 'cmCreatedBy={ user() } & postcode={ [postcode] }' )

Now imagine that you want to include this where clause in Javascript so you can call a service from within the Javascript and get a count. The problem is that once the where clause hits the service, the context of that service is completely different from the runtime context when the whereclause was first created. The user() may be different and the variable [postcode] is probably not set.

The solution is to disconnect the where clause from the context:

set( 'client', bo.create( 'client' ) )

set( 'whereClause', bo.disconnectWhereClause( [client], 'cmCreatedBy={ user() }' ) )

response.write( [whereClause] )

// Displays
// cmCreatedBy=2

<End of document>