5 Replies Latest reply: Jun 9, 2016 1:19 AM by Swetha More RSS

    Subquery in load

    Swetha More

      Hi,

       

      I have a table account_invoice which contains all the invoices. I have to look for the invoices where free samples were given to the customers and if those customers later on bought more products by paying.

       

      account_invoice:

      Load "partner_id",

              "date_invoice",

                .....

                .....;

      SQL Select  "partner_id",

                         "date_invoice",

                          .......

                          .......

      From account_invoice;

       

      Freesamples:

      SELECT partner_id pid,

                    date_invoice din

      FROM account_invoice

      WHERE UPPER(name) like '%SAMPLE%' and amount_untaxed = 0;

          

      Converted:

      Load partner_id,

              date_invoice

      Resident account_invoice

      where exists(pid,partner_id) and amount_untaxed <> 0;

       

      How do i get the list of free sample customers and the converted customers? Should i use a left join or exists? Both dont give the correct resultset, it will be great if somebody could point out what is wrong in the above logic.

       

      Thanks

      Swetha

        • Re: Subquery in load
          Tresesco B

          May be you can rather create a flag field like:

           


          SQL Select  "partner_id",

                             "date_invoice",

                              .......

                              .......

          From account_invoice;

           

          Left Join

           

          SELECT partner_id,

                        date_invoice,

                        If(UPPER(name) like '%SAMPLE%' and amount_untaxed = 0, 'Free Sample',

                              If(mount_untaxed=<>0, 'Converted', 'Others')) as CustomerType

                             

          FROM account_invoice;

           

          And then use this CustomerType filed in the front-end for analysis.

            • Re: Subquery in load
              Swetha More

              tresesco   Thanks for the quick response. I have to find out how many of those free sample customers actually got converted. Hence the flagging logic may not work.

               

              Please let me know if i am missing something here.

               

              Thanks

              Swetha

                • Re: Subquery in load
                  Tresesco B

                  But your conditions of amount_untaxed for both (=0 and <>0) are mutually exclusive, there can't be a common element. Not sure if I am missing some of your points.

                    • Re: Subquery in load
                      Sasidhar Parupudi

                      account_invoice:

                      Load "partner_id",

                              "date_invoice",

                                .....

                                .....;

                      SQL Select  "partner_id",

                                         "date_invoice",

                                          .......

                                          .......

                      From account_invoice;

                       

                      Left Join(account_invoice);

                      SELECT partner_id

                                   IsFreeSampleCustomer

                      resident account_invoice

                      WHERE wildmatch(name, '*SAMPLE*')>0

                       

                      From account_invoice;

                       

                      Left Join(account_invoice);

                      SELECT partner_id

                                   IsUntaxedCustomer

                      resident account_invoice

                      WHERE  amount_untaxed = 0;

                      • Re: Subquery in load
                        Swetha More

                        Thanks for your help . I was able to solve it using inline tables but your approach gave me ideas.

                         

                        Thanks

                        Swetha