QBCase

Overview

The QBCase class is a utility for constructing SQL CASE expressions within a QBSelect query. It allows for dynamic conditional logic by specifying WHEN clauses and an optional ELSE clause to handle unmatched conditions. This enables advanced query customizations, such as transforming values or applying conditional calculations.

The when method adds conditions to the CASE expression, while the else method sets the default value for cases where no conditions are satisfied. The parent and root properties provide access to the query's parent table clause or the root query, allowing seamless integration with other query builder components.

For more information about constructing and executing queries, refer to QBSelect section of this documentation.

Properties Summarized

Type	Name	Summary
QBTableClause	parent	Get query builder parent table clause, this may be a query or a join clause.
QBSelect	root	Get query builder parent.

Methods Summarized

Type	Name	Summary
QBGenericColumn	else(value)	Set the return value to use when none of the when-clauses conditions are met.
QBCaseWhen	when(condition)	Add a when-clause to the case searched expression.

Properties Detailed

parent

Get query builder parent table clause, this may be a query or a join clause.

Type QBTableClause

Sample

var query = datasources.db.example_data.person.createSelect();
	query.where.add(query.joins.person_to_parent.joins.person_to_parent.columns.name.eq('john'))
	foundset.loadRecords(query)

root

Get query builder parent.

Type QBSelect

Sample

var subquery = datasources.db.example_data.order_details.createSelect();

	var query = datasources.db.example_data.orders.createSelect();
	query.where.add(query
		.or
			.add(query.columns.order_id.not.isin([1, 2, 3]))

			.add(query.exists(
					subquery.where.add(subquery.columns.orderid.eq(query.columns.order_id)).root
			))
		)

	foundset.loadRecords(query)

Methods Detailed

else(value)

Set the return value to use when none of the when-clauses conditions are met.

Parameters

• Object value The value.

Returns: QBGenericColumn A QBColumn that defines the value to return if none of the when clauses are satisfied.

Sample

var query = datasources.db.example_data.order_details.createSelect();

// case expressions can be added to the result of the query
	query.result.add(query.case.when(query.columns.quantity.ge(1000)).then('BIG').else('small'));

 // they can also be used in conditions
	query.where.add(query.case
		.when(query.columns.discount.gt(10)).then(50)
		.when(query.columns.quantity.le(20)).then(70)
		.else(100)
	.multiply(query.columns.unitprice).lt(10000));

when(condition)

Add a when-clause to the case searched expression.

Parameters

• QBCondition condition The condition.

Returns: QBCaseWhen A QBCaseWhen instance, allowing you to specify the then value for the provided when condition.

Sample

var query = datasources.db.example_data.order_details.createSelect();

// case expressions can be added to the result of the query
	query.result.add(query.case.when(query.columns.quantity.ge(1000)).then('BIG').else('small'));

 // they can also be used in conditions
	query.where.add(query.case
		.when(query.columns.discount.gt(10)).then(50)
		.when(query.columns.quantity.le(20)).then(70)
		.else(100)
	.multiply(query.columns.unitprice).lt(10000));

---