Link to home
Start Free TrialLog in
Avatar of Allen Pitts
Allen PittsFlag for United States of America

asked on

PL/SQL Display based on value

Hello Expert,

Working query like
SELECT EMAIL,
  CON.CONTACT_ID,
  USR.FIRST_NAME AS LAST_Update_1st,
  USR.LAST_NAME as LAST_Update_LAST,
  CON.LAST_UPDATE_DATE,
  PUBLISH_STATUS_CODE,
  LAST_PUBLISH_DATE,
OWNER_TYPE_CODE
FROM CCM.CCM_CONTACT CON
join CCM_CONTACT_EMAIL CEM on CON.CONTACT_ID = CEM.CONTACT_ID
join CCM_USER USR on CON.LAST_UPDATE_USER = USR.USER_ID  OWNER_ID
FROM CCM.CCM_CONTACT CON
order by EMAIL

This returns good information.
It is, however, for an end user who will not know that OWNER_TYPE_CODE
15001 is and Employer and 15002 is a Location.

Have tried several versions of
............
  PUBLISH_STATUS_CODE,
  LAST_PUBLISH_DATE,
IF OWNER_TYPE_CODE = 15001 THEN
   {OWNER_TYPE_CODE = 'Employer'}

ELSE
      {OWNER_TYPE_CODE = 'Location'}

END IF,
OWNER_ID
FROM CCM.CCM_CONTACT CON
..............

With no luck.

How can a string be displayed based on a value
in a cell?

Thanks.

Allen Pitts, Dallas Texas
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sean Stuber
Sean Stuber

put your owner types and descriptions in a table then do a lookup or a join

  SELECT email,
         con.contact_id,
         usr.first_name AS last_update_1st,
         usr.last_name AS last_update_last,
         con.last_update_date,
         publish_status_code last_publish_date,
         (SELECT description
            FROM owner_type
           WHERE code = owner_type_code)
             owner_type
    FROM ccm.ccm_contact con
         JOIN ccm_contact_email cem ON con.contact_id = cem.contact_id
         JOIN ccm_user usr ON con.last_update_user = usr.user_id
ORDER BY email;


  SELECT email,
         con.contact_id,
         usr.first_name AS last_update_1st,
         usr.last_name AS last_update_last,
         con.last_update_date,
         publish_status_code last_publish_date,
         ot.description owner_type
    FROM ccm.ccm_contact con
         JOIN ccm_contact_email cem ON con.contact_id = cem.contact_id
         JOIN ccm_user usr ON con.last_update_user = usr.user_id
         JOIN owner_type ot ON ot.code = owner_type_code
ORDER BY email;
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Allen Pitts

ASKER

Tested both. Both work. Thanks.