I have a table with fields man_phone and head_phone, and in the selects there are such cases for obtaining them:

case
  when p.dep_code <> 'MM' then
    case
      when p.man_id = '%%%%%%%' then
        'Man Spec-100'
      when m.man_l is not null then
        m.man_phone
      else
        case
          when m2.man_l is not null then
            m2.man_phone
          else
            m3.man_phone
        end
    end
  else
    o.cont_phone  
end as man_phone,
   
case 
   when p.dep_code <> 'MM' then
     case
       when p.man_id = '%%%%%%%' then
         'Man Spec-100'   
       when m2.man_l is not null then
         m2.man_phone
       else
         m3.man_phone
       end
   else 
     o.head_phone      
end as head_phone

I want to add a new field called head_login with a case if man_phone = head_phone, then it will output man_l as head_login and I tried to push these two existing cases into only one case, what would be the right way to do this?

case 
  case
    when p.dep_code <> 'MM' then
      case
        when p.man_id = '%%%%%%%' then
          'Man Spec-100'
        when m.man_l is not null then
          m.man_phone
        else
          case
            when m2.man_l is not null then
              m2.man_phone
            else
              m3.man_phone
          end
      end
    else
      o.cont_phone  
  end as man_phone,
  case 
     when p.dep_code <> 'MM' then
       case
         when p.man_id = '%%%%%%%' then
           'Man Spec-100'   
         when m2.man_l is not null then
           m2.man_phone
         else
           m3.man_phone
         end
     else 
       o.head_phone      
  end as head_phone
  when p.department_code <> 'MM' then
    case
      when man_phone = head_phone
        m2.man_l
      else
        m3.man_l
    end
end as head_login

CodePudding user response：

I want to add a new field called head_login with a case if man_phone = head_phone, then it will output man_l as head_login and I tried to push these two existing cases into only one case.

Do not try to merge the two cases into a third case. Evaluate them in a sub-query and then put the CASE which compares them into an outer query:

SELECT man_phone,
       head_phone,
       CASE
       WHEN man_phone = head_phone
       THEN man_l2
       ELSE man_l3
       END AS head_login
FROM   (
  SELECT CASE <your case> END AS man_phone,
         CASE <your case> END AS head_phone,
         m2.man_l AS man_l2,
         m3.man_l AS man_l3
  FROM   table_name
)