Error ** More than 1024 items in a single statement. Use the -tok parameter. (136)

Mady

Member
Hi All,

Can you please advise how to execute the attached query ? It is getting executed dynamically in the code and I was getting No data found in the end. When I execute this query in editor, I get this error ** More than 1024 items in a single statement. Use the -tok parameter. (136)
 

Attachments

  • Huge Query.txt
    12.9 KB · Views: 20

Cringer

ProgressTalk.com Moderator
Staff member
Wow that is never going to perform very well.
Why not make a temp table with the customer numbers and then make the query:
Code:
FOR  EACH ttCustomer, 
     each order no-lock 
    WHERE order.co_num = "1" 
      and order.warehouse = "primary" 
      and order.customer = ttCustomer.customer :
 

Mady

Member
To me a bit more clear - It happens like below.

1. Query gets prepared through below logic
IF NUM-ENTRIES(searchstring) > 0 THEN
DO i = 1 TO NUM-ENTRIES(searchstring):

cOrd = ENTRY(i,searchstring).

IF i = 1 THEN DO:
IF NUM-ENTRIES(searchstring) > 1 THEN
querystring = querystring
+ ' and ( order.customer = '
+ CHR(34) + cOrd + CHR(34) .

else
querystring = querystring
+ ' AND order.customer = '
+ CHR(34) + cOrd + CHR(34).
END.
ELSE
querystring = querystring
+ ' Or order.customer = '
+ CHR(34) + cOrd + CHR(34) .

2. Then passing this query variable to get executed on appserver, like below

RUN cs_ord.p ON SERVER kServer TRANSACTION DISTINCT
(INPUT querystring,
INPUT RecordLimit,
OUTPUT TABLE temp_order,
OUTPUT viNum,
OUTPUT lRecords) NO-ERROR .

May I know where to modify the -tok parameter ?
 

Stefan

Well-Known Member
-tok is a client startup parameter, so it needs to be on the command line directly or indirectly (-pf) of whatever is starting your ABL session.
 

Mady

Member
I have been trying to find the right place where I can increase the -TOK -INP parameter. Meanwhile, just giving some more information to know if I am missing out on some other important concept.
The query string, which I have given in this post is passed to a program file, which is on the server side. Call happens like below.
Is there any other way possible in the code, to get the huge query accommodated, instead of increasing the client session parameters ?

RUN order_fill.p ON SERVER aServer TRANSACTION DISTINCT
(INPUT Querystring,
INPUT iRecordLimit,
OUTPUT TABLE temp_order,
OUTPUT fiNum,
OUTPUT lMoreRecords) NO-ERROR.

In order_fill.p we have query-prepare and query-open

CREATE QUERY hQry. /* create query */
hQry:set-buffers(hTablBuffer). /* Sets the buffer to use for the query */

/* Open the query */
hQry:query-prepare(querystring).
hQry:query-open().

/* Get the first record */
hQry:get-first().

/* Like a for each block */
query1:
repeat while hTablBuffer:available:

/* Edit the Record Count Limit if set by user. */
iRecordCount = iRecordCount + 1.
if iRecordLimit ne 0 and iRecordCount > iRecordLimit then do:
lMoreRecords = TRUE.
leave query1.
end. /* iRecordLimit check */

create tt_ord.
hTTOrd = BUFFER tt_ord:HANDLE.
httOrd:buffer-copy(hTablBuffer).
 

Cringer

ProgressTalk.com Moderator
Staff member
Please take a look at my suggestion from May 13th. You could easily pass the customer numbers to the back end and build a temp-table there to do the query. Not only will the query not break -tok, it will also almost certainly run quicker.
 

Mady

Member
Thank you very much Cringer. I put in some time for the temp table approach and yes I was able to achieve the result and also no need to increase the client session parameter.
Thank you very much Stefan.
 
Top