the code specifically I am referring to is:
AND (
(','||to_char('~[gpv:lt]')||',' LIKE '%,' || to_char(log.logtypeid) || '.' ||
CASE WHEN log.subtype is null
THEN ' '
ELSE log.subtype
END || ',%')
OR (','||to_char('~[gpv:lt]')||',' LIKE '%,' || to_char(log.logtypeid) || '.-1,%')
OR (to_char(log.logtypeid) LIKE
CASE
WHEN to_char('~[gpv:lt]') = '-1'
THEN '%'
ELSE ','||to_char('~[gpv:lt]')||','
END)
)
Any clarification would be great. Thank you!
Let's start with this expression:
(','||to_char('~[gpv:lt]')||',' LIKE '%,' || to_char(log.logtypeid) || '.' ||
CASE WHEN log.subtype is null
THEN ' '
ELSE log.subtype
END || ',%')
It is an example of this idiom:
','||a||',' LIKE '%,'||b||',%'
in which a is your lt parameter, and b a "type dot subtype" string. This comparison can be used whenever you have an a string that is a comma-separated list of values, and a b string that is a single value, and you want to know whether the list a contains the value b.
To see why it's written that way, first look at this attempt to do it more simply:
a LIKE '%'||b||'%'
We take b, put a wildcard at the front and back, and match a. This will be true if a is for example 1,2,3 and b is 2. It will also unfortunately be true if a is 12,34,56 and b is 2. LIKE doesn't do comma-separated list parsing, just string matching.
So next you might try this:
a LIKE '%,'||b||',%'
Now if b is 2, the pattern is %,2,% - which will match any string containing ,2, so it is true for a=1,2,3 and false for a=12,34,56. Unfortunately it's also false for a=2,3,4 because the 2 doesn't have a comma before it and false for a=0,1,2 because the 2 doesn't have a comma after it.
For the next improvement there are 2 ways to go. You can use separate pattern cases to match b at the beginning, middle, and end of a (if you go this way, using a regular expression will help make it readable!)
The other way is to modify a to match the existing pattern. We didn't match 0,1,2 or 2,3,4 because the first element of the list and the last element of the list aren't surrounded by commas. But if we add a comma to the beginning of a before matching, then the first element of the list will be surrounded by commas! And add another comma to the end of a to make sure the last element is also surrounded by commas.
','||a||',' LIKE '%,'||b||',%'
Now when a is 0,1,2 and b is 2, the LIKE expression becomes:
',0,1,2,' LIKE '%,2,%'
which is a match! The first wildcard absorbs the ,0,1 and the ,2, is found. The last wildcard matches a zero-length substring at the end, which is allowed.