Tune Query Advises

[mike2000]

THE BELOW QUERY DOESN'T WORK B/C i THINK I HAVE THREE LEFT JOIN. IF i COMMENT THE
TCTRL_SECTOR_OFFERING."OFFERING_ID" = TCTRL_CO_PROD_CURRENT_USE.""OFFERING_ID" THE QUERY WILL WORK, AT THE SAME TIME IF I NEED THE OFFERING COLUMN TOO.


SELECT
TCTRL_CO."CO_ID", TCTRL_CO."CO_NM",
TCTRL_CO_PROD_CURRENT_USE."OFFERING_ID", TCTRL_CO_PROD_CURRENT_USE."ELIGIBILITY_CD", TCTRL_CO_PROD_CURRENT_USE."INTERVIEW_STATUS_CD", TCTRL_CO_PROD_CURRENT_USE."PROD_END",
TCTRL_CO_SECTOR."SECTOR_ID",
TCODE_INTERVIEW_STATUS."INTERVIEW_STATUS_DESC",
TCTRL_SECTOR_OFFERING."OFFERING_ID",
TCTRL_CO_METRIC."METRIC_ID", TCTRL_CO_METRIC."METRIC_VALUE",
TCODE_SECTOR."SECTOR_NM",
TCODE_OFFERING."OFFERING_NM",
TCODE_METRIC."METRIC_NM"
FROM
"CRM"."TCTRL_CO" TCTRL_CO,
"CRM"."TCTRL_CO_PROD_CURRENT_USE" TCTRL_CO_PROD_CURRENT_USE,
"CRM"."TCTRL_CO_SECTOR" TCTRL_CO_SECTOR,
"CRM"."TCODE_INTERVIEW_STATUS" TCODE_INTERVIEW_STATUS,
"CRM"."TCTRL_SECTOR_OFFERING" TCTRL_SECTOR_OFFERING,
"CRM"."TCTRL_CO_METRIC" TCTRL_CO_METRIC,
"CRM"."TCODE_SECTOR" TCODE_SECTOR,
"CRM"."TCODE_OFFERING" TCODE_OFFERING,
"CRM"."TCODE_METRIC" TCODE_METRIC
WHERE
TCTRL_CO."CO_ID" = TCTRL_CO_PROD_CURRENT_USE."CO_ID" (+) AND
TCTRL_CO."CO_ID" = TCTRL_CO_SECTOR."CO_ID" (+) AND
TCTRL_SECTOR_OFFERING."OFFERING_ID" = TCTRL_CO_PROD_CURRENT_USE.""OFFERING_ID" (+) AND --- This is the join that doesn’t work
TCTRL_CO_PROD_CURRENT_USE."INTERVIEW_STATUS_CD" = TCODE_INTERVIEW_STATUS."INTERVIEW_STATUS_CD" (+) AND
TCTRL_CO_SECTOR."SECTOR_ID" = TCTRL_SECTOR_OFFERING."SECTOR_ID" (+) AND
TCTRL_CO_SECTOR."CO_ID" = TCTRL_CO_METRIC."CO_ID" (+) AND
TCTRL_CO_SECTOR."SECTOR_ID" = TCODE_SECTOR."SECTOR_ID" (+) AND
TCTRL_SECTOR_OFFERING."OFFERING_ID" = TCODE_OFFERING."OFFERING_ID" (+) AND
TCTRL_CO_METRIC."METRIC_ID" = TCODE_METRIC."METRIC_ID" (+)

[marist89]

9 outer joins? Is this thing supposed to finish this century?

[slimdave]

This is the A,B,C table problem, right? Cant join ..

select * from a,b,c
where
a.c = b.c(+) and
c.c = b.c(+)

... so you have to hide one of the joins inside an inline view ...

select * from a,(select * from b,c where b.c(+) = c.c) d
where d.c(+) = a.(+)

or somethin like that.

[mike9]

May it be that you made a typing mistake. For example typing 2 times double quote instead of only once?

TCTRL_CO_PROD_CURRENT_USE.""OFFERING_ID"


Mike