'not in condition not working with Case statement PLSQL
NOT IN condition, it is not working. I have these records in the table
| proj_comp_id | proj_no |
|---|---|
| 1050-WE-0005 | 8661 |
| TOGG0000001 | 1581 |
| 5207-00GAC01AA502 | 8662 |
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
***
Solution 1:[1]
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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | d r |

