Switch with custom functions giving #ERROR

[bogzla]

The following query is giving me issues:

SELECT
Switch([A.SalesNotes] like '*[C-D][0-9][0-9][0-9]*',mid([A.SalesNotes],FindIt([A.SalesNotes],'[C-D][0-9][0-9][0-9]'),4),[A.SalesNotes] like '*[C-D][0-9][0-9]*',mid([A.SalesNotes],FindIt([A.SalesNotes],'[C-D][0-9][0-9]'),3)) as 'Master Batch', A.WorksOrderNumber + '/A1' as WorksOrderNo
FROM         Production_WorksOrder AS A
WHERE     (A.SalesNotes LIKE '*[C-D][0-9][0-9][0-9]*' OR A.SalesNotes LIKE '*[C-D][0-9][0-9]*')
ORDER BY 'Master Batch' DESC, A.WorksOrderNumber DESC

I'm looking for and showing codes like C01, C012, D01, D012 which have a variable position with the SalesNotes field
FindIt is a custom function that returns the position using regexp.
In the above case C012 and D012 style codes are displayed fine but everything else shows #ERROR

interestingly, if I strip it down to the following:

SELECT
Switch([A.SalesNotes] like '*[C-D][0-9][0-9][0-9]*',FindIt([A.SalesNotes],'[C-D][0-9][0-9][0-9]'),[A.SalesNotes] like '*[C-D][0-9][0-9]*',FindIt([A.SalesNotes],'[C-D][0-9][0-9]')) as 'Master Batch', A.WorksOrderNumber + '/A1' as WorksOrderNo
FROM         Production_WorksOrder AS A
WHERE     (A.SalesNotes LIKE '*[C-D][0-9][0-9][0-9]*' OR A.SalesNotes LIKE '*[C-D][0-9][0-9]*')
ORDER BY 'Master Batch' DESC, A.WorksOrderNumber DESC

Then all positions returned by FindIt for all cases are displayed fine. This leads me to think there is something up in the way switch and mid are behaving but I cannot fathom what is going on...

any help gratefully recieved!

[ranman256]

Instead of using switches,
use a lookup table joined to your table to convert, or a custom function.

[bogzla]

You know it never occured to me to put the whole thing in a function. Probably a bit blinkered as converting it from a SQL server CASE query.

I would still be interested to understand what caused the error if anyone has some insight?

cheers!