Your various matches and extracts within the if-loop may not return your expected results. Therefore I suggest to check each part separately by adding them as extra fields, like:
...
index(value,'.') as check1,
left(value,7) as check2,
...
There may some logical mistake but also that your fieldvalues are different as you think they are. They may contain some invisible chars like leading/ending spaces or even other ones. This means you need to ensure that they are like you want to query them. For it you could wrap your fields with trim() or maybe with keepchar(value, 'abc...123...') and similar funtions.
Further your if-logic may not catch all possibilities - maybe you missed here or there the else-branch and/or some condition may be never true because any earlier check were returned already a TRUE - this means the order of the checks within the if-loop needs to changed and/or some checks needs to extended to multiple checks, means something like:
if(check1 = true and check2 = true, 1, if(check1 = true, 2, 3))
and here again it might be useful to add some extra fields to see which condition returned which result.
- Marcus
