×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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.

CODE --> sql

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
 

Thanks,
Sherry

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

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


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

(OP)
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.

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(+)
 

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

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. smile

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

(OP)
Thank you, I'll take a look at it and keep it for future reference.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close