Avatar of gocoool
gocoool
 asked on

How to use the join condition when using like ?

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
Databases

Avatar of undefined
Last Comment
dsacker

8/22/2022 - Mon
dsacker

You should be able to simply add one line to the bottom:

WHERE B.user_name(+) = A.user_name
AND A.user_name like '%%'        -- or whatever you want in there

Since it's either B.user_name or A.user_name, either one could have been in this last "AND" statement, but since you've already outer-joined B, just use A for your like statement. Make sense?
gocoool

ASKER
Sorry i think i have confused you. Instead of tht outer join which i have used i have to use like the following one


where B.user_name(+) like '%A.user_name%'

Thanks
Gokul Dharumar
dsacker

If you're saying that A.user_name is an inner string within B.user_name, then do this:

WHERE INSTR(B.user_name,A.user_name) > 0

Now, this is for starters. I haven't figured out how to employ the outer join.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
dsacker

You may find this works:

WHERE INSTR(B.user_name(+), A.user_name) > 0
gocoool

ASKER
Ya i tried that....since these tables are big my system itself gets hanged on running the instr..is there any way to use the outer join condition for the like statement

Thanks
Gokul Dharumar
dsacker

Not really. That would be somewhat self-defeating, then an outer join is for a relationship result between two tables.

Have you tried adding a /*hint*/ to try forcing Oracle to indexes?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
gocoool

ASKER
No and i dont know wht r u talking abt the /*hint*/ ..can you please explain on this

Thanks
Gokul Dharumar
ASKER CERTIFIED SOLUTION
dsacker

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question