Allen Pitts
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tested both. Both work. Thanks.
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;