1 Reply Latest reply on May 27, 2011 8:38 AM by 722075

    Left Outer Join syntax for the BIP SQL Query

    820365
      While using BI Publisher, I created a query using the query builder and the Data Source is Oracle BI EE (My local RPD). The query that was generated is as follows:

      select     "- ASR CABS Billing Info".BAN as BAN,
           "- ASR Inbound Path Info".CCNA as CCNA,
           "- ASR Inbound Path Info".CNO as CNO,
           "- ASR Inbound Path Info".CKR as CKR,
           "- ASR Inbound Path Info".Initiator as Initiator,
           "- ASR Inbound Path Info"."Customer PON" as "Customer PON",
           Circuits."Z Side Site Name" as "Z Side Site Name",
           Circuits."Circuit Path" as "Circuit Path",
           Circuits."Scheduled Due Date" as "Scheduled Due Date",
      Circuits."Ordered Date" as "Ordered Date",
           "- ASR Inbound Path Info".SECLOC as SECLOC,
           "- ASR Inbound Path Info".FNI as FNI,
           "- ASR Inbound Path Info".ORD as ORD,
           "- ASR Inbound Path Info".VER as VER,
           "- ASR Inbound Path Info"."ASR Number" as "ASR Number",
           "- ASR Inbound Path Info"."Project ID" as "Project ID",
      "- FOC Sent Info"."DLR Date" as "DLR Date",
      "- FOC Sent Info"."EC VER" as "EC VER",
      "- FOC Sent Info"."PTD" as PTD,
      "- FOC Sent Info"."REF NUM" as "REF NUM",
      "- FOC Sent Info"."REMARKS" as REMARKS
      from     "Application - NIS Granite".Circuits Circuits,
           "Application - NIS Granite"."- ASR Inbound Path Info" "- ASR Inbound Path Info",
           "Application - NIS Granite"."- ASR CABS Billing Info" "- ASR CABS Billing Info",
      "Application - NIS Granite"."- FOC Sent Info" "- FOC Sent Info"
      where   "- ASR CABS Billing Info"."Circuit Path" = Circuits."Circuit Path"
      and      "- ASR Inbound Path Info"."Circuit Path" = Circuits."Circuit Path"
      and      "- FOC Sent Info"."Circuit Path" = Circuits."Circuit Path"

      The important part is in BOLD

      I want to indicate a LEFT OUTER JOIN but when I modify the query as follows I get a syntax error:

      where   "- ASR CABS Billing Info"."Circuit Path" = Circuits."Circuit Path" (+)
      and      "- ASR Inbound Path Info"."Circuit Path" = Circuits."Circuit Path" (+)
      and      "- FOC Sent Info"."Circuit Path" = Circuits."Circuit Path" (+)
      So my ultimate question is:

      What is the syntax for left outer join when defining the sql query in BI Publisher? It looks like the generated query that includes the left outer join also causes a syntax error. What am I missing?

      Thanks!

      Edited by: user6098581 on Dec 1, 2010 1:23 PM