Subquery in load

[#1]

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

[tresB]

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.

[#3]

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

[tresB]

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.

[sasiparupudi1]

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;

[#6]

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

Thanks

Swetha