SQL Server Refactor so if all the fields are empty then to return some value?

eeq64g8w  于 2023-06-21  发布在  其他
关注(0)|答案(1)|浏览(151)

The following code is in SQL Server, I want to know how to refactor it so if EDA.GROUP_CARGO='' (If this field is empty, it means if it isn't A neither B) then to return 'X' AS B, but it gives problem because there is already a 'B'

CREATE PROCEDURE [dbo].[MySp]                    
    @param1 DECIMAL,                    
    @param2 DECIMAL                    
AS    
WITH TMP_APCM AS (                
    SELECT EDA.ID_DOC_APCM AS ID_DOC_APCM,                  
        ESC.CONTAINER_NO AS CONTAINER_NO,                    
        CASE WHEN EDA.GROUP_CARGO = 'A' THEN 'X' ELSE '' END AS A,  
        CASE WHEN EDA.GROUP_CARGO = 'B' THEN 'X' ELSE '' END AS B,  
        CASE WHEN EDA.ADDITIONAL_CERTIFICATES = 'MOISTURE' THEN 'X' ELSE '' END AS C,  
        CASE WHEN EDA.ADDITIONAL_CERTIFICATES = 'WEATHERING' THEN 'X' ELSE '' END AS D,  
        CASE WHEN EDA.ADDITIONAL_CERTIFICATES = 'EXEMPTION' THEN 'X' ELSE '' END AS E,  
        CASE WHEN EDA.TREATMENT_CERTIFICATION = 'A' THEN 'X' ELSE '' END AS F,  
        CASE WHEN EDA.TREATMENT_CERTIFICATION = 'B' THEN 'X' ELSE '' END AS G,  
        CASE WHEN EDA.TREATMENT_CERTIFICATION = 'C' THEN 'X' ELSE '' END AS H  
    FROM EDX_DOC_APCM EDA   
    JOIN EDX_SPLITS_CONTAINERS ESC ON ESC.ID_CONTAINER = EDA.ID_CONTAINER       
    WHERE EDA.ID_SPLIT = @param1 AND EDA.ID_CERTIFICATE = @param2
)
SELECT
    APCM.ID_DOC_APCM,                           
    APCM.CONTAINER_NO,          
    APCM.A,  
    APCM.B,  
    APCM.C,  
    APCM.D,  
    APCM.E
FROM TMP_APCM APCM

I don't know if I can use Logic Operators such as OR or AND with CAST or how is the best way to Refactor this SP.

dvtswwa3

dvtswwa31#

In sql you are allowed more than one WHEN and in your context it works our perfectly in this use case.

see B. if it's empty (neither A or B) then return X if it's A, it will return false and go to the 2nd eval, then the else '' if it's B, it will return false and go to the 2nd eval, then return X

CREATE PROCEDURE [dbo].[MySp]                    
    @param1 DECIMAL,                    
    @param2 DECIMAL                    
AS    
WITH TMP_APCM AS (                
    SELECT EDA.ID_DOC_APCM AS ID_DOC_APCM,                  
        ESC.CONTAINER_NO AS CONTAINER_NO,                    
        CASE WHEN EDA.GROUP_CARGO = 'A' THEN 'X' ELSE '' END AS A,  
        CASE
            WHEN EDA.GROUP_CARGO = ''  THEN 'X' 
            WHEN EDA.GROUP_CARGO = 'B' THEN 'X' 
            ELSE '' END 
            AS B,  
        CASE WHEN EDA.ADDITIONAL_CERTIFICATES = 'MOISTURE' THEN 'X' ELSE '' END AS C,  
        CASE WHEN EDA.ADDITIONAL_CERTIFICATES = 'WEATHERING' THEN 'X' ELSE '' END AS D,  
        CASE WHEN EDA.ADDITIONAL_CERTIFICATES = 'EXEMPTION' THEN 'X' ELSE '' END AS E,  
        CASE WHEN EDA.TREATMENT_CERTIFICATION = 'A' THEN 'X' ELSE '' END AS F,  
        CASE WHEN EDA.TREATMENT_CERTIFICATION = 'B' THEN 'X' ELSE '' END AS G,  
        CASE WHEN EDA.TREATMENT_CERTIFICATION = 'C' THEN 'X' ELSE '' END AS H  
    FROM EDX_DOC_APCM EDA   
    JOIN EDX_SPLITS_CONTAINERS ESC ON ESC.ID_CONTAINER = EDA.ID_CONTAINER       
    WHERE EDA.ID_SPLIT = @param1 AND EDA.ID_CERTIFICATE = @param2
)
SELECT
    APCM.ID_DOC_APCM,                           
    APCM.CONTAINER_NO,          
    APCM.A,  
    APCM.B,  
    APCM.C,  
    APCM.D,  
    APCM.E
FROM TMP_APCM APCM

相关问题