Apply filter on in-memory table after populating

This question has suggested answer(s)

hi,

I want to apply filter on in-memory table after populating base on condition if(fund999 && sum(difference)==0.00) where sum(difference) is aggregated function.

I also have other question can we change the query dataset on run time.?

can we filter in-memory table from  report process class after papulating.?

can we handle this logic on ssrs design mode. where we can add filters on row group properties .. em adding [sum(difference)]=0.00 but this is also not filtering .

please also explain where is best place to handle this logic.

All Replies
  • Conditions on aggregate fields like that are part of the HAVING clause of an SQL statement.

    But, if that's giving you trouble, you can always spool off the aggregate data into a 2nd temporary table, which can even be the same temporary table as the original data, and then you can spool and even delete from that 2nd temporary table as needed.

    In memory temporary tables require a lot of CPU resources to get their work done, so whether this is the "best" approach depends somewhat on how many records and how you need to access them.

  • hmm, you means fill the second temporary table first and then after populating second temp table value of second temp table into original table base on condition .

    e.g

    if(condition==true)

    {

       //fill data in orignal table

    }

    else

    {

    //=======fill data in temp table

    }

    //==========now after filling all table if condition is false===

    while select * from table group by columns where condition

    {

    //insert temp data to orignal data

    }

    can we change query object at run time ?

    can we filter directly in-memory table after populating?

    like

    SELECT AccountNum, AmountCurrencyCredit, AmountCurrencyDebit, ChequeDate,ChequeNum,Credit,CurrencyCode,

    Debit,sum(Difference),Fund999,InvoiceDate,InvoiceDesc,InvoiceNum,Name,Reversed,

    SettledDate,SettledVoucher,TraceNum,TransDate,Txt,Voucher

           from vendTransListTmp

           group by AccountNum, AmountCurrencyCredit, AmountCurrencyDebit, ChequeDate,ChequeNum,Credit,CurrencyCode,

    Debit,Fund999,InvoiceDate,InvoiceDesc,InvoiceNum,Name,Reversed,

    SettledDate,SettledVoucher,TraceNum,TransDate,Txt,Voucher

               where vendTransListTmp.Difference==0.00;

  • I'm not sure why you use (condition == true), but whatever mechanism you construct to take two passes at the data will work.

    You can change the query at run time, because you can use QueryBuild classes with temp tables.

    You can filter directly on in-memory temp tables.

    Your example SQL uses where vendTransListTmp.Different == 0.00 which will work, but could leave you with sum(Difference) that is still 0.00 because the where clause acts before the grouping, so if you have two transactions of -10 and +10 each they will net to 0.00.  You can deal with this using the having clause or by using a delete_from .. where Difference == 0.00 statement later.

  • Please give query filter example.

    on frontend ssrs design mode ventranslisttemp is grouped on [AccountNum] and [Voucher]

    I need to  sum(debit)-sum(credit)=0   where it is 0 remove that row. Following is simple query that is fatching all the row.

     SELECT AccountNum, AmountCurrencyCredit, AmountCurrencyDebit, ChequeDate,ChequeNum,Credit,CurrencyCode,

    Debit,sum,Fund999,InvoiceDate,InvoiceDesc,InvoiceNum,Name,Reversed,

    SettledDate,SettledVoucher,TraceNum,TransDate,Txt,Voucher

           from vendTransListTmp