Use CASE Expression in the Database Lookup Query

The database lookup query supports the use of CASE expression. The following scenario demonstrates the use of CASE expression in the database lookup query.

Scenario

Consider a work order - job scenario where the supervisor wants to identify all the "Blending" and "Pre-mixing" jobs with the job quantity rejected greater than 5. 

JOB table schema

Follow the steps given below to achieve this scenario.

Steps to achieve the scenario

1. Create a database lookup, say JobQtyRejectedLookup.

   A CASE expression is added to the query to achieve this scenario. The query is as follows:

   Select wo_id,oper_id,seq_no,job_desc,item_id,qty_rejected, 

   Case

   When job_desc='Blending' and qty_rejected>5 then 1

   When job_desc='Pre-mixing' and qty_rejected>5 then 2

   Else 3

   End as Job_Qty_Rejected

   from job

   Note: Ensure that the columns being used in the CASE expression are a part of the SELECT clause. For example, in the above query job_desc and qty_rejected are a part of the SELECT clause. The column Job_Qty_Rejected cannot be used as a part of display column or a value column.

2. Click Validate & Preview. The column Job_Qty_Rejected is displayed which helps the user to identify the Blending and Pre-mixing jobs with quantity rejected greater than 5.