Thanks !

Best regards,
Georg

Woudnt that get a while lot of records ?
and what it we need to do it mroe then ones say one master table and mutilple detaisl tables..? would we need to have more then one select * from (select * etc

Tom,
Thanks a lot for all your help to the DBA community all over the world, I have the following query for which i'm getting this

"ORA-01417: a table may be outer joined to at most one other tabl" error. Can you please help me put here, your quick response(as always) will be really valueable and appreciated.




select * from
(select inner.*, rownum rn from
(select SL.LD_LEG_DETL_ID XYZ0,
SL.SEQ_NUM XYZ1,
SL.SHPM_ID XYZ2,
SL.CUR_OPTLSTAT_ID XYZ3,
SL.DISPLAY_STATUS XYZ4,
SL.CUR_FNCLSTAT_ID XYZ5,
SL.DIV_CD XYZ6,
SL.LGST_GRP_CD XYZ7,
(case when SL.HELD_YN = 'T' then 1 else 0 end) XYZ8,
(case when SL.SUS_YN = 'T' then 1 else 0 end) XYZ9,
(case when SL.URGT_YN = 'T' then 1 else 0 end) XYZ10,
SL.RLS_FUNC_ENU XYZ11,
SL.BS_WGT XYZ12,
SL.BS_VOL XYZ13,
SL.TOT_DIST XYZ14,
SL.CUST_CD XYZ15,
SL.CARR_CD XYZ16,
(case when SL.CARR_CMTD_YN = 'T' then 1 else 0 end) XYZ17,
SL.SRVC_CD XYZ18,
(case when SL.SRVC_CMTD_YN = 'T' then 1 else 0 end) XYZ19,
SL.EQMT_TYP XYZ20,
(case when SL.EQMT_TYP_CMTD_YN = 'T' then 1 else 0 end) XYZ21,
SL.LD_LEG_ID XYZ22,
SL.LD_LEG_CAT_ENU XYZ23,
SL.PKUP_ARVL_DTT XYZ24,
SL.PKUP_DPTR_DTT XYZ25,
SL.DROP_ARVL_DTT XYZ26,
SL.DROP_DPTR_DTT XYZ27,
SL.CFMD_DTT XYZ28,
SL.RFRC_NUM XYZ29,
SL.BOL_NUM XYZ30,
SL.MMO_ID XYZ31,
SL.TFF_ID XYZ32,
SL.BILLTO_CUST_CD XYZ33,
SL.PKUP_APT_ID XYZ34,
SL.DROP_APT_ID XYZ35,
SL.PICK_STOP_ID XYZ36,
SL.DROP_STOP_ID XYZ37,
SL.TNST_MD_ENU XYZ38,
(case when SL.TNST_MD_CMTD_YN = 'T' then 1 else 0 end) XYZ39,
SL.MRGE_CSLD_CLS_ID XYZ40,
SL.CSLD_CLS XYZ41,
(case when SL.SCHD_ACTV_YN = 'T' then 1 else 0 end) XYZ42,
(case when SL.RATG_VLID_YN = 'T' then 1 else 0 end) XYZ43,
(case when SL.COMP_TRKG_YN = 'T' then 1 else 0 end) XYZ44,
SL.RATE_CD XYZ45,
SL.UMSRSYS_ENU XYZ46,
SL.UMSRWGT_ENU XYZ47,
SL.UMSRLEN_ENU XYZ48,
SL.UMSRDIST_ENU XYZ49,
SL.CNCY_TYP XYZ50,
SL.FRHT_TRM_ENU XYZ51,
SL.FRM_SHPG_LOC_CD XYZ52,
SL.FRM_SHPG_LOC_NAME XYZ53,
SL.FRM_PNT_TYP_ENU XYZ54,
SL.FRM_CTRY_CD XYZ55,
SL.FRM_STA_CD XYZ56,
SL.FRM_CTY_NAME XYZ57,
SL.FRM_PSTL_CD XYZ58,
SL.TO_SHPG_LOC_CD XYZ59,
SL.TO_SHPG_LOC_NAME XYZ60,
SL.TO_PNT_TYP_ENU XYZ61,
SL.TO_CTRY_CD XYZ62,
SL.TO_STA_CD XYZ63,
SL.TO_CTY_NAME XYZ64,
SL.TO_PSTL_CD XYZ65,
S.SHPM_NUM XYZ66,
S.SHPM_DESC XYZ67,
S.CDTY_CD XYZ68,
S.TOT_SKID XYZ69,
S.TOT_PCE XYZ70,
S.BS_WGT XYZ71,
S.BS_VOL XYZ72,
S.BS_ODR_DLR XYZ73,
S.BS_DCLD_DLR XYZ74,
S.INPT_UMSR_WGT_ENU XYZ75,
S.SCLD_WGT XYZ76,
S.NMNL_WGT XYZ77,
S.INPT_UMSR_LEN_ENU XYZ78,
S.LDN_LEN XYZ79,
S.CFMG_USR_CD XYZ80,
RN1.RFRC_NUM XYZ81,
RN2.RFRC_NUM XYZ82,
RN3.RFRC_NUM XYZ83,
RN4.RFRC_NUM XYZ84,
RN5.RFRC_NUM XYZ85,
RN6.RFRC_NUM XYZ86,
RN7.RFRC_NUM XYZ87,
RN8.RFRC_NUM XYZ88,
RN9.RFRC_NUM XYZ89
from ld_leg_detl_t SL, shpm_t S, ld_bult_ver_t LBV,
rfrc_num_t RN1, rfrc_num_t RN2, rfrc_num_t RN3, rfrc_num_t RN4,
rfrc_num_t RN5, rfrc_num_t RN6, rfrc_num_t RN7, rfrc_num_t RN8,
rfrc_num_t RN9
where SL.SHPM_ID = S.SHPM_ID AND
SL.CUR_OPTLSTAT_ID in (410, 425) and
LBV.LD_BULT_VER_CD = '*DFT'
and (SL.SHPM_ID = RN1.SHPM_ID(+) and LBV.LD_TYP_ENU = RN1.RFRC_NUM_TYP(+))
and (SL.SHPM_ID = RN2.SHPM_ID(+) and LBV.LD_TYP_ENU = RN2.RFRC_NUM_TYP(+))
and (SL.SHPM_ID = RN3.SHPM_ID(+) and LBV.LD_TYP_ENU = RN3.RFRC_NUM_TYP(+))
and (SL.SHPM_ID = RN4.SHPM_ID(+) and LBV.LD_TYP_ENU = RN4.RFRC_NUM_TYP(+))
and (SL.SHPM_ID = RN5.SHPM_ID(+) and LBV.LD_TYP_ENU = RN5.RFRC_NUM_TYP(+))
and (SL.SHPM_ID = RN6.SHPM_ID(+) and LBV.LD_TYP_ENU = RN6.RFRC_NUM_TYP(+))
and (SL.SHPM_ID = RN7.SHPM_ID(+) and LBV.LD_TYP_ENU = RN7.RFRC_NUM_TYP(+))
and (SL.SHPM_ID = RN8.SHPM_ID(+) and LBV.LD_TYP_ENU = RN8.RFRC_NUM_TYP(+))
and (SL.SHPM_ID = RN9.SHPM_ID(+) and LBV.LD_TYP_ENU = RN9.RFRC_NUM_TYP(+))
order by S.SHPM_NUM desc) inner where rownum < 18) where rn >= 1


Regards
Tony

Thanks for the quick response Tom, well they'll be different, actually my concern is to make one join condition work e.g

(SL.SHPM_ID = RN1.SHPM_ID(+) and (LBV.LD_TYP_ENU = RN1.RFRC_NUM_TYP(+) )

I just need all ship_id values if LBV.LD_TYP_ENU = RN1.RFRC_NUM_TYP and if LBV.LD_TYP_ENU <> RN1.RFRC_NUM_TYP then i'm looking for those where SL.SHPM_ID = RN1.SHPM_ID(+) is true and LBV.LD_TYP_ENU = RN1.RFRC_NUM_TYP is not true.
let me give you and example:
let's say

SL table constains value 223
RN1 table constains values 223,223 for shpm_id and 5,6 for RFRC_NUM_TYP
Now i'm looking for both the rows in RN1 table if LBV table constains LD_TYP_ENU values 5 and 6
but if LBV.LD_TYP_ENU does not contain 5 and 6 then I just need one row from RN1 table with value 223 and a null for RN1.RFRC_NUM_TYP

Please help me out!
Thanks
Tony

Thanks Tom,
I know the query is performing the same join nine times that's why i don't care about the other eight joins but just the first one. Actually this query is written by a developer here and I know he is doing it wrong. Can you please help me with the problem of getting the desired rows for just one join as I mentioned in previous update.

Thanks a lot
Tony

ORA-01417: a table may be outer joined to at most one other table

Hello Tom

I need help with the following error ORA-01417: a table may be outer joined to at most one other table. I understand why I am getting the error, but I cannot figure out how to fix it with the current editing restrictions. The SQL statement is generated by an application called Business Objects and has a built in Universe by the vendor that is not editable with respect to certain joins. Also, while I know that the SQL can be constructed more succinctly, I am again limited by the application and what it generates as the statement.

There are basically 3 tables in the query: study (STPR_STUDY), student (STUDENT), task (DERIVED_TASK_SCHEDULE)

Each study can have 0 or more students assigned.
Each task id has 1 study assigned and can have 0 or more students

The study table is joined to the students table via a study id
The task table is joined to the study table via a study id and to the students table via a student id

I need to capture all the studies with or without a student assigned.
I also need to capture all the tasks associated with a study with or without a student assigned.

I cannot change the join between the study and student table (its static based on a vendor supplied universe). I can however change the join between the task and the student table
(AND (DERIVED_TASK_SCHEDULE.STUDENT_ID=STUDENT.ID (+) ).
I also cannot do much editing on the select statement, only the where clause between the join above.

How can I rewrite the outer join statement to generate the results I need?

Any help is greatly appreciated!

Thank you,
Dee

SELECT
STPR_STUDY.STD_ID,
STUDENT.STUDENT_NUM,
DERIVED_TASK_SCHEDULE."Task"
FROM
STPR_STUDY,
(SELECT
ID,
STD_ID,
STPR_STD_STUDENT.STUDENT_NUM FROM STPR_STD_STUDENT
UNION
SELECT
ID,
STD_ID,
STPR_PRESTD_STUDENT.STUDENT_NUM
FROM STPR_PRESTD_STUDENT) STUDENT,
(SELECT
stpr_study.id AS "Study ID",
case when stpr_task_subj.student_id is not null then stpr_task_subj.student_id
else stpr_task_subj.prestd_student_id
end as student_id,
stpr_task.id,
stpr_task.name AS "Task"

FROM stpr_study,stpr_study_det, stpr_task, stpr_task_date, stpr_task_subj

WHERE
stpr_study_det.std_id = stpr_study.id
and stpr_study.id = stpr_task.std_id
and stpr_task.id= stpr_task_date.task_id
and stpr_task.id = stpr_task_subj.task_id (+)) DERIVED_TASK_SCHEDULE

WHERE
(STPR_STUDY.ID=DERIVED_TASK_SCHEDULE."Study ID" )
AND (DERIVED_TASK_SCHEDULE.STUDENT_ID=STUDENT.ID (+) )
AND (STPR_STUDY.ID=STUDENT.STD_ID(+))

Hi Tom

Thank you for the reply.
The Derived_Task_Schedule table is the SQL I need which I created similiar to a view, however I need to link it to the student table so that users can drop in objects from the student business objects class and the task class to create reports.

Is there anything that can be changed in the where clause join between students and tasks

Thanks again
Dee

You may find RN1-410 exam at
http://www.certmagic.com/RN1-410-certification-practice-exams.html Good luck in passing the exam