Tune Query Advises
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Tune Query Advises

  1. #1
    Join Date
    Sep 2002
    Posts
    411

    Tune Query Advises

    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" (+)

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    9 outer joins? Is this thing supposed to finish this century?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Mar 2002
    Posts
    534
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width