Link to home
Start Free TrialLog in
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
Avatar of dsacker
dsacker
Flag of United States of America image

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?
Avatar of gocoool
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
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.
You may find this works:

WHERE INSTR(B.user_name(+), A.user_name) > 0
Avatar of 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
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?
Avatar of 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
Avatar of dsacker
dsacker
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial