troubleshooting Question

How to use the join condition when using like ?

Avatar of gocoool
gocoool asked on
Databases
8 Comments1 Solution553 ViewsLast Modified:
i want to join two tables using the like condition but i have to join them using the right outer join. How to do that..I am attaching my query here


SELECT DISTINCT A.user_name,A.effective_date,B.user_id FROM

(SELECT DISTINCT(last_name || ' '|| first_name) AS user_name,TO_CHAR(effdate,'MM/DD/YYYY') AS effective_date FROM (
SELECT last_name,first_name,effdate FROM ( SELECT first_name,effdate,trim(SUBSTR(last_name,1,INSTR(last_name,' ',1)-1))
 AS last_name FROM(SELECT (last_name || ' ') AS last_name, first_name,effdate FROM SOXADMIN.sox_hr_report WHERE
 UPPER(type_of_user) = 'TERMINATION' AND TO_CHAR(file_date) =(SELECT MAX(TO_DATE(TO_CHAR(file_date,'MM/DD/YYYY'),
 'MM/DD/YYYY')) FROM SOXADMIN.sox_hr_report))) ))A,
 
(SELECT user_id,tss_code_desc AS user_name FROM soxadmin.sox_tss_by_profile WHERE  tss_code = '0100'
UNION
SELECT user_id,user_name AS user_name FROM soxadmin.sox_cross_reference
UNION
SELECT user_id,tss_code_desc AS user_name  FROM soxadmin.sox_tss_by_profile_prior WHERE tss_code = '0100'
UNION
SELECT user_id,user_name AS user_name FROM  soxadmin.sox_apps_approved_profile
)B WHERE B.user_name(+) = A.user_name( here we have to use lie B.user_name like '%%' (+))


please reply
thanks
ASKER CERTIFIED SOLUTION
dsacker
Contract ERP Admin/Consultant

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros