eQL - Possible to have a CASE statement with 2 WHEN clauses?

Hi Bentley and the Forum,

I would like to run some eQL for a CASE statement so that when 2 properties are used, rather than the usual one.

I would like to use Files.Copies.DocumentCopy.InDefaultVisibleScope and Approval Status and set a string value for where the different when clauses are true.

     CASE Files.Copies.DocumentCopy.InDefaultVisibleScope
     WHEN Files.Copies.DocumentCopy.InDefaultVisibleScope='Y' 
    AND ApprovalStatus= 'A'
    THEN 'Green'
    WHEN Files.Copies.DocumentCopy.InDefaultVisibleScope='Y' 
    AND WHEN ApprovalStatus= 'N'
    THEN 'Amber'
    ELSE 'Red'
    END as Ragstatus

Please could you assist with the code? Is this possible in eQL?

Many thanks,


  • This can be accomplished with nested CASE statements. First check the default visible scope condition. If 'Y' then add another case statement to examine the approval status to set 'Green' or 'Amber'. If the indefaultvisiblescope is not 'Y' the Ragstatus is always 'Red', regardless of the approval status (if I followed your logic correctly).

    CASE Files.Copies.DocumentCopy.InDefaultVisibleScope 
           WHEN 'Y' THEN 
           CASE  ApprovalStatus
           WHEN 'A' THEN 'Green' 
           WHEN 'N' THEN 'Amber' 
           ELSE 'If you need to consider status = P' END 
    ELSE 'Red'
    END Ragstatus

    If you are differentiating between status 'N' and 'P', add an "ELSE" clause to the inner CASE statement. Similarly, you can add another case statement within the outer ELSE clause if you need to check other conditions before assigning red or amber.

    Eric Rajala | Consultant


    Email: eric.rajala@cohesivegroup.com


    This is a test

    Answer Verified By: julie_beck 

Reply Children
No Data