thank you, Chris , for the explanation will commit this for the moment as possible sql-definition
2013/12/13 Chris Travers <[email protected]>: > > > > On Fri, Dec 13, 2013 at 2:30 AM, herman vierendeels > <[email protected]> wrote: >> >> I would like to add a view to track ap for which payments are not ok >> (not paid, paid too little, paid too much) > > > Ideally if we are going to go this way, it might make sense to design a view > which can be used by our stored procedures. >> >> >> Is this ok? >> >> Could someone other check validity of view-logic: >> >> View definition: >> WITH cte(ap_id, ac_amount) AS ( >> SELECT ap.id, sum(ac.amount) AS sum >> FROM ap ap >> JOIN acc_trans ac ON ac.trans_id = ap.id >> JOIN account cnt ON cnt.id = ac.chart_id >> JOIN account_link al ON al.account_id = cnt.id >> WHERE al.description = 'AP'::text >> GROUP BY ap.id >> HAVING sum(ac.amount) <> 0::numeric >> ) >> SELECT cte.ac_amount, e.name, ap.id, ap.invnumber, ap.transdate, >> ap.taxincluded, ap.amount, ap.netamount, ap.duedate, ap.invoice, >> ap.ordnumber, ap.curr, ap.notes, ap.person_id, ap.till, ap.quonumber, >> ap.intnotes, ap.shipvia, ap.language_code, ap.ponumber, >> ap.shippingpoint, ap.on_hold, ap.approved, ap.reverse, ap.terms, >> ap.description, ap.force_closed, ap.crdate >> FROM ap ap, entity_credit_account eca, entity e, cte cte >> WHERE ap.id = cte.ap_id AND ap.entity_credit_account = eca.id AND >> eca.entity_id = e.id; > > > Based on the rewrite below, I don't see anything wrong with your logic. I > think your view definition would work for businesses with a relatively small > number of AP transactions. I think you will run into issues with more AP > centered workflows (such as financial services businesses), so I would > probably prefer to optimize it (see below). The optimizations below are > just a matter of transforming what you have written. > > CTE's are optimization fences, so you effectively have two scans on ap, > which may be a problem for larger data sets (for example some of our > financial services users). You don't get anything by putting that logic in > the CTE, and in fact you lose optimization possibilities. So if you are > going to go this way, unfold your CTE and put it in the main query. I would > also suggest that explicit joins are likely to be easier to manage down the > road. Also, the join on account is unnecessary since you are joining all > the way across on the same values to account_link. So rewriting this you > have: > > > SELECT sum(ac.amount) as balance, e.name, ap.id, ap.invnumber, > ap.transdate, > ap.taxincluded, ap.amount, ap.netamount, ap.duedate, > ap.invoice, > ap.ordnumber, ap.curr, ap.notes, ap.person_id, ap.till, > ap.quonumber, > ap.intnotes, ap.shipvia, ap.language_code, ap.ponumber, > ap.shippingpoint, ap.on_hold, ap.approved, ap.reverse, > ap.terms, > ap.description, ap.force_closed, ap.crdate > FROM ap > JOIN entity_credit_account eca ON ap.entity_credit_account = eca.id > JOIN entity e ON eca.entity_id = e.id > JOIN acc_trans ac ON ac.trans_id = ap.id > JOIN account_link al ON al.description = 'AP'::text AND al.account_id = > ac.chart_id > GROUP BY e.name, ap.id, ap.invnumber, ap.transdate, > ap.taxincluded, ap.amount, ap.netamount, ap.duedate, ap.invoice, > ap.ordnumber, ap.curr, ap.notes, ap.person_id, ap.till, ap.quonumber, > ap.intnotes, ap.shipvia, ap.language_code, ap.ponumber, > ap.shippingpoint, ap.on_hold, ap.approved, ap.reverse, ap.terms, > ap.description, ap.force_closed, ap.crdate > HAVING sum(ac.amount) <> 0; > > This is, essentially, the same logic and it does away with the extra scan > through ap, and any scans on account. That should show the balance due in > the first column. > > Also it is worth noting that due to SQL-Ledger rounding errors, we set the > threshold at $0.005 to pay (for those migrating there) so you might end up > with some small amounts on migrated databases. If this is a problem we'd > change the having statement to: > > HAVING abs(sum(ac.amount)) > 0.005 > > To be honest, I don't know whether we want to do that (setting the threshold > to 0.005) out of the box or implement that as an add-on for those who need > it (my vote is probably for the latter because these sorts of efforts to > cover up bugs of other programs in the past may cover up bugs in the > present. So I would probably stick with the main rewrite and the original > HAVING statement. It's worth knowing though. > > One question I have is whether we should put such views in a dedicated > reporting schema (maybe lsmb_reporting?). If the goal is external access > from spreadsheets, this would make this easier to manage and maybe provide > dedicated space for others for reports too. > -- > Best Wishes, > Chris Travers > > Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor > lock-in. > http://www.efficito.com/learn_more.shtml > > ------------------------------------------------------------------------------ > Rapidly troubleshoot problems before they affect your business. Most IT > organizations don't have a clear picture of how application performance > affects their revenue. With AppDynamics, you get 100% visibility into your > Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics > Pro! > http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk > _______________________________________________ > Ledger-smb-devel mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel > ------------------------------------------------------------------------------ Rapidly troubleshoot problems before they affect your business. Most IT organizations don't have a clear picture of how application performance affects their revenue. With AppDynamics, you get 100% visibility into your Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro! http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk _______________________________________________ Ledger-smb-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
