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 [email protected].
For more options, visit https://groups.google.com/d/optout.
