Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need to select the one value if it exists otherwise another value

Status
Not open for further replies.

szeiss

Programmer
Apr 5, 2000
137
US
I'm not sure how to go about this. In my tbl_Project_Employee below I have 4 dummy records. I need a query that selects where the employee_type_id = 48 if it exists; otherwise I select where the employee_type_id = 22. I've shown below the table what my results should be.

SQL:
[pre]
tbl_Project_Employee
ProjectID		Employee_Type_ID	Employee_Type_Desc		Email	
001			        48	        Public_Contact			john.doe@anywhere.com
001				22	        Project_Manager			jane.doe@anywhere.com
002				48	        Public_Contact			susan.jones@anywhere.com
003				22	        Project_Manager			david.howard@anywhere.com

Results S/B:
ProjectID		Employee_Type_ID	Employee_Type_Desc		Email
001				48	        Public_Contact			john.doe@anywhere.com
002			        48	        Public_Contact			susan.jones@anywhere.com
003				22	        Project_Manager			david.howard@anywhere.com
[/pre]

Thanks,
Sherry
 
I am not sure if Employee_Type_ID is numeric or character - I have assumed Char:

CASE
WHEN select * from tbl_Project_Employee where Employee_Type_ID = '48' IS NULL
THEN select * from tbl_Project_Employee where Employee_Type_ID = '22'
ELSE select * from tbl_Project_Employee where Employee_Type_ID = '48'
END

There is a better way to do this, and someone will post it. But this works and you should learn the CASE syntax.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity


 
Thank you for the reply. A co-worker was able to free up some time and help me out with this. I am familiar with the CASE syntax just wasn't successful in making it work. But, this is what we came up with, we also added a little more information to the query.

[pre]
select
s.ProjectID,
CASE
WHEN pc.EMPLOYEE_ID IS NOT NULL THEN
DECODE(uc.email_addr,NULL,lpc.firstname||'.'||lpc.lastname||'@anywhere.com',uc.email_addr)
ELSE
DECODE(um.email_addr,NULL,lm.firstname||'.'||lm.lastname||'@anywhere.com',um.email_addr)
END as CONTACT,
pc.EMPLOYEE_TYPE_ID,
pc.EMPLOYEE_ID,
(SELECT EMPLYSTAT FROM LIST_EMPLOYEE_INFO i where i.EMPLOYEE_ID = pc.EMPLOYEE_ID) as CONTACT_EMP_STATUS,
m.EMPLOYEE_TYPE_ID,
m.EMPLOYEE_ID,
(SELECT EMPLYSTAT FROM LIST_EMPLOYEE_INFO i2 where i2.EMPLOYEE_ID = m.EMPLOYEE_ID) as MANAGER_EMP_STATUS
from
subprojects s,
subproject_employee pc,
subproject_employee m,
cip_users uc,
cip_users um,
list_employees lpc,
list_employees lm
where
s.ProjectID = pc.ProjectID (+)
and pc.employee_type_id(+) = 48 -- public contact
and pc.employee_id = uc.employee_id(+)
and pc.employee_id = lpc.employee_id(+)

and s.ProjectID = m.ProjectID(+)
and m.employee_type_id(+) = 22 -- subproject manager
and m.employee_id = um.employee_id(+)
and m.employee_id = lm.employee_id(+)
[/pre]
 
One alternative to the CASE construct would be
Code:
SELECT * FROM tbl_project_employee 
WHERE (project_id, employee_type_id) IN (SELECT project_id, max(employee_type_id) 
                                           FROM tbl_project_employee 
                                          WHERE employee_type_id IN (22,48) 
                                          GROUP BY project_id);
This may or may not be a more efficient approach depending on table size, value mixture, etc, particularly if you use this as a dynamic view.
Of course, this is answering your original question. Looking at the solution you came up with, I think it would definitely be more efficient since you would not have to be doing outer joins on twin sets of tables. The resultant query would take a general form of
Code:
SELECT p.project_id,
       p.email AS contact,
       p.employee_type_id,
       p.employee_type_desc,
       e.employee_id,
       i.emplystat AS contact_status
  FROM (SELECT * FROM tbl_project_employee 
         WHERE (project_id, employee_type_id) IN (SELECT project_id, max(employee_type_id) 
                                                    FROM tbl_project_employee 
                                                   WHERE employee_type_id IN (22,48) 
                                                   GROUP BY project_id)) p
       INNER JOIN list_employees e
          ON e.email = p.email 
       INNER JOIN list_employee_info i
          ON i.employee_id = e.employee_id;
This hasn't been tested beyond the dynamic view, but I think it would work. Of course, if your current solution is working for you, this is all incidental. :)
 
Thank you, I'll take a look at it and keep it for future reference.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top