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

    SET operation

    973616
      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
            973616
            select
            g.cod_cc_brn BRANCH,
            g.ctr_batch_no BATCHNO,

            s1.cod_user_id INPUTTER,
            s2.cod_user_id AUTHORIZER
            from

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

            and
            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 .

            Thanks
            Kiran

            Edited by: 970613 on Nov 27, 2012 9:57 PM
            • 3. Re: SET operation
              950217
              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.

              Regards
              Raj Kumar