home

MinistryPlatform

RealmRealm AccountingMinistryPlatformeGivingPDSACSConnectShepherdGrowth MethodHeadMasterRefresh WebsitesTrust CenterGo MethodMissionInsite
RealmRealm AccountingMinistryPlatformeGivingPDSACSConnectShepherdGrowth MethodHeadMasterRefresh WebsitesTrust CenterGo MethodMissionInsite

What would you like to know more about?

Show Contents

thumbnailMinistryPlatform

SQL Comparisons

Basic Comparisons

This section discusses basic SQL comparisons you can use in MinistryPlatform.

Comparisons Without Search Terms

Some comparisons check for values or missing values using the NULL keyword. NULL represents a missing value and NOT NULL represents an existing value.

The valid operators when comparing NULL are "IS" and "IS NOT."

Note: Values are never equal to NULL - not even NULL is equal to NULL. This means you can't use equality operators for comparisons to NULL, and comparisons that use equality operators always drop the NULL values. The ISNULL Function is a helpful SQL function often used to deal with this NULL limitation.

The following query returns records with birth date:

The New/Copy View dialog box. Contact ID, Display Name, and Date of Birth are in the Form Layout tab, and Date of Birth is set to NOT NULL.

The comparison in the resulting SQL looks like this:

Date_of_Birth IS NOT NULL

The following query returns records without birth dates:

The New/Copy View dialog box. Contact ID, Display Name, and Date of Birth are in the Form Layout tab, and Date of Birth is set to NULL.

The comparison in the resulting SQL looks like this:

Date_of_Birth IS NULL

Single Value Comparisons

The majority of filter types are single-value comparisons. Each of these requires a single Search Term.

The Search Term drop-down list expanded to display the operators users can use for single value comparisons, including =, >=, <=, >, <, BETWEEN, IN, and NULL

Think of these as mathematical comparisons, even for text and dates. You can compare any piece of text to another using these comparisons because the text acts like a string of numbers in which A < B < C and so on. Dates are also treated as numbers internally.

Text Comparisons

Text values are expressed in single quotes. If you leave out the quotes, the Advanced Search Tool adds them for you.

Text comparisons support exact matches or wildcards so you can match when text is "like" a search term or only when it is exact.

The Search Term drop-down list expanded to display the operators users can use for text comparisons, including =, LIKE, CONTAINS, STARTS WITH, ENDS WITH, BETWEEN, IN, and NULL

Exact Match

The exact match comparison uses equals:

Display_Name = 'Smith, John'
portal
MinistryPlatform

Last updated: October 28, 2024

Related Articles: