NOT IN condition, it is not working. I have these records in the table

so it should add an error for the 2nd value..but not working pls help. below code is adding errors for 1 and 3rd lines also.

SELECT p.proj_comp_no AS primarykey,
       p.proj_comp_id AS proj_comp_id,
       pr.proj_id     AS proj_id,
       p.proj_no,       
       CASE
         WHEN p.proj_comp_id NOT IN ('5207-%',
                                     '1051-%',
                                     '1050-%',
                                     '1048-%',
                                     '1049-%',
                                     '2006-%',
                                     '5203-%',
                                     '1055-%',
                                     '1056-%',
                                     '1054-%') and p.proj_no NOT like (1581)         
         THEN
          '1TAG_ID does not start with project prefix, pls add project prefix(5207-, 1049-,1048)'       
         ELSE
          'NO ERROR'
       END AS tag_ID_ERROR,       
       p.descr AS descr
  FROM proj_comp p
  LEFT JOIN std_mat_cls l
    ON l.std_mat_cls_no = p.std_mat_cls_no
  LEFT JOIN std_mat b
    ON p.proj_comp_id = b.std_mat_id
  LEFT JOIN proj pr
    ON p.proj_no = pr.proj_no
 WHERE (SELECT m.mat_stat_ext_id
          FROM mat_stat_ext m
         WHERE p.mat_stat_ext_no = m.mat_stat_ext_no) IN ('N', 'P')
   AND b.std_mat_id IS NULL

***    

CodePudding user response：

That must be because IN isn't appropriate in this context. You should switch to something else, e.g. separate LIKE conditions:

No:

WHEN p.proj_comp_id NOT IN ( '5207-%', '1051-%', '1050-%', etc.

Yes:

 WHEN p.proj_comp_id NOT LIKE '5207-%'
  AND p.proj_comp_id NOT LIKE '1051-%'
  AND p.proj_comp_id NOT LIKE '1050-%'
  etc.

CodePudding user response：

The other way to test this could be solved using the InStr() function...

WHEN 
    InStr('5207-, 1051-. 1050-, 1048-, 1049-, 2006-, 5203-, 1055-, 1056-, 1054-', SubStr(p.proj_comp_id, 1, 5)) = 0