b1nary0mega
4/28/2014 - 4:37 PM

Set employee alias

Set employee alias

UPDATE employee_profile
SET ms_alias = (
  CASE
    WHEN (title LIKE '%Technician%')
    THEN SUBSTR(first_name,1,1)
      || SUBSTR(last_name,1,3)
      || '_TECH'
    WHEN (title LIKE '%LPN%')
    THEN SUBSTR(first_name,1,1)
      || SUBSTR(last_name,1,4)
      || '_LPN'
    WHEN (title LIKE '%Nurse Mgr%')
    THEN SUBSTR(first_name,1,1)
      || SUBSTR(last_name,1,4)
      || '_MGR'
    WHEN (title LIKE '%Nurse%')
    THEN SUBSTR(first_name,1,1)
      || SUBSTR(last_name,1,5)
      || '_RN'
    WHEN (title LIKE '%RN, Weekend%')
    THEN SUBSTR(first_name,1,1)
      || SUBSTR(last_name,1,5)
      || '_RN'
    WHEN (title LIKE '%Unit Support Asst%')
    THEN SUBSTR(first_name,1,1)
      || SUBSTR(last_name,1,4)
      || '_USA'
    WHEN (title LIKE '%Secretary%')
    THEN SUBSTR(first_name,1,1)
      || SUBSTR(last_name,1,3)
      || '_SECT'
    WHEN (title LIKE '%Receptionist Clerk%')
    THEN SUBSTR(first_name,1,1)
      || SUBSTR(last_name,1,4)
      || '_RCP'
    WHEN (title LIKE '%Oprs Sup%')
    THEN SUBSTR(first_name,1,1)
      || SUBSTR(LAST_NAME,1,4)
      || '_OPS'
    WHEN (ms_alias IS NULL) 
    THEN SUBSTR(first_name,1,1)
      || SUBSTR(last_name,1,8)
    ELSE ms_alias    
  END )
WHERE UNIT_ID='CH3'
AND DEPT='500342';