'variable within a case expression oracle

I have the following:

SELECT name,
       listagg(
               CASE length(TRIM(regexp_substr(lower(docs),
                       'process\s+explanation\s*:\s*(.*?)(\.\s*final activity|$)',
                                              1, 1, 'i', 1)))
                   WHEN 0 THEN
                       lower(docs)
                   ELSE
                       regexp_substr(lower(docs),
                        'process\s+explanation\s*:\s*(.*?)(\.\s*final activity|$)',
                                     1, 1, 'i', 1)
                   END, '. '
           )              AS final_doc
FROM my_table
GROUP BY
     name

I don't want to repeat the regular expression: 'process\s+explanation\s*:\s*(.*?)(\.\s*final activity|$)'. I just want to define it once and use it. Or preferrably, I don't want this entire line duplicated: regexp_substr(lower(docs), 'process\s+explanation\s*:\s*(.*?)(\.\s*final activity|$)', 1, 1, 'i', 1). How can I do that? Version is 19c

sqloracle


Solution 1:[1]

You can use Common Table expression(WITH..AS) in such a way that

WITH t AS
(
 SELECT m.*, 
        REGEXP_SUBSTR(docs,
                      'process\s+explanation\s*:\s*(.*?)(\.\s*final activity|$)',
                       1,
                       1,
                      'i',
                       1) AS rgs
   FROM my_table m
   
)
SELECT name,
       LISTAGG(CASE length(TRIM(rgs))
                 WHEN 0 THEN
                  lower(docs)
                 ELSE
                  rgs
               END,
               '. ') AS final_doc
  FROM t
 GROUP BY name

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

Related Questions