Hi there,

I have got these 2 tables,

COM_ASS_USER_TAB (with the foll. columns)
-ID,FORENAME,SURNAME,PASSWORD,EMAIL,
TEL_STD,TEL_NO,VISITS,LAST_VISIT_DATE,
ACTIVE_YN,TEAM_ID,DIRECTORATE_ID


COM_SDG_SYSTEM_USERS_TAB (with the foll. columns)
-SYSTEM_VISITS,SYSTEM_LAST_VISIT_DATE,
ACCESS_LEVEL,SYSTEM_ACTIVE_YN,
USER_ID

I need to do outer join using ID from 1st table and USER_ID from 2nd table. Thats no hassle could do it.

But the conditions are they will be passing a System_ID as IN Parameter and i need to send a REF CURSOR back.

the foll. conditions have to be met.

1)IF system_id and access_level are empty, i want the record to be picked.

2)if system_id and access_level are not empty, i want the record to be picked.

I am passing the SQL query as a REF CURSOR to the front end.

When i combine all the conditions above with the outer join, its not working.

Outer join as of now is working below with the SQL
Select
decode(sdg.system_id,p_system_id,p_system_id,null),
sdg.SYSTEM_VISITS,
sdg.SYSTEM_LAST_VISIT_DATE,
sdg.ACCESS_LEVEL,
sdg.SYSTEM_ACTIVE_YN,
sdg.USER_ID,
cuser.ID,
cuser.LOGIN,
cuser.FORENAME,
cuser.SURNAME,
cuser.PASSWORD,
cuser.EMAIL,
cuser.TEL_STD,
cuser.TEL_NO,
cuser.VISITS,
cuser.LAST_VISIT_DATE,
cuser.ACTIVE_YN,
cuser.TEAM_ID,
cuser.DIRECTORATE_ID
from COM_SDG_SYSTEM_USERS_TAB sdg,
COM_ASS_USER_TAB cuser
where cuser.id=sdg.user_id(+);

System_ID will be passed as a IN parameter.

I m not sure how to go about.

Gurus out there, pl let me know

Cheers
Srini