If it is a multi-user app, using temporary tables means all sessions or users update the same table. You may need to add a session ID as a key to solve this, although it then needs cleaning up. An automatic, per-session temporary table database would be an awesome thing.
I know one ERP system which builds an intermediate table when reporting AR Aging (for performance). It keys this temporary table with "runid", giving each logged-in user a unique run id. It cleans up old run IDs. On Friday, 27 March 2015 05:38:00 UTC+11, LaDarrius Stewart wrote: > > WITH X (CURRENCY_CODE, AR_CONTROL_ACCT, CLIENT_ID, BILL_NUMBER) AS > ( > SELECT > A.CURRENCY_CODE, > A.AR_CONTROL_ACCT, > A.CLIENT_ID, > A.BILL_NUMBER > FROM > AR_TRANSACTIONS_ALL A > WHERE > A.SOURCE_AUDIT <> 0 > AND A.TX_POSTED = 'True' > AND A.TX_DATE <= TIMESTAMP('{0}', '23.59.59') > GROUP BY > A.CURRENCY_CODE, A.AR_CONTROL_ACCT, A.CLIENT_ID, A.BILL_NUMBER > HAVING > ROUND(SUM(A.TX_AMT),2) <> 0 > ) > > SELECT > 1 RECTYPE, > A.CLIENT_ID, > C.NAME, > A.CURRENCY_CODE, > (SELECT NAME FROM UNIT WHERE UNIT_TYPE = 'Y' AND UNIT_SYMBOL = > A.CURRENCY_CODE FETCH FIRST ROW ONLY) CURRENCY_NAME, > A.AR_CONTROL_ACCT, > A.BILL_NUMBER, > (SELECT DATE(AGING_DATE) FROM AR_SUM_ALL WHERE CLIENT_ID = A.CLIENT_ID > AND BILL_NUMBER = A.BILL_NUMBER FETCH FIRST ROW ONLY) AGING_DATE, > A.TX_TYPE, > DATE(A.TX_DATE) TX_DATE, > A.TX_AMT, > USER USER_ID, > A.COMPANY_ID, > P.NAME COMPANY_NAME, > C.ALT_CONTACT BILL_NAME, > C.ALT_BUSINESS_PHONE BILL_PHONE, > (C.NAME || ' - ' || A.CLIENT_ID) CLIENT_GROUP > FROM > X, COMPANY_INFO P, CLIENT_ALL C, AR_TRANSACTIONS_ALL A > WHERE > P.COMPANY_INFO_ID = A.COMPANY_ID > AND C.CLIENT_ID = A.CLIENT_ID > AND X.CURRENCY_CODE = A.CURRENCY_CODE > AND X.AR_CONTROL_ACCT = A.AR_CONTROL_ACCT > AND X.CLIENT_ID = A.CLIENT_ID > AND X.BILL_NUMBER = A.BILL_NUMBER > AND A.TX_POSTED = 'True' > AND A.CLIENT_ID = '{1}' > AND A.TX_DATE <= TIMESTAMP('{0}', '23.59.59') > > There is a snippet of the query its a bit longer than that a few unions > and some case statements . Johann and Manuele that thought never crossed my > mind I'll give that a try and get back with the result. Thank You ahead of > time. > > This message is for named person(s) only. It may contain confidential > and/or legally privileged information. No confidentiality or privilege is > waived or lost should mis-transmission occur. If you receive this message > in error, delete it (and all copies) and notify the sender. You must not, > directly or indirectly,use, disclose, distribute, print, or copy any part > of this message if you are not the intended recipient. GAD GROUP > TECHNOLOGY, INC. reserves the right to monitor all e-mail communications > through its networks. > > Any views expressed in this message are those of the individual sender, > except where the message states otherwise and the sender is authorized to > state them to be the views of any such entity. > > This e-mail has been virus and content scanned by GAD GROUP TECHNOLOGY, > INC. > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.