sql - 使用 CASE 作为 dblink 的一部分来获取数据

Question

我正在尝试使用 CASE 从带有子查询的 dblink 返回值。但是，结果并没有显示我所期望的。它不是遍历每个 WHEN，而是从第一个 WHEN 返回值（即使它不符合条件）。我确定这与我如何将 CASE 与子查询一起使用有关。

我自己运行了子查询，它返回了预期的结果。

select su.shp_filter as "User_Type", su.shp_access_id as "RAD_ID", su.shp_name as "User_Name", rg.rad_stu_level as "Student_Level",
case
    when su.shp_filter = 'STU' then 
        case
            when rgd.rad_goal_value like 'HP%' then 'HP'
            when rgd.rad_goal_value like 'AS%' then 'AS'
            when rgd.rad_goal_value like 'BU%' then 'BU'
            when rgd.rad_goal_value like 'ED%' then 'ED'
            when rgd.rad_goal_value like 'TE%' then 'TE'
            when rgd.rad_goal_value like 'UN%' then 'UN'
            when rgd.rad_goal_value like 'KE%' then 'KE'
            when rgd.rad_goal_value like 'PH%' then 'PH'
            when rgd.rad_goal_value like 'OP%' then 'OP'
            when rgd.rad_goal_value like 'CP%' then 'CP'
        end
    when su.shp_filter in ('ADV','REG','DEAN','DEPT','ATHL') then
        case
            when exists (select pebempl.pebempl_orgn_code_home, su.shp_access_id, spriden.spriden_id from pebempl@dblink pebempl
            join [e-mail] spriden
            on pebempl.pebempl_pidm=spriden.spriden_pidm
            join DWSCHEMA.SHP_USER_MST su
            on substr(su.shp_access_id,1,8) = substr(spriden.SPRIDEN_ID,1,8)
            where spriden.spriden_change_ind is null
            and pebempl.pebempl_orgn_code_home like '11%') then 'Office 1'
            when exists (select pebempl.pebempl_orgn_code_home, su.shp_access_id, spriden.spriden_id from pebempl@dblink pebempl
            join [e-mail] spriden
            on pebempl.pebempl_pidm=spriden.spriden_pidm
            join DWSCHEMA.SHP_USER_MST su
            on substr(su.shp_access_id,1,8) = substr(spriden.SPRIDEN_ID,1,8)
            where spriden.spriden_change_ind is null
            and pebempl.pebempl_orgn_code_home like '21%') then 'Office 2'
            when exists (select pebempl.pebempl_orgn_code_home, su.shp_access_id, spriden.spriden_id from pebempl@dblink pebempl
            join [e-mail] spriden
            on pebempl.pebempl_pidm=spriden.spriden_pidm
            join DWSCHEMA.SHP_USER_MST su
            on substr(su.shp_access_id,1,8) = substr(spriden.SPRIDEN_ID,1,8)
            where spriden.spriden_change_ind is null
            and pebempl.pebempl_orgn_code_home like '22%') then 'Office 3'
            when exists (select pebempl.pebempl_orgn_code_home, su.shp_access_id, spriden.spriden_id from pebempl@dblink pebempl
            join [e-mail] spriden
            on pebempl.pebempl_pidm=spriden.spriden_pidm
            join DWSCHEMA.SHP_USER_MST su
            on substr(su.shp_access_id,1,8) = substr(spriden.SPRIDEN_ID,1,8)
            where spriden.spriden_change_ind is null
            and pebempl.pebempl_orgn_code_home like '31%') then 'Academic Affairs'
end "College"

标签： sqloraclecasedblink

Answer 1

哦 - 我想我看到了你的问题。您的exists子查询根本没有连接到您的外部查询，因此它们总是返回数百行。如果将 SHP_USER_MST 的每个实例重命名为 su1、su2、su3 等，这会更清楚：

when su.shp_filter in ('ADV','REG','DEAN','DEPT','ATHL') then
    case
        when exists (select pebempl.pebempl_orgn_code_home, su1.shp_access_id, spriden.spriden_id from pebempl@dblink pebempl
            join [e-mail] spriden
            on pebempl.pebempl_pidm=spriden.spriden_pidm
            join DWSCHEMA.SHP_USER_MST su1 
            on substr(su1.shp_access_id,1,8) = substr(spriden.SPRIDEN_ID,1,8)
            where spriden.spriden_change_ind is null
            and pebempl.pebempl_orgn_code_home like '11%') then 'Office 1'
        when exists (select pebempl.pebempl_orgn_code_home, su2.shp_access_id, spriden.spriden_id from pebempl@dblink pebempl
            join [e-mail] spriden
            on pebempl.pebempl_pidm=spriden.spriden_pidm
            join DWSCHEMA.SHP_USER_MST su2 
            on substr(su2.shp_access_id,1,8) = substr(spriden.SPRIDEN_ID,1,8)
            where spriden.spriden_change_ind is null
            and pebempl.pebempl_orgn_code_home like '21%') then 'Office 2'

如果您看这里，子查询中的任何内容都没有引用当前行su- 因此您正在检查您的子查询中是否存在任何用户。他们总是这样做，所以它总是返回“Office 1”。

我想你想要的东西更像

when su.shp_filter in ('ADV','REG','DEAN','DEPT','ATHL') then
    case
        when exists (select pebempl.pebempl_orgn_code_home, su.shp_access_id, spriden.spriden_id from pebempl@dblink pebempl
            join [e-mail] spriden
            on pebempl.pebempl_pidm=spriden.spriden_pidm
            where spriden.spriden_change_ind is null
            and substr(su.shp_access_id,1,8) = substr(spriden.SPRIDEN_ID,1,8) -- Link subquery to outer query
            and pebempl.pebempl_orgn_code_home like '11%') then 'Office 1'
        when exists (select pebempl.pebempl_orgn_code_home, su.shp_access_id, spriden.spriden_id from pebempl@dblink pebempl
            join [e-mail] spriden
            on pebempl.pebempl_pidm=spriden.spriden_pidm
            where spriden.spriden_change_ind is null
            and substr(su.shp_access_id,1,8) = substr(spriden.SPRIDEN_ID,1,8) -- Link subquery to outer query
            and pebempl.pebempl_orgn_code_home like '21%') then 'Office 2'
...etc

我认为这应该对你有用，但作为一个额外的建议，我认为你也可以重写该部分以提高效率和可读性。

case (select substr(min(pebempl.pebempl_orgn_code_home),1,2) from pebempl@dblink pebempl
        join [e-mail] spriden
        on pebempl.pebempl_pidm=spriden.spriden_pidm
        where spriden.spriden_change_ind is null
        and substr(su.shp_access_id,1,8) = substr(spriden.SPRIDEN_ID,1,8))
    when '11' then 'Office 1'
    when '21' then 'Office 2'
    when '22' then 'Office 3'
    when '31' then 'Academic Affairs'
end