select 
DISTINCT
       mp.[DocumentGuid]
      ,mp.[CustomerSpecifcation]
      ,mp.[Pre-CleaningMeth]
      ,mp.[ComponentSurfaceCondition]
      ,mp.[CurrentType]
      ,mp.[ParticleType]
      ,mp.[Color]
      ,mp.[Type]
      ,mp.[Natural]
      ,mp.[Consumables]
      ,mp.[Examination Area/Weld]
      ,mp.[Description-Remarks]
      ,mp.[Accept]
      ,mp.[TypeOfInsp]
from 
[dbo].[Magnetic Particle] mp
left join Answer a
on mp.CurrentType = a.QuestionGuid
WHERE
    (@CUSTOMERSPEC IS NULL OR REPLACE(LOWER(mp.[CustomerSpecifcation]),' ','') = REPLACE(LOWER(@CUSTOMERSPEC),' ',''))
AND (@PRECLEANINGMETH IS NULL OR (mp.[Pre-CleaningMeth] IN (@PRECLEANINGMETH) or '<[PARAMETER_EMPTY]>' in (@PRECLEANINGMETH)))
AND (@COMPSURFCONDN IS NULL OR REPLACE(LOWER(mp.[ComponentSurfaceCondition]),' ','') = REPLACE(LOWER(@COMPSURFCONDN),' ',''))
AND (@PARTICLETYPE IS NULL OR (mp.[ParticleType] IN (@PARTICLETYPE)  or '<[PARAMETER_EMPTY]>' in (@PARTICLETYPE)))
AND (@COLOR IS NULL OR REPLACE(LOWER(mp.[Color]),' ','') = REPLACE(LOWER(@COLOR),' ',''))
AND (@NATURAL IS NULL OR REPLACE(LOWER(mp.[Natural]),' ','') = REPLACE(LOWER(@NATURAL),' ',''))
AND (@CONSUMABLES IS NULL OR REPLACE(LOWER(mp.[Consumables]),' ','') = REPLACE(LOWER(@CONSUMABLES),' ',''))
AND (@EQUIPMENTTYPE IS NULL OR REPLACE(LOWER(mp.[Type]),' ','') = REPLACE(LOWER(@EQUIPMENTTYPE),' ',''))
AND (@CURRENTTYPE IS NULL OR (a.ValueAsString IN (@CURRENTTYPE) or '<[PARAMETER_EMPTY]>' in (@CURRENTTYPE)));

Below error is returned when the query is processed in Report Builder. I know for sure that this problem is with 'where in' clause but I unable to figure out the rectification.

0

There are 0 best solutions below