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.
1条答案
按热度按时间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