https://netsuiteprofessionals.com logo
Join SlackCommunities
Powered by
Hello! We're getting SSS_SEARCH_ERROR_OCCURRED. Qu...
# suiteql
j
Hello! We're getting SSS_SEARCH_ERROR_OCCURRED. Query is working as expected with around 3k or 5k results, but stopped working when we're expecting around 8k results. I don't think this should be an issue since NS should just return 5k results. To further isolate, I tried to use runSuiteQLPaged and it still didn't work for large results. Could it be a NS defect?
c
Can you share your query in a snippet?
j
Copy code
let SQL= `
        SELECT DISTINCT BUILTIN.DF(grade.custrecord_tqc_grade_item)            AS
                        displayname,
                        grade.id                                               AS
                        grade_detail_id,
                        grade.custrecord_tqc_grade_item_number,
                        grade.NAME,
                        grade.custrecord_tqc_grade_qty,
                        BUILTIN.DF(grade.custrecord_tqc_grade)                 AS grade,
                        BUILTIN.DF(grade.custrecord_tqc_grade_classification)  AS
                        gradeclass,
                        grade.custrecord_tqc_grade_comment,
                        grade.custrecord_tqc_grade_salescomment,
                        grade.custrecord_tqc_grade_to_allocation,
                        BUILTIN.DF(grade.custrecord_tqc_grade_to_allocation)   AS
                        to_alloc_number,
                        grade.custrecord_tqc_grade_to_lineid                   AS
                        to_line_id,
                        BUILTIN.DF(grade.custrecord_tqc_grade_so_allocation)   AS
                        so_alloc_number,
                        grade.custrecord_tqc_grade_so_lineid                   AS
                        so_line_id,
                        BUILTIN.DF(grade.custrecord_tqc_grade_icso_allocation) AS
                        icso_alloc_number,
                        grade.custrecord_tqc_grade_icso_lineid                 AS
                        icso_line_id,
                        grade.custrecord_tqc_grade_so_allocation,
                        grade.custrecord_tqc_grade_icso_allocation,
                        rfs.custrecord_rfs_lp_line_detail_lpn                  AS
                        lincense_plate,
                        ( CASE
                            WHEN TO.status IN ( 'B', 'F' ) THEN 'T'
                            WHEN ICSO.po_status IN ( 'B' ) THEN 'T'
                            WHEN ( invAssignment.location != 219
                                   AND ( invAssignment.quantityonhand > 0
                                          OR invAssignment.quantityintransit > 0 ) )
                          THEN 'T'
                            ELSE 'F'
                          END )                                                AS
                        in_transit
        FROM   customrecord_tqc_quality_grade grade
               LEFT JOIN (SELECT t.id,
                                 t.status,
                                 t.transferlocation
                          FROM   TRANSACTION t
                          WHERE  t.type = 'TrnfrOrd') TO
                      ON TO.id = grade.custrecord_tqc_grade_to_allocation
                         AND TO.id != 804218
               LEFT JOIN (SELECT t.id,
                                 t.status,
                                 poLine.location AS destination_location,
                                 po.status       AS po_status
                          FROM   TRANSACTION t
                                 JOIN TRANSACTION po
                                   ON t.intercotransaction = po.id
                                 JOIN transactionline poLine
                                   ON po.id = poLine.TRANSACTION
                                      AND poLine.mainline = 'T'
                          WHERE  t.type = 'SalesOrd') ICSO
                      ON ICSO.id = grade.custrecord_tqc_grade_icso_allocation
                         AND ICSO.id != 804218
               LEFT JOIN customrecord_rfs_lp_line_detail rfs
                      ON grade.NAME = rfs.custrecord_rfs_lp_line_detail_inv_number
               JOIN (SELECT BUILTIN.DF(invNumLoc.inventorynumber) AS invNum,
                            invNumLoc.inventorynumber,
                            invNumLoc.quantityavailable,
                            invNumLoc.quantityonhand,
                            invNumLoc.quantityintransit,
                            invNumLoc.location
                     FROM   inventorynumber
                            LEFT JOIN inventorynumberlocation invNumLoc
                                   ON inventorynumber.id = invNumLoc.inventorynumber)
                      invAssignment
                 ON invAssignment.invnum = grade.NAME
               LEFT JOIN (SELECT inventoryassignment.id,
                                 inventoryassignment.inventorynumber,
                                 TRANSACTION.transferlocation,
                          FROM   inventoryassignment
                                 JOIN transactionline
                                   ON transactionline.id =
                                      inventoryassignment.transactionline
                                      AND transactionline.TRANSACTION =
                                          inventoryassignment.TRANSACTION
                                 JOIN TRANSACTION
                                   ON inventoryassignment.TRANSACTION = TRANSACTION.id
                          WHERE  transactionline.item = 14377
                                 AND TRANSACTION.type = 'ItemShip'
                                 AND TRANSACTION.transferlocation = 219)
               inventoryAssignedOnFulfilledTO
                      ON inventoryAssignedOnFulfilledTO.inventorynumber =
                         grade.custrecord_tqc_grade_inv_detail_ref
        WHERE  grade.id IN (SELECT Min(grade.id)
                            FROM   customrecord_tqc_quality_grade grade
                            GROUP  BY grade.custrecord_tqc_grade_inv_detail_ref)
               AND grade.custrecord_tqc_grade_item = 14377
               AND ( ( grade.custrecord_tqc_grade_to_allocation = 804218
                       AND grade.custrecord_tqc_grade_to_lineid = 1 )
                      OR ( grade.custrecord_tqc_grade_so_allocation IS NULL
                           AND grade.custrecord_tqc_grade_icso_allocation IS NULL
                           AND grade.custrecord_tqc_grade_to_allocation IS NULL
                           AND invAssignment.quantityavailable > 0
                           AND invAssignment.location = 219 )
                      OR ( grade.custrecord_tqc_grade_so_allocation IS NULL
                           AND grade.custrecord_tqc_grade_to_allocation IS NULL
                           AND ICSO.destination_location = 219
                           AND invAssignment.location = 219 )
                      OR ( grade.custrecord_tqc_grade_so_allocation IS NULL
                           AND grade.custrecord_tqc_grade_icso_allocation IS NULL
                           AND TO.transferlocation = 219
                           AND invAssignment.location = 219
                           AND invAssignment.quantityonhand > 0 )
                      OR ( grade.custrecord_tqc_grade_so_allocation IS NOT NULL
                           AND grade.custrecord_tqc_grade_icso_allocation IS NOT NULL
                           AND TO.status IN ( 'G' )
                           AND ICSO.destination_location = 219
                           AND invAssignment.location = 219 )
                      OR ( grade.custrecord_tqc_grade_so_allocation IS NULL
                           AND inventoryAssignedOnFulfilledTO.transferlocation = 219
                           AND ( invAssignment.quantityonhand > 0
                                  OR invAssignment.quantityintransit > 0 ) ) )
        ORDER  BY grade.NAME 
        ;
    `;
c
what's changed between it working and it erroring?
j
Basically only 219, 804218, and 14377 being changed
Here's a sample where it worked:
Copy code
SELECT DISTINCT BUILTIN.DF(grade.custrecord_tqc_grade_item)            AS
                displayname,
                grade.id                                               AS
                grade_detail_id,
                grade.custrecord_tqc_grade_item_number,
                grade.NAME,
                grade.custrecord_tqc_grade_qty,
                BUILTIN.DF(grade.custrecord_tqc_grade)                 AS grade,
                BUILTIN.DF(grade.custrecord_tqc_grade_classification)  AS
                gradeclass,
                grade.custrecord_tqc_grade_comment,
                grade.custrecord_tqc_grade_salescomment,
                grade.custrecord_tqc_grade_to_allocation,
                BUILTIN.DF(grade.custrecord_tqc_grade_to_allocation)   AS
                to_alloc_number,
                grade.custrecord_tqc_grade_to_lineid                   AS
                to_line_id,
                BUILTIN.DF(grade.custrecord_tqc_grade_so_allocation)   AS
                so_alloc_number,
                grade.custrecord_tqc_grade_so_lineid                   AS
                so_line_id,
                BUILTIN.DF(grade.custrecord_tqc_grade_icso_allocation) AS
                icso_alloc_number,
                grade.custrecord_tqc_grade_icso_lineid                 AS
                icso_line_id,
                grade.custrecord_tqc_grade_so_allocation,
                grade.custrecord_tqc_grade_icso_allocation,
                rfs.custrecord_rfs_lp_line_detail_lpn                  AS
                lincense_plate,
                ( CASE
                    WHEN TO.status IN ( 'B', 'F' ) THEN 'T'
                    WHEN ICSO.po_status IN ( 'B' ) THEN 'T'
                    WHEN ( invAssignment.location != 218
                           AND ( invAssignment.quantityonhand > 0
                                  OR invAssignment.quantityintransit > 0 ) )
                  THEN 'T'
                    ELSE 'F'
                  END )                                                AS
                in_transit
FROM   customrecord_tqc_quality_grade grade
       LEFT JOIN (SELECT t.id,
                         t.status,
                         t.transferlocation
                  FROM   TRANSACTION t
                  WHERE  t.type = 'TrnfrOrd') TO
              ON TO.id = grade.custrecord_tqc_grade_to_allocation
                 AND TO.id != 645690
       LEFT JOIN (SELECT t.id,
                         t.status,
                         poLine.location AS destination_location,
                         po.status       AS po_status
                  FROM   TRANSACTION t
                         JOIN TRANSACTION po
                           ON t.intercotransaction = po.id
                         JOIN transactionline poLine
                           ON po.id = poLine.TRANSACTION
                              AND poLine.mainline = 'T'
                  WHERE  t.type = 'SalesOrd') ICSO
              ON ICSO.id = grade.custrecord_tqc_grade_icso_allocation
                 AND ICSO.id != 645690
       LEFT JOIN customrecord_rfs_lp_line_detail rfs
              ON grade.NAME = rfs.custrecord_rfs_lp_line_detail_inv_number
       JOIN (SELECT BUILTIN.DF(invNumLoc.inventorynumber) AS invNum,
                    invNumLoc.inventorynumber,
                    invNumLoc.quantityavailable,
                    invNumLoc.quantityonhand,
                    invNumLoc.quantityintransit,
                    invNumLoc.location
             FROM   inventorynumber
                    LEFT JOIN inventorynumberlocation invNumLoc
                           ON inventorynumber.id = invNumLoc.inventorynumber)
              invAssignment
         ON invAssignment.invnum = grade.NAME
       LEFT JOIN (SELECT inventoryassignment.id,
                         inventoryassignment.inventorynumber,
                         TRANSACTION.transferlocation,
                  FROM   inventoryassignment
                         JOIN transactionline
                           ON transactionline.id =
                              inventoryassignment.transactionline
                              AND transactionline.TRANSACTION =
                                  inventoryassignment.TRANSACTION
                         JOIN TRANSACTION
                           ON inventoryassignment.TRANSACTION = TRANSACTION.id
                  WHERE  transactionline.item = 14353
                         AND TRANSACTION.type = 'ItemShip'
                         AND TRANSACTION.transferlocation = 218)
       inventoryAssignedOnFulfilledTO
              ON inventoryAssignedOnFulfilledTO.inventorynumber =
                 grade.custrecord_tqc_grade_inv_detail_ref
WHERE  grade.id IN (SELECT Min(grade.id)
                    FROM   customrecord_tqc_quality_grade grade
                    GROUP  BY grade.custrecord_tqc_grade_inv_detail_ref)
       AND grade.custrecord_tqc_grade_item = 14353
       AND ( ( grade.custrecord_tqc_grade_to_allocation = 645690
               AND grade.custrecord_tqc_grade_to_lineid = 1 )
              OR ( grade.custrecord_tqc_grade_so_allocation IS NULL
                   AND grade.custrecord_tqc_grade_icso_allocation IS NULL
                   AND grade.custrecord_tqc_grade_to_allocation IS NULL
                   AND invAssignment.quantityavailable > 0
                   AND invAssignment.location = 218 )
              OR ( grade.custrecord_tqc_grade_so_allocation IS NULL
                   AND grade.custrecord_tqc_grade_to_allocation IS NULL
                   AND ICSO.destination_location = 218
                   AND invAssignment.location = 218 )
              OR ( grade.custrecord_tqc_grade_so_allocation IS NULL
                   AND grade.custrecord_tqc_grade_icso_allocation IS NULL
                   AND TO.transferlocation = 218
                   AND invAssignment.location = 218
                   AND invAssignment.quantityonhand > 0 )
              OR ( grade.custrecord_tqc_grade_so_allocation IS NOT NULL
                   AND grade.custrecord_tqc_grade_icso_allocation IS NOT NULL
                   AND TO.status IN ( 'G' )
                   AND ICSO.destination_location = 218
                   AND invAssignment.location = 218 )
              OR ( grade.custrecord_tqc_grade_so_allocation IS NULL
                   AND inventoryAssignedOnFulfilledTO.transferlocation = 218
                   AND ( invAssignment.quantityonhand > 0
                          OR invAssignment.quantityintransit > 0 ) ) )
ORDER  BY grade.NAME
c
Do you expect 8k results specifically because you're getting back a result count and then encountering the error when you attempt to iterate over results and read values?
j
The expected result is from another report outside NS. We can't even get a response from 
query.runSuiteQL({ query: SQL })
to get the exact result count. That is where we get the error.
j
I wonder if there’s a particular row in the dataset that it doesn’t like for some reason
j
That would be very hard to catch. Haha
49 Views
Open in Slack
PreviousNext
Powered by