Section 2
SELECT base.*,
-- Evaluate complex conditions ONLY ONCE
CASE
WHEN base.HRStatus_c = 'A'
AND base.EmploymentCountry_c != 'AT'
AND UPPER(base.SalesRP_c) = 'Y'
AND TO_NUMBER(base.HRTI_c) != 0
AND TO_NUMBER(base.BBOfHRTI_c) BETWEEN -5 AND 5
AND TO_NUMBER(base.BeginningBalance_c) != 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 1
WHEN base.HRStatus_c = 'L'
AND UPPER(base.SalesRP_c) = 'Y'
AND TO_NUMBER(base.CED_c) != 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 2
WHEN base.HRStatus_c = 'T'
AND base.EmploymentCountry_c != 'AT'
AND UPPER(base.SalesRP_c) = 'Y'
AND TO_NUMBER(base.CEDUSD_c) BETWEEN 0 AND 10
AND base.IncentiveType_c = 'COMMISSION'
THEN 3
WHEN base.HRStatus_c = 'T'
AND UPPER(base.SalesRP_c) = 'Y'
AND TO_NUMBER(base.CEDUSD_c) BETWEEN 0 AND 10
AND base.IncentiveType_c = 'BONUS'
THEN 4
WHEN base.HRStatus_c = 'T'
AND UPPER(base.SalesRP_c) = 'Y'
AND TO_NUMBER(base.CEDUSD_c) > 10
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 5
WHEN base.HRStatus_c = 'T'
AND base.EmploymentCountry_c = 'RO'
AND UPPER(base.SalesRP_c) = 'Y'
AND TO_NUMBER(base.CED_c) < 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 6
WHEN base.HRStatus_c = 'T'
AND base.EmploymentCountry_c != 'RO'
AND UPPER(base.SalesRP_c) = 'Y'
AND TO_NUMBER(base.CED_c) < 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 7
WHEN base.HRStatus_c = 'A'
AND (UPPER(base.SalesRP_c) = 'N' OR base.SalesRP_c IS NULL)
AND TO_NUMBER(base.CED_c) > 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 8
WHEN base.HRStatus_c = 'A'
AND base.EmploymentCountry_c = 'RO'
AND (UPPER(base.SalesRP_c) = 'N' OR base.SalesRP_c IS NULL)
AND TO_NUMBER(base.CED_c) < 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 9
WHEN base.HRStatus_c = 'A'
AND base.EmploymentCountry_c != 'RO'
AND (UPPER(base.SalesRP_c) = 'N' OR base.SalesRP_c IS NULL)
AND TO_NUMBER(base.CED_c) < 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 10
WHEN base.HRStatus_c = 'A'
AND (UPPER(base.SalesRP_c) = 'Y')
AND TO_NUMBER(base.BeginningBalance_c) = 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 11
WHEN base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 99
ELSE 0
END AS Rule_Number
FROM (
WITH
Comments
Post a Comment