我应该在下面的查询中更改什么？

Question

SELECT 
      ERI.ATTACK_CASE_ID, 
      LRM.REF_VALUE AS ATTACK_CASE_STATUS,
      WDR.RESTRICTION_NAME, 
    LL.USER_LABEL AS ***RESTRICTION_STATUS***,
     WSRI.RESTRICTION_DATE,
       CASE NVL(ERI.IS_PRIMARY, 0) WHEN 0 THEN 'No' ELSE CASE ERI.IS_PRIMARY WHEN 1 THEN 'Yes' ELSE 'No' END END AS PRIMARY
FROM ALERT A 
 LEFT OUTER JOIN ENTITY_RESTRICTION_INFO ERI ON A.ALERT_KEY=ERI.ENTITY_KEY
 LEFT OUTER JOIN LK_REFERENCE_MAPPING LRM ON LRM.REF_KEY= TO_CHAR(ERI.ATTACK_CASE_STATUS)
 LEFT OUTER JOIN LK_REFERENCE_CATEGORY LRG ON LRM.LK_REFERENCE_CATEGORY_ID = LRG.ID 
AND  LRG.CATEGORY_CODE = 'DD' 
AND LRG.SUB_CATEGORY_CODE='ATTACK_STATUS_ID_NAME'   
 LEFT OUTER JOIN WNORKOM_DD_RESTRICTION WDR ON ERI.RESTRICTION_ID=WDR.RESTRICTION_ID 
 LEFT OUTER JOIN WNORKOM_SAR_RESTRICT_INFO WSRI ON WSRI.RESTRICTION_ID=ERI.RESTRICTION_ID
 *LEFT OUTER JOIN LK_LOOKUPS LL ON LL.CODE=**WSRI.RESTRICTION_STATUS***
WHERE A.ALERT_KEY=121234 
AND ERI.ATTACK_CASE_ID='PP-123-0980'
AND LL.TYPE=9502

当 WSRI.RESTRICTION_STATUS 中有某个值时，上面的查询会返回所有记录。

但是，当 WSRI.RESTRICTION_STATUS = NULL 时，它会返回 NO RECORDS。 我可以在上面的查询中添加/更改什么，以便当 WSRI.RESTRICTION_STATUS = NULL 时 LL.USER_LABEL = 'Unknown'。供您参考，LK_LOOKUPS 表中没有值“未知”。当 WSRI.RESTRICTION_STATUS = NULL 时，我希望将其作为独立字符串返回。使用 LL.CODE=NULL 和 LL.USER_LABEL='Unknown' 将记录添加到 LK_LOOKUPS 表中可能会弄乱事情，因此请帮助我在查询中独立传递它。

提前致谢。

Answer 1

听起来您只需将条件 LL.Type = 9502 移至 Left Join 并在 Select 子句中使用 Coalesce 即可。此外，由于您要从Where子句将条件应用于Entity_Restriction_Info表中的列，因此您已将该表的左联接转换为内联接，因此，您不妨使用内联接。

Select Eri.Attack_Case_Id
    , Lrm.Ref_Value As Attack_Case_Status
    , Wdr.Restriction_Name
    , Coalesce(LL.User_Label, 'Unknown') As Restriction_Status
    , Wsri.Restriction_Date
    , Case Nvl(Eri.Is_Primary, 0) 
        When 0 Then 'No' 
        Else Case Eri.Is_Primary 
                When 1 Then 'Yes' 
                Else 'No' 
                End 
        End As Primary
From Alert As A
    Inner Join Entity_Restriction_Info As Eri 
        On A.Alert_Key=Eri.Entity_Key
    Left Outer Join Lk_Reference_Mapping As Lrm 
        On Lrm.Ref_Key= To_Char(Eri.Attack_Case_Status)
    Left Outer Join Lk_Reference_Category As Lrg 
        On Lrm.Lk_Reference_Category_Id = Lrg.Id
            And  Lrg.Category_Code = 'Dd' 
            And Lrg.Sub_Category_Code='Attack_Status_Id_Name'   
    Left Outer Join Wnorkom_Dd_Restriction As Wdr 
        On Eri.Restriction_Id=Wdr.Restriction_Id 
    Left Outer Join Wnorkom_Sar_Restrict_Info As Wsri 
        On Wsri.Restriction_Id=Eri.Restriction_Id
    Left Outer Join Lk_Lookups As LL
        On LL.Code='Wsri.Restriction_Status'
            And LL.Type=9502
Where A.Alert_Key=121234 
    And Eri.Attack_Case_Id='Pp-123-0980'

Answer 2

使用 LL.CODE=NULL 添加记录不会对您有帮助，因为 NULL=NULL 始终为 null（因此为 false）。您需要使用选择表达式：

删除 LEFT OUTER JOIN LK_LOOKUPS LL ON LL.CODE=WSRI.RESTRICTION_STATUS 和 AND LL.TYPE=9502，然后更改LL.USER_LABEL AS RESTRICTION_STATUS, 为：

IF(WSRI.RESTRICTION_STATUS IS NULL,
  "Unknown", 
  (SELECT USER_LABEL FROM LK_LOOKUPS LL
    WHERE LL.TYPE=9502 AND LL.CODE=WSRI.RESTRICTION_STATUS)
) AS USER_LABEL

Answer 3

尝试（编辑 - 根据评论）：

SELECT 
      ERI.ATTACK_CASE_ID, 
      LRM.REF_VALUE AS ATTACK_CASE_STATUS,
      WDR.RESTRICTION_NAME, 
    DECODE ( WSRI.RESTRICTION_STATUS, NULL, 'Unknown', (SELECT USER_LABEL FROM LK_LOOKUPS LL WHERE LL.TYPE=9502 AND LL.CODE=WSRI.RESTRICTION_STATUS)) AS RESTRICTION_STATUS,
     WSRI.RESTRICTION_DATE,
       CASE NVL(ERI.IS_PRIMARY, 0) WHEN 0 THEN 'No' ELSE CASE ERI.IS_PRIMARY WHEN 1 THEN 'Yes' ELSE 'No' END END AS PRIMARY
FROM ALERT A 
 LEFT OUTER JOIN ENTITY_RESTRICTION_INFO ERI ON A.ALERT_KEY=ERI.ENTITY_KEY
 LEFT OUTER JOIN LK_REFERENCE_MAPPING LRM ON LRM.REF_KEY= TO_CHAR(ERI.ATTACK_CASE_STATUS)
 LEFT OUTER JOIN LK_REFERENCE_CATEGORY LRG ON LRM.LK_REFERENCE_CATEGORY_ID = LRG.ID 
AND  LRG.CATEGORY_CODE = 'DD' 
AND LRG.SUB_CATEGORY_CODE='ATTACK_STATUS_ID_NAME'   
 LEFT OUTER JOIN WNORKOM_DD_RESTRICTION WDR ON ERI.RESTRICTION_ID=WDR.RESTRICTION_ID 
 LEFT OUTER JOIN WNORKOM_SAR_RESTRICT_INFO WSRI ON WSRI.RESTRICTION_ID=ERI.RESTRICTION_ID
WHERE A.ALERT_KEY=121234 
AND ERI.ATTACK_CASE_ID='PP-123-0980'