• 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.

NAB : editgrid - not updating record

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

  • NAB : editgrid - not updating record

    I have and editgrid, and when performing and update on a field, message is returned that 'record was updated' however it is not in the database..

    i have a feeling it is with the Datasource, which has joins to other files, that are resulting in this....


    is this a bug ?, or is what i'm trying to achieve not even possible with my Sql Statement ..


    PS. the files in the join are views...

    (i've left out some fields)
    Code:
    SELECT
      ecxseller,
      ecxplant,
      ecytest,
      eccsite,
      ...
      ecnlvad,
    FROM edicusppf 
         LEFT OUTER JOIN cxxv2 ON eccsite = cxcsite and cxcuno = ecnklnt
         LEFT OUTER JOIN coblavc ON eccsite = lacsite and lanklnt = ecnklnt and lanlvad = ecnlvad
    ORDER BY ecytest, ecnklnt, ecnlvad
    this is what is defined in my editgrid

    Image 005.png


    In the grid, i want to change f.e the address nbr
    Image 006.png

    when double clicking, i get the edit window
    Image 007.png

    when updated.. notificiation is sent
    Image 008.png


    but the record is not updated.. after refreshing nor after verifying in the Database
    Don't see any messages neither in the console log, nor on the iSeries...
    Attached Files

  • #2
    Hi Thierry,

    When you go into your edit grids data source do you see RRN's in the result set of the SQL statement?

    For example:
    Code:
    SELECT
      cusno,
      cname,
      caddr1,
      caddr2,
      ccity,
      cstate,
      ccountry,
      czip,
      cytdsales,
      clastact,
    rrn(DEMOCMAST) AS rrn1
    FROM
      democmast

    Comment


    • #3
      Hi Thierry,

      Perhaps the issue is that you're trying to update a data source based on a view rather than a collection of regular tables, physical files or logical files. I suspect the update attempt may be throwing an SQL0150 error that perhaps we're not currently trapping (we'll look into that).


      The SQL0150 message details would look something like this:

      Message . . . . : View, index, or table YOUR_VIEW in YOUR_LIB read-only.

      Cause . . . . . : Update, delete, insert, merge, or truncate is not allowed.
      YOUR_VIEW in YOUR_LIB can be used only for read operations.
      A view or logical file can be used only for read operations if one or more
      of the following conditions are true:
      -- The view contains a DISTINCT keyword, GROUP BY clause, HAVING clause,
      CONNECT BY clause, FOR SYSTEM_TIME clause, or an aggregate function in the
      outer fullselect.
      -- The view or logical file references more than one table in the outer
      fullselect.
      -- The view contains a subquery that refers to the same table as the
      table of the outer fullselect. A view of this type may be used for inserting
      rows.
      -- The view contains UNION, EXCEPT, or INTERSECT in the outer fullselect.
      -- All the columns of the view are expressions, constants, or special
      registers.
      -- All the columns of the logical file are input only.
      -- The select list of the view omits a column of the based on table that
      does not allow null values or default values. Inserting into the view is
      not allowed.
      A history table cannot be modified directly.
      Recovery . . . : Change the statement to insert, delete, update, merge, or
      truncate data using the base table of XXX. All columns of the table that do
      not allow null values or default values must be assigned a value when
      inserting a row into a table or view unless an INSTEAD OF trigger is defined
      for the view. To modify data in a history table, drop versioning first. Try
      the request again.


      If you have SQL logging for NAB data sources turned on in settings, you should be able to see the attempted UPDATE statement in file VVGENLOG, or in the Valence 6.1 Portal Admin > Logs, click on the appropriate call record and then look at the Log Data tab.

      If you can find that SQL statement and try to run it manually, perhaps you could confirm it is indeed a View-related error.

      Comment


      • #4
        The primary file is not a view..just a old PF...
        but the ones i use in the join are views.

        The rrn wasn't automatically added .....
        (i tried earlier already to add it manually, but did not help)



        looked now in the logs and i see : update successfull, nbr of records updated : 0

        so i guess this means not to use views in joins?

        Image 009.png

        Comment


        • #5
          I replaced the views that were in the left outer join with the physical files and now the RRN-fields were added automatically....
          and the updates are working now...

          which is fine for me now....

          just as a title of information:
          disadvantage however is that i now have to hardcode the libraries of the files. whereas with specifying the view, i could rely on the library list where the view was created and was taking over the location from library specified in the valence environment...
          with this is do not have to worry when switcing over from my Production and Test environment...
          Now i need to make a change in the datasource when i want to switch between my Test environment and my production environment..


          Code:
          SELECT
            ecxseller,  ecxplant,  ecytest, eccsite, ecnklnt,
            CASE WHEN eccsite = 'TLT' THEN tltcxx.cxcunm  ELSE budcxx.cxcunm  END AS cxcunm,
            CASE WHEN eccsite = 'TLT' THEN tltcxx.cxcua1  ELSE budcxx.cxcua1  END AS  cxcua1,
            CASE WHEN eccsite = 'TLT' THEN tltcxx.cxcua2  ELSE budcxx.cxcua2  END AS cxcua2,
            CASE WHEN eccsite = 'TLT' THEN tltcxx.cxpccd  ELSE budcxx.cxpccd  END AS cxpccd,
            CASE WHEN eccsite = 'TLT' THEN tltcxx.cxpocd  ELSE budcxx.cxpocd  END AS cxpocd,
            CASE WHEN eccsite = 'TLT' THEN tltcxx.cxpocd  ELSE budcxx.cxpocd  END AS cxpocd,
            CASE WHEN eccsite = 'TLT' THEN tltbla.laxcunm ELSE budbla.laxcunm END AS laxcunm,
            CASE WHEN eccsite = 'TLT' THEN tltbla.laxcua1 ELSE budbla.laxcua1 END AS laxcua1,
            CASE WHEN eccsite = 'TLT' THEN tltbla.laxcua2 ELSE budbla.laxcua2 END AS laxcua2,
            CASE WHEN eccsite = 'TLT' THEN tltbla.laxpccd ELSE budbla.laxpccd END AS laxpccd,
            CASE WHEN eccsite = 'TLT' THEN tltbla.laxpocd ELSE budbla.laxpocd END AS laxpocd,
            CASE WHEN eccsite = 'TLT' THEN tltbla.laxcuct ELSE budbla.laxcuct END AS laxcuct,
            CASE WHEN eccsite = 'TLT' THEN tltbla.lacdcl1 ELSE budbla.lacdcl1 END AS lacdcl1,
            ecnlvad, ecxsndrc, ecdcrea, echcrea, eccuscr, ecdupdt, echupdt, eccusup,
            rrn(EDICUSPPF) AS rrn1,
            rrn(tltcxx) AS rrn2,
            rrn(budcxx) AS rrn3,
            rrn(tltbla) AS rrn4,
            rrn(budbla) AS rrn5
          FROM edicusppf
             LEFT OUTER JOIN codat/cxx AS tltcxx ON tltcxx.cxcuno = ecnklnt
             LEFT OUTER JOIN codathu/cxx AS budcxx ON budcxx.cxcuno = ecnklnt
             LEFT OUTER JOIN codat/coblal1 AS tltbla ON tltbla.lanklnt = ecnklnt and tltbla.lanlvad = ecnlvad
             LEFT OUTER JOIN codathu/coblal1 AS budbla ON budbla.lanklnt = ecnklnt and budbla.lanlvad = ecnlvad
          ORDER BY ecytest, ecnklnt, ecnlvad


          Comment


          • #6
            We're looking into why the inclusion of views in the data source is apparently stopping you from updating fields in the regular physical file. So you may well be able to go back to using your original statement if we can figure that out. We'll get back to you.

            Comment


            • #7
              Hi Thierry,

              FYI, we got this sorted out. On the next build you'll be able to create Edit Grids over data sources that include views or CTEs. Of course, only columns coming from regular files will be editable. You will need to re-save any existing data sources so that the appropriate RRN columns are injected.

              Comment

              Working...
              X