6 Replies Latest reply: Feb 13, 2014 8:21 AM by Srikanth P RSS

    where exists not filtering data

      I am loading "Dealer Id" from a table then filtering the data while loading another table using where exists clause(dealer id,dealer no).

       

      Filtering is done for above table.

      then,

       

      concatenate another table

       

      while loading this table using where exists clause(dealer id,dealer no).

       

      but data is not filtered for 2nd table.

       

       

       

      why exists not working in 2nd table?

       

      Please help.

        • Re: where exists not filtering data
          Vishwaranjan Kumar

          hi

          i think try according to this,

          Table1:

          load  * from  Table1;

          join

          Table2:

          load  * from  Table2 where exits(dealer id,dealer no) ;

           

          Table11:

          load  * from  Table11;

          join

          Table22:

          load  * from  Table22 where exits(dealer id,dealer no) ;

           

          Output1:

          load * resident Table1;

          concatenate

          Output2:

          load * resident Table11;


          drop tables Table1,Table11; // if  you change name of field in Output1 and Output2 tables.

          • Re: where exists not filtering data
            Mohit Sharma

            I think because in first condition your where exist condition will get those records which is loaded above so far.

            so, after again use this exist condition for another table on the basis of filtering table the condition will get only those records which are in all three tables and second time used where exists condition doesn't give you the exact result which you want So, you have to load 2nd table two times like below and make join between them

            T1:

            LOAD RowNo() as Key,* INLINE [

                PId, PValue

                1, 10

                2, 20

                3, 30

                4, 40

                5, 50

                6, 60

            ];

            Join

            T2:

            LOAD RowNo() as Key,* INLINE [

                PId, PValue

                4, 70

                5, 80

                6, 90

                7, 100

                8, 110

                9, 120

            ]Where Exists(PId);

            Join

            AgainT2:

            LOAD RowNo() as Key,* INLINE [

                PId, PValue

                4, 70

                5, 80

                6, 90

                7, 100

                8, 110

                9, 120

            ];

            Join

            T3:

            LOAD

            PId ,PValue Where Exists(PId);

             

            LOAD RowNo() as Key, * Inline [

              PId,PValue

              7, 15

                8, 30

                9, 45

                10, 60

                11, 65

                ];

            //