4 Replies Latest reply: Jul 13, 2017 5:28 AM by Mohammad Azadi RSS

    exists condition

    Mohammad Azadi

      hello every body

      i have  two table :

       

      Table1:

      LOAD * INLINE [

          ID, Num1

          16110001, 382500

          16110005, 400000

          16110006, 230000

      ];

       

       

      Table2:

      LOAD * INLINE [

          ID, Num2

          16110001, 43000

          16110002, 50000

          16110003, 24000

          16110004, 18000

      ]

      Where not Exists (ID);

       

      result is :

       

      IDNum2
      1611000250000
      1611000324000
      1611000418000

       

      but i want add other condition in where syntax ; so that i check Num1 = Num2 also

      i wrote:

       

      Table2:

      LOAD * INLINE [

          ID, Num2

          16110001, 43000

          16110002, 50000

          16110003, 24000

          16110004, 18000

      ]

      Where not Exists (ID) and (Num1=Num2);


      but send error . what can i do for resolve it?


      Regards ...



        • Re: exists condition
          Prashant Sangle

          You cant in this case join both table then use Num1=num2

           

          Table1:

          LOAD * INLINE [

              ID, Num1

              16110001, 382500

              16110005, 400000

              16110006, 230000

          ];

           

           

          Join

          LOAD * INLINE [

              ID, Num2

              16110001, 43000

              16110002, 50000

              16110003, 24000

              16110004, 18000

          ]

          Where not Exists (ID);

          NoConcatenate

          Final:

          Load * From Table1

          where Num1=Num2;


          Drop table Table1;


          Regards,

            • Re: exists condition
              Mohammad Azadi

              Dear Prashant

              My expectation from result is :

              ID Num2
              1611000143000
              1611000250000
              1611000324000
              1611000418000

              because in my condition (         Where not Exists (ID) and (Num1=Num2)      )

              1. not exists(ID)

              2. Num1=Num2


              ID 16110001 is common field in Table1 and Table2 and should not come to result

              but when we have Num1=Num2 ; so we have ID 16110001 too in result.

            • Re: exists condition
              Arvind Patil

              Hi Mohammad,

               

               

              Use below code may be it will help you:

               

              Table1:

              LOAD * INLINE [

                  ID1, Num1

                  16110001, 382500

                  16110005, 400000

                  16110006, 230000

              ];

               

               

              Table2:

              LOAD * INLINE [

                  ID, Num2

                  16110001, 43000

                  16110002, 50000

                  16110003, 24000

                  16110004, 18000

                  16110004, 18000

              ]

              Where not Exists (ID1,ID);




              Op: will be

              IDNum2
              1611000250000
              1611000324000
              1611000418000

               

              More help please find app.

               

              Thanks,

              Arvind patil

              • Re: exists condition
                Antonio Mancini

                I Think this doesn't make sense.

                ID result  don't have Num1 because they "not Exists" in Table1.