CASE Clause

Use the CASE clause within the SELECT clause to add conditions to your ADQL query. It helps you to transform data with the specified condition. This clause supports aggregation and bucketing functions.

Syntax

SELECT CASE WHEN (condition) THEN (action 1) ELSE (action 2) END FROM event_type
You can specify more than one conditions in the CASE clause:
SELECT CASE WHEN (condition 1) THEN (action 1) 
	WHEN (condition 2) THEN (action 2)
	WHEN (condition 3) THEN (action 3)
	ELSE (action 4) END FROM event_type

Examples using Metric Functions

You can use metric functionsin the CASE clause for aggregations. The following are some examples with count and average aggregation functions:

Count the Transactions based on User Experience in Applications

The following query shows the number of transactions with normal and other user experiences in the applications.
SELECT application, CASE WHEN userExperience = "NORMAL" THEN count(userExperience) AS normalTransactionsCount ELSE count(userExperience) AS otherTransactionsCount END FROM transactions
The results are displayed under the following columns:
  • Application
  • normalTransactionsCount
  • otherTransactionsCount

View the User Experience Values and the Total Transactions Count in Applications

For applications, the following query shows user experience values for normal and other transactions, along with the total transaction count. The total count is displayed only once for each application.
SELECT application, CASE WHEN userExperience = "NORMAL" THEN userExperience AS userExperienceValues ELSE userExperience AS otherUserExperienceValues END, count(userExperience) AS totalTransactionsCount FROM transactions
Note: If the WHEN condition fails, the value for that field is displayed as null.

View the Application's Normal and Other Transactions Count

The following query shows the applications and transaction name along with the counts for normal and other transactions. 
SELECT application, transactionName, CASE WHEN userExperience = "NORMAL" THEN count(userExperience) AS normalTransactionsCount ELSE count(userExperience) AS otherTransactionsCount END FROM transactions
The results are displayed under the following columns:
  • Application
  • Transaction Name
  • normalTransactionsCount
  • otherTransactionsCount

View the Count for each User Experience Values

This query shows the total transaction count and the user experience values along with their respective counts.
SELECT count(*), userExperience, CASE WHEN userExperience = "NORMAL" THEN count(userExperience) AS normalTransactionsCount ELSE count(userExperience) AS otherTransactionsCount END FROM transactions
View the Count for each User Experience Values

View the Transaction Counts for Applications having High Response Time

This query shows the count of normal and other transactions that are having the response time greater than 10.
SELECT responseTime, CASE WHEN userExperience = "NORMAL" THEN count(userExperience) AS normalTransactionsCount ELSE count(userExperience) AS otherTransactionsCount END FROM transactions WHERE responseTime > 10
The results are displayed in the following columns:
  • Response Time
  • normalTransactionsCount
  • otherTransactionsCount

Calculate Average Response Time for the User Experience Transactions

This query shows the applications having normal, slow, and other user experience transactions with their average response times.
SELECT application, CASE WHEN userExperience="NORMAL" THEN avg(responseTime) AS normalTransactionsAvgResponseTime WHEN userExperience="SLOW" THEN avg(responseTime) AS slowTransactionsAvgResponseTime ELSE avg(responseTime) AS otherTransactionsAvgResponseTime END FROM transactions
The results are displayed under the columns:
  • Application
  • normalTransactionsAvgResponseTime
  • slowTransactionsAvgResponseTime
  • otherTransactionsAvgResponseTime

Examples using without Metric Functions

The following are some examples without metric functions:

View User Experience Values with their Response Times

The following query shows the user experience values for normal and other transactions with their response times.
SELECT CASE WHEN userExperience = "NORMAL" THEN userExperience AS userExperienceValues ELSE userExperience AS otherUserExperienceValues END, responseTime FROM transactions
Note: If the WHEN condition fails, the value for that field is displayed as null.
The results are displayed under the columns:
  • userExperienceValues
  • otherUserExperienceValues
  • Response Time

View User Experience Values that are not NULL with their Response Times

The following query shows the user experience values that are null for normal and other transactions with their response times.
SELECT CASE WHEN userExperience = "NORMAL" THEN userExperience AS userExperienceValues ELSE userExperience AS otherUserExperienceValues END, responseTime FROM transactions WHERE userExperience IS NOT NULL
Note: If the WHEN condition fails, the value for that field is displayed as null.
The results are displayed under the columns:
  • userExperienceValues
  • otherUserExperienceValues
  • Response Time

Statements after the CASE Clause

The CASE clause applies conditional logic to compute specific metrics and cannot nest separate metrics defined outside the clause. Below are valid ADQL queries, with results displayed in the specified order:

Variation 1: 
SELECT application, 
	CASE
		WHEN userExperience = "NORMAL" THEN count(userExperience) AS normalTransactionsCount
		ELSE count(userExperience) AS otherTransactionsCount
	END,
	count(*)
FROM transactions
The result is displayed in the following order:
  • Application
  • normalTransactionCount
  • otherTransactionCount
  • count
Variation 2: 
SELECT application,
	count(*),
	CASE 
		WHEN userExperience = "NORMAL" THEN count(userExperience) AS normalTransactionsCount
		ELSE count(userExperience) AS otherTransactionsCount
	END
FROM transactions
The result is displayed in the following order:
  • Application
  • count
  • normalTransactionCount
  • otherTransactionCount
In the above examples,
  • The CASE block is used for conditional logic to compute specific metrics like normalTransactionsCount or otherTransactionsCount.
  • The count(*) is a separate metric that reflects the total count of all transactions grouped by application.

Limitations

The MAUI Agent has the following limitations for which crash data is reported:

  • Reported: Managed crashes - crashes that occur on .NET Runtime).
  • Not reported: Native crashes - crashes that occur on Objective-C runtime.