How to optimioze the Case statement for a column. Please help

Question

CREATE TABLE MYCheck1Temp1 ( NDS VARCHAR(20), OPTCODE VARCHAR(20), OPTCODEDESC varchar(20), CODECHECK Varchar(20), SRCCODE VARCHAR(20), VALID_RECORD VARCHAR(1))

SELECT DISTINCT 'MILLER' AS NDS
, TRIM(STG.OPTCODE) AS SRC_CD
, UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION
, TRIM(STG.OPTCODE) AS CON_CODE
, UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC
, 'HEALTHCARE' AS DOM_NAME
FROM MYCheck1Temp1 STG
WHERE STG.VALID_RECORD = 'Y' AND SRCCODE <> ''
AND TRIM(STG.CODECHECK) = 'MILT'

UNION ALL

SELECT DISTINCT 'ROMAN BOWLER' AS NDS
, TRIM(STG.OPTCODE) AS SRCCODE
, UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION
, TRIM(STG.OPTCODE) AS CON_CODE
, UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC
, 'HEALTHCARE' AS DOM_NAME
FROM MYCheck1Temp1 STG
WHERE STG.VALID_RECORD = 'Y' AND SRCCODE <> ''
AND TRIM(STG.CODECHECK) = 'ROMB'

UNION ALL

SELECT DISTINCT 'LOANER' AS NDS
, TRIM(STG.OPTCODE) AS SRCCODE
, UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION
, TRIM(STG.OPTCODE) AS CON_CODE
, UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC
, 'HEALTHCARE' AS DOM_NAME
FROM MYCheck1Temp1 STG
WHERE STG.VALID_RECORD = 'Y' AND SRCCODE <> ''
AND TRIM(STG.CODECHECK) = 'BANK'

UNION ALL

SELECT DISTINCT 'MOVABLES' AS NDS
, TRIM(STG.OPTCODE) AS SRCCODE
, UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION
, TRIM(STG.OPTCODE) AS CON_CODE
, UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC
, 'HEALTHCARE' AS DOM_NAME
FROM MYCheck1Temp1 STG
WHERE STG.VALID_RECORD = 'Y' AND SRCCODE <> ''
AND TRIM(STG.CODECHECK) = 'MORT'

like this I have 150 UNION ALLS referring the same table causing much time to execte. here i am trying to optimize this code..

first thing i did was

STEP1)
created a temp table that trims and loads all data (with upper, trims) in one shot rather at every query.

CREATE TABLE #abc AS
SELECT DISTINCT
TRIM(STG.OPTCODE) AS SRCCODE
, UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION
, TRIM(STG.OPTCODE) AS CON_CODE
, UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC
, TRIM(STG.CODECHECK) AS CODECHECK
FROM MYCheck1Temp1 STG
WHERE STG.VALID_RECORD = 'Y' AND SRCCODE <> ''

STEP2)
here i would like to include a where clause for all the CODECHECK COLUMNS but the question here is how to make the NDS column depend on the CODECHECK column
which means below query i can make up NDS with CASE STATEMENT (about 150) is there anyotherway to do this NDS column with other temp tables using joins etc (or any other Idea)? to optimize this PLEASE...

SELECT DISTINCT
CASE WHEN CODECHECK = 'MORT' THEN 'MOVABLES'
WHEN CODECHECK = 'BANK' THEN 'LOANER'
WHEN CODECHECK = 'ROMB' THEN 'ROMAN BOWLER'
WHEN CODECHECK = 'MILT' THEN 'MILLER'
ELSE NULL END AS NDS
, TRIM(STG.OPTCODE) AS SRCCODE
, UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION
, TRIM(STG.OPTCODE) AS CON_CODE
, UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC
, TRIM(STG.CODECHECK) AS CODECHECK
FROM MYCheck1Temp1 STG
WHERE CODECHECK IN
(
'MORT',
'BANK',
'ROMB',
'MILT'
)

please help me out here to optimze it.. Thanks in advance.

Answer 1

Create a mapping table and just do it once.

DECLARE @mapping TABLE (NDS VARCHAR(20), CODECHECK VARCHAR(20))

INSERT INTO @mapping (NDS, CODECHECK) VALUES
('MILLER','MILT'),
('ROMAN BOWLER','ROMB'),
('LOANER','BANK'),
('MOVABLES','MORT')

SELECT DISTINCT m.NDS AS NDS
, TRIM(STG.OPTCODE) AS SRCCODE
, UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION
, TRIM(STG.OPTCODE) AS CON_CODE
, UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC
, 'HEALTHCARE' AS DOM_NAME
FROM MYCheck1Temp1 STG
    INNER JOIN @mapping m
    ON m.CODECHECK = TRIM(STG.CODECHECK)
WHERE STG.VALID_RECORD = 'Y' AND SRCCODE <> ''

Answer 2

Hi @aswaniD-8992n

Please refer to:

    SELECT DISTINCT 'MOVABLES' NDS, TRIM(STG.OPTCODE) AS SRCCODE  
    , UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION  
    , TRIM(STG.OPTCODE) AS CON_CODE  
    , UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC  
    , TRIM(STG.CODECHECK) AS CODECHECK  
    FROM MYCheck1Temp1 STG  
    WHERE CODECHECK = 'MORT'  
    union all  
    SELECT DISTINCT 'LOANER' NDS, TRIM(STG.OPTCODE) AS SRCCODE  
    , UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION  
    , TRIM(STG.OPTCODE) AS CON_CODE  
    , UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC  
    , TRIM(STG.CODECHECK) AS CODECHECK  
    FROM MYCheck1Temp1 STG  
    WHERE CODECHECK = 'BANK'  
    union all  
    SELECT DISTINCT 'ROMAN BOWLER' NDS, TRIM(STG.OPTCODE) AS SRCCODE  
    , UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION  
    , TRIM(STG.OPTCODE) AS CON_CODE  
    , UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC  
    , TRIM(STG.CODECHECK) AS CODECHECK  
    FROM MYCheck1Temp1 STG  
    WHERE CODECHECK = 'ROMB'  
    union all  
    SELECT DISTINCT 'MILLER' NDS, TRIM(STG.OPTCODE) AS SRCCODE  
    , UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION  
    , TRIM(STG.OPTCODE) AS CON_CODE  
    , UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC  
    , TRIM(STG.CODECHECK) AS CODECHECK  
    FROM MYCheck1Temp1 STG  
    WHERE CODECHECK = 'MILT'  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.