• If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Announcement

Collapse
No announcement yet.

Valence 5.2 Nitro App Builder - Paging with function in SQL

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Valence 5.2 Nitro App Builder - Paging with function in SQL

    I have created several apps using Nitro App Builder where I have a function in my SQL statement. This is a function we use regularly to retrieve an account's current balance and is used like "select acctbal(accountNumber) as Balance from mylib/myfile". On each app I create with the App Builder, the function works great on the first page of the grid but then gets an error when paging to the next page.

    The error is:

    An unexpected error occurred on the server. See Valence error log for further details.

    Description: SQL statement prep failure (SQLSTT=42845) - SQL0583 - Use of function ACCTBAL in PRODLIB not valid.

    Error Log ID: 7844


    So it works fine on the first page and also for all records when I click my icon to download the results to Excel, but not when I page through on the grid. This is a problem on each of the apps where I have included this function.
    Last edited by roedea; 10-05-2020, 04:20 PM.

  • #2
    That should work... Try upgrading to the latest build. Assuming you're still on 5.2, the current latest build is 5.2.20200819.0.

    If after installing that build the error is still occurring, give us a call on the support line and we'll try to sort it out with you.
    Last edited by robert.swanson; 10-05-2020, 02:48 PM.

    Comment


    • #3
      Ok, I have now upgraded to the latest 5.2 build. The issue is still there on the apps. I will call the support line.

      Comment


      • #4
        Cause of paging problem was determined to be from a non-deterministic function call being included in the SELECT clause, coupled with an OFFSET xx ROWS clause at the end of the statement (which is appended when you start paging).

        We can't make sense of why it would matter whether a function is deterministic or non-deterministic, in conjunction with OFFSET, if the function is not part of the ORDER BY. We've opened a ticket with IBM to explore this further.

        Comment

        Working...
        X