In a CASE statement, can you store WHEN subquery result for use in the THEN output?-CodePudding

I have a report outputting the results of a query which was designed to provide links to a webpage:

SELECT 
a, 
b, 
c, 
'string' || 
(SELECT sso_code 
       FROM men_sso 
       WHERE sso_parm LIKE '%URL_LINK~' || ipp_code || '%' 
       ORDER BY sso_cred, sso_cret 
       FETCH FIRST 1 ROWS ONLY
) AS URL
FROM men_ipp

This works well, but I was asked to amend it so that if the records needed to generate the URL were missing (ie. sso_code can't be retrieved), it outputs a warning message instead of the subquery output.

Since there's always going to be a string of a set length (6 characters in this example), my solution was to create a CASE statement which is evaluating the length of the subquery output, and if the answer is greater than 6 characters it returns subquery result itself, otherwise it returns a warning message to the user. This looks like:

SELECT 
a, 
b, 
c, 
CASE 
  WHEN 
    LENGTH('string' || 
       (SELECT sso_code 
       FROM men_sso 
       WHERE sso_parm LIKE '%IPP_URL_LINK~' || (ipp_code) || '%' 
       ORDER BY sso_cred, sso_cret 
       FETCH FIRST 1 ROWS ONLY)  
     ) > 6
  THEN 
    ('string' || 
       (SELECT sso_code 
       FROM men_sso 
       WHERE sso_parm LIKE '%IPP_URL_LINK~' || (ipp_code) || '%' 
       ORDER BY sso_cred, sso_cret 
       FETCH FIRST 1 ROWS ONLY)
  ELSE 'warning message'
END AS URL
FROM men_ipp

The statment works fine, however the processing time is nearly doubled because it's having to process the subquery twice. I want to know if there's any way to store the result of the subquery in the WHEN, so it doesn't need to be run a second time in the THEN? eg. as a temporary variable or similar?

I've tried to declare a variable like this:

DECLARE URLLINK NVARCHAR(124);
SET URLLINK = 'string' || 
(SELECT sso_code 
       FROM men_sso 
       WHERE sso_parm LIKE '%URL_LINK~' || ipp_code || '%' 
       ORDER BY sso_cred, sso_cret 
       FETCH FIRST 1 ROWS ONLY
)

However this causes the query to error saying the it Encountered the symbol "https://evision.dev.uwl.tribalsits.com/urd/sits.urd/run/siw_file_load.sso?" when expecting one of the following: := . ( @ % ; not null range default character

CodePudding user response：

Use a CTE.

with temp as
  (SELECT sso_code
       FROM men_sso 
       WHERE sso_parm LIKE '%IPP_URL_LINK~' || (ipp_code) || '%' 
       ORDER BY sso_cred, sso_cret 
       FETCH FIRST 1 ROWS ONLY
  )
select a, b, c,
  case when sso_code is null then 'warning message'
       else 'string' || sso_code
  end as url
from men_ipp full outer join temp on 1 = 1;

CodePudding user response：

You can use NULLIF to make the result null if it is "string" (i.e., you appended nothing to it from your subquery). Then use NVL to convert to the warning message. Something like this:

SELECT 
a, 
b, 
c, 
nvl(nullif(
'string' || 
       (SELECT sso_code 
       FROM men_sso 
       WHERE sso_parm LIKE '%IPP_URL_LINK~' || (ipp_code) || '%' 
       ORDER BY sso_cred, sso_cret 
       FETCH FIRST 1 ROWS ONLY),'string'),'warning message')
FROM men_ipp

CodePudding user response：

Use a sub-query:

SELECT a, 
       b,
       c,
       CASE 
       WHEN LENGTH(sso_code) > 6
       THEN sso_code
       ELSE 'warning message'
       END AS URL
FROM   (
  SELECT a,
         b,
         c,
         'string' || 
         ( SELECT sso_code 
           FROM   men_sso 
           WHERE  sso_parm LIKE '%IPP_URL_LINK~' || ipp_code || '%' 
           ORDER BY sso_cred, sso_cret 
           FETCH FIRST 1 ROWS ONLY ) AS sso_code
  FROM   men_ipp
)