Moreira1985

How can i put my searche more fast without Ca GEN time out

Discussion created by Moreira1985 on Sep 19, 2017
Latest reply on Sep 21, 2017 by Lynn_Williams

I have my query ca gen 

 

229 | | += SUMMARIZE EACH GROUP OF sales_document
230 | | | sales_payment
231 | | | WITH THE SAME sales_payment request
232 | | | sales_document document_number
233 | | | sales_payment entity
234 | | | TARGETING grp_out_saldoc_commit_nr FROM THE BEGINNING UNTIL FULL
235 | | | PLACING sales_payment request INTO out_list_salpay sales_payment request
236 | | | PLACING sales_document document_number INTO out_list_saldoc sales_document document_number
237 | | | PLACING sales_payment entity INTO out_list_salpay sales_payment entity
238 | | | SORTED BY DESCENDING sales_document document_number
239 | | | WHERE DESIRED sales_payment belongs DESIRED sales_document
240 | | | AND DESIRED sales_document invoice_status IS EQUAL TO "P"
241 | | | AND DESIRED sales_payment type IS EQUAL TO "DE"
242 | | | AND DESIRED sales_document commitment_number IS EQUAL TO SPACES

 

The problem is the both tables is very big, one of them has more than 90 million lines... because off that, it always have time out.

 

TIRM030E:  APPLICATION FAILED - UPDATES HAVE BEEN BACKED OUT                   

TIRM031E:  FAILING PROCEDURE EXIT DATA FOLLOWS                                 

TIRM032E:  LAST OR CURRENT ACTION BLOCK ID   =     1900891396                  

TIRM033E:  LAST OR CURRENT ACTION BLOCK NAME  =      AGENT_INVOICING_SEARCH_CO 

TIRM034E:  LAST OR CURRENT DATABASE STATEMENT =      67                        

TIRM035E:   CURRENT STATEMENT BEING PROCESSED =      0000000067                

TIRM038E:   ** FATAL DATABASE ERROR WAS ENCOUNTERED **                         

TIRM039E:                      DB LAST STATUS =      DB                        

--------------------------------------------------------------------------------

ORA-24761: transacção anulada                                                   

                                                                                

SQL-02114: Invalid SQL Cursor usage: trying to CLOSE a CLOSEd cursor           

--------------------------------------------------------------------------------

    RS6E                TIRM046E:     TRANSACTION PROCESSING TERMINATED        

TIRM602E:  REMOTE COOPERATIVE PROCESSING WAS ACTIVE                            

TIRM604E:  PROCEDURE STEP AND TRANCODE TRACE BACK                               

RS_SL_AGENT_INVOICING            RS6E   

 

The tables already has indices wiche has the fields that i am using for my search.

 

There is other ways to put my query more fast with this parameters?

 

thanks. 

Outcomes