4 Replies Latest reply: Jul 17, 2013 9:15 AM by 650380 RSS

    EXISTS / NOT EXISTS

    650380

      Hi everybody,

      just a quick question. What might be a way or the best way to realize the following SQLs with ODI (11.1.1.7).

      It seems that SQLs with EXISTS / NOT EXISTS are unexpectedly hard to realize.

       

      --Q1

      SELECT a.*

        FROM tab_a a WHERE EXISTS (

             SELECT 1 FROM tab_b b WHERE a.x_id = b.x_id)

         AND a.cdate <= to_date('2012-03-01','YYYY-MM-DD')

         AND ( a.mdate IS NULL

             OR a.mdate >= to_date('2012-03-01','YYYY-MM-DD'));

             

      --Q2

      SELECT a.*, 'X' dataflag

        FROM tab_a a WHERE NOT EXISTS (

             SELECT 1 FROM tab_b b WHERE a.x_id = b.x_id

                AND b.mdate IS NULL )

        AND a.logicflag <> 'Y';

       

      Regards,

      Jens

        • 1. Re: EXISTS / NOT EXISTS
          DecaXD

          Hi,

           

          you need to convert into outer join. For example this

           

           

          SELECT a.*

            FROM tab_a a WHERE EXISTS (

                 SELECT 1 FROM tab_b b WHERE a.x_id = b.x_id)

             AND a.cdate <= to_date('2012-03-01','YYYY-MM-DD')

             AND ( a.mdate IS NULL

                 OR a.mdate >= to_date('2012-03-01','YYYY-MM-DD'));



          • put two datastore (alias TAB_A, TAB_B)
          • join between TAB_A, TAB_B on x_id
          • filter on TAB_A with this text

                              TAB_A.cdate <= to_date('2012-03-01','YYYY-MM-DD') AND ( TAB_A.mdate IS NULL OR TAB_A.mdate >= to_date('2012-03-01','YYYY-MM-DD'));

           

          Let us know

          • 2. Re: EXISTS / NOT EXISTS
            650380

            Hi,

            and thank you. Helpfull answer but works only if tab_b has only one entry per x_id. Unfortunaletly that is not the case. I forgot to mention that.

            It was possible to use the "Distinct" Option on the target table to realize Q1.

             

            Any ideas concerning Q2, the NOT EXISTS Query? As mentioned above, tab_b has more than one entry per x_id.

             

            Regards,

            Jens

            • 3. Re: EXISTS / NOT EXISTS
              DecaXD

              Hi,

               

              first of all you could use subselect temporary interface (if in odi11). To use distinct go to flow tab and check "distinct" checkbox.

               

              For query B:

              • put TAB_A, TAB_B
              • left outer join between TAB_A,TAB_B which this text

                             TAB_A.x_id = TAB_B.x_id AND TAB_B.mdate IS NULL

              • filter on TAB_B
                • TAB_B.x_id is null
              • filter on TAB_A
                • TAB_A.logicflag <> 'Y';

               

              the most important thing is the filter on TAB_B. In this way ODI will filter the query AFTER the join, and the only records that'll be the x_id null will be the record that didn't match the join.

               

              Let me know

              • 4. Re: EXISTS / NOT EXISTS
                650380

                Hi,

                problem solved. Important were the LEFT OUTER JOIN with filter "mdate is null" , the TAB_B.x_id is null Filter and that we switchesd to "Ordered" Joins (we had to activate them in our Oracle Technology).

                 

                 

                • left outer join between TAB_A,TAB_B which this text

                               TAB_A.x_id = TAB_B.x_id AND TAB_B.mdate IS NULL

                • filter on TAB_B
                  • TAB_B.x_id is null

                 

                Thx,

                Jens