2 Replies Latest reply: Dec 30, 2015 10:29 AM by Peter Cammaert RSS

    Check itens included in other table

    Danilo Torres

      I have two tables called BILL and PAYMENT.

      How can i create in BILL table a column to indicate only the bills that has a payment in PAYMENT table associated?

        • Re: Check itens included in other table
          Petter Skjolden

          In your load script you could do this:

           

           

           

          PAYMENT:

          LOAD

               BillNo,

               .....

          FROM      // or ;SQL SELECT ...

               .....;

           

           

          HAS_PAYMENTS:

          LOAD DISTINCT

               PaymentBillNo

          RESIDENT

               PAYMENT;

           

          BILL:

          LOAD

               BillNo,

               If( Exists('PaymentBillNo') , 'Y' , 'N') AS HasPayments,

               ....

          FROM        // or ;SQL SELECT ....

               .....;

           

          DROP TABLE HAS_PAYMENTS;

          • Re: Check itens included in other table
            Peter Cammaert

            Same with mapping table. Usually much faster...


            PAYMENT:

            LOAD BillID, ..... // Load everything you need

            FROM DataSource (options);

             

            MapHasPayment:

            MAPPING LOAD DISTINCT BillID, 'Y' AS F2 RESIDENT PAYMENT;

             

            BILL:

            LOAD BillID,

                 applymap('MapHasPayment', BillID, 'N') AS HasPaymentFlag,

                 .... // Load everything you need

            FROM OtherDataSource (options);

             

            No need to drop Mapping Table. Disappears automatically at the end of the script run