11 Replies Latest reply: Apr 1, 2009 8:21 PM by 689067 RSS

    How to pass multiple parameters to Query using START WITH, CONNECT BY OBIEE

    689067
      Hi

      I have following oracle query which need to be used as a Data Source in OBIEE Physical Layer. I guess I have to create stored proc. How do I implement this in OBIEE RPD and how do I implement the respective Dashboard prompts for the parameters.

      SELECT
      CIRC.PATH_NAME, CIRC.BANDWIDTH , CIRC.CATEGORY, CIRC.CUSTOMER_ID,
      CIRC.STATUS, CIRC.CUSTOMER_NAME,
      QUER.LEV
      FROM
      CIRCUIT_PATH circ, VAL_CUSTOMER cust,
      ( SELECT
      DISTINCT CIRC_PATH_INST_ID, LEVEL LEV
      FROM
      CIRC_PATH_ELEMENT
      START WITH
      CIRC_PATH_ELEMENT.CIRC_PATH_INST_ID IN ( SELECT
      DISTINCT CIRC_PATH_INST_ID
      FROM
      PORTS a
      WHERE SITE_NAME = @variable('Enter a Site Name')
      AND CARD_SLOT = @variable('Enter a Card Slot')
      )
      CONNECT BY
      PRIOR CIRC_PATH_ELEMENT.CIRC_PATH_INST_ID =
      CIRC_PATH_ELEMENT.PATH_INST_ID
      AND ELEMENT_TYPE != 'K' ) QUER
      WHERE
      circ.circ_path_inst_id = QUER.CIRC_PATH_INST_ID
      and circ.cust_inst_id = cust.cust_inst_id (+)
      ORDER BY
      LEV DESC , CIRC.PATH_NAME ASC, CIRC.BANDWIDTH ASC

      Thanks
      DG