Home

CASE structures should not have too many WHEN clauses

Description

    The rule "CASE structures should not have too many WHEN clauses" states that when using a CASE structure in PL/SQL code, it is best practice to limit the number of WHEN clauses used. This is because too many WHEN clauses can lead to code that is difficult to read and maintain, and can also lead to performance issues. It is recommended to use IF-THEN-ELSE statements instead of multiple WHEN clauses when possible.

Key Benefits

  • Reduced complexity: CASE structures should not have too many WHEN clauses to reduce complexity.
  • Better readability: Fewer WHEN clauses make the CASE structure easier to read and understand.
  • Improved performance: Fewer WHEN clauses can improve the performance of the CASE structure.

 

Non-compliant Code Example

SELECT Id,
       CASE statecode
			WHEN 101 THEN 'ABC' 
			WHEN 201 THEN 'DEF' 
			WHEN 301 THEN 'MNP' 
			WHEN 401 THEN 'PQR'
			WHEN 501 THEN 'XYZ'
			WHEN 601 THEN 'IJK'
			WHEN 701 THEN 'UVW'
			WHEN 801 THEN 'FGH'
			WHEN 901 THEN 'PLM'
			WHEN 1001 THEN 'QRE' --Non compliant code (Number of when clauses are equal or more then default defined limit 10)
			WHEN 1101 THEN 'STR' --Non compliant code (Number of when clauses are equal or more then default defined limit 10)
			Else 'Unknown'
		END
FROM cars
WHERE Id < 1000
Visual Expert 2024
 VEPLSQLRULE98