Pervasive v11: Remove optional paramter in where clause

Question

I am using Pervasive V11. In the following query i have an optional parameter called ModelYear. I would like to exclude it from running in the WHERE clause when it is passed as NULL.

SELECT  Year,Make,Style,Model,Color1,VIN
                       FROM
                       Vehicles
                      WHERE

                         (VIN ='{VIN}') AND (xyz = '{xyz}') AND 
                       (COALESCE(NULLIF('{ModelYear}', ''), Year))

Answer 1

You could try:

SELECT  
   Year,Make,Style,Model,Color1,VIN
FROM
   Vehicles
WHERE
   (VIN ='{VIN}') AND 
   (xyz = '{xyz}') AND 
   (ISNULL('{ModelYear}','') = Year END)

From the docs:

ISNULL (exp, value): Replaces NULL with the value specified for value. Exp is the expression to check for NULL. Value is the value returned if exp is NULL. Exp is returned if it is not NULL. The data type of value must be compatible with the data type of exp.

NULLIF (exp1, exp2): NULLIF returns exp1 if the two expressions are not equivalent. If the expressions are equivalent, NULLIF returns a NULL value.