3 Replies Latest reply: Nov 29, 2012 9:21 PM by 950217 RSS

    SET operation

      Hi all ,

      Here i a hve a senario with two tables ,table A and B
      table A and B has a one common column is " branch".
      Here i want the Branh columns values from A only that values those values not be in the B bracn column values .ie.e common values should not displayed and apart from the commnon values the remaining values from A only will have to display .No vlaues should not displayed from A

      The query is

      select  "ctr_batch_no"  from A   where  "ctr_batch_no" is   not in ( select distinct ctr_batch_no from B ).
      This is the SQL query i wnatto generate the report with scenario.Please let me know hoe it is possible in OBIEE11g.

      Please help me
        • 1. Re: SET operation
          Srini VEERAVALLI
          Create a new logical column with exp:
          case when B."ctr_batch_no" !=A."ctr_batch_no" then A."ctr_batch_no" end

          If helps pls mark as correct/helpful
          • 2. Re: SET operation
            g.cod_cc_brn BRANCH,
            g.ctr_batch_no BATCHNO,

            s1.cod_user_id INPUTTER,
            s2.cod_user_id AUTHORIZER

            gl_batctrl g, sm_user_profile s1, sm_user_profile s2
            g.ctr_batch_no not in ( select distinct ctr_batch_no from gl_detemp )

            and g.cod_userno = s1.cod_userno
            and g.cod_auth_no = s2.cod_userno
            and g.cod_cc_brn = s1.cod_cc_brn
            and g.cod_cc_brn = s2.cod_cc_brn

            Hi Guys ,

            Please help me how to develop the report from this query in OBIEE .
            Actually i am done the report except this statement g.ctr_batch_no not in ( select distinct ctr_batch_no from gl_detemp )

            The ansewer from this query in data base i am getting is NO ROWS .As upto the development of the repot my level the report is giving four rows. If i implement the above step then the required result will be achieved for me .Bit iam strucked in to implemet the step in report for the complenetion of the report

            Pleas help me how to implement this step .

            Note : I wnat the result apart from the OPpaq view or another view creation in the data base .Please help me .


            Edited by: 970613 on Nov 27, 2012 9:57 PM
            • 3. Re: SET operation
              Hi Kiran,

              OBIEE answers provide one feature called set operations. You can go to criteria and can make use of "combine results based on union, intersection & difference operations" feature (see the double circled plus button on right).

              Hope it'll help to get ur answers.

              Raj Kumar