RE: SQL Problem w/ UPDATE file SET fieldname and IN operator.

["Mackie, Roger L. (Precision Press)" <RLMackie@xxxxxxxxxxxxx>]

John,

I would either STRDBG and display the job log or use an Ops Navigator SQL
Performance Monitor to see if the query optimizer is choosing a read-only
access path for the files that fail.

Good Luck,
Roger Mackie

-----Original Message-----
From: Rusling, John B. (Alliance) [mailto:jbrusling@alliancedev.com]
Sent: Friday, August 02, 2002 10:39 AM
To: 'rpg400-l@midrange.com'
Subject: SQL Problem w/ UPDATE file SET fieldname and IN operator.


SQL Problem w/ UPDATE file SET fieldname and IN operator.

An Item# to be Deleted is entered in the 'DIFIL'
My 'IMDEL' program later is run against the 'SJ' file
 and others to delete or update using the deleted item#'s.

  |||||||||||||||||||||||||||||||||||||
  |- File 'SJ', a File w/ Item#'s &  -|
  |-  other fields and info...       -|
  |-----------------------------------|
  |  SJITNO     QTY FLD   OTHER FLDS  |
  |-----------------------------------|
  |  #01            383   yadda       |
  |  #02            156   yadda       |
  |  #03             20   yadda       |
  |   /\            /\     /\         |
  |   ||            ||     ||         |
  |   \/            \/     \/         |
  |  #1000          145   yadda       |
  |  #1001         5700   yadda       |
  |                                   |
  |||||||||||||||||||||||||||||||||||||


  |||||||||||||||||||
  |- File: 'DIFIL' -|
  |-  File of      -|
  |-  Deleted      -|
  |-  Item#'s      -|
  |-----------------|
  |  DLTIT#   User  |
  |-----------------|
  |  #14      Pat   |
  |  #47      Fred  |
  |  #66      Sally |
  |  #82      Jo    |
  |                 |
  |||||||||||||||||||


The update portion, specifically the sql statement
 below is giving me problems;


 UPDATE sj SET sjitno = 'DELETED ITEM #'
   WHERE sjitno
     IN
    (SELECT dltit#' FROM difil)
     WITH NC

The pgm type is SQLRPGLE.

All of our production files are sequential with logicals
 built over them for either 'READ' or 'UPDATE'.

I copy the production 'base'(PF) files to my testing library.

When I run my 'IMDEL' program some of the files will get
 updated and run the way I expect...  BUT---
 others will give me this message;

View or logical file SJ in TESTLIB read-only.

the additional information is;

*---------------------------------------------------------------------------
-*
Message ID . . . . . . :   SQL0150

Date sent  . . . . . . :   08/02/02      Time sent  . . . . . . :   07:40:57



Message . . . . :   View or logical file SJ in TESTLIB read-only.



Cause . . . . . :   Update, delete, or insert is not allowed. SJ in library

  TESTLIB 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,
  or a column function in the outer-most subselect.

     -- The view or logical file contains a join function.

     -- The view contains a subquery that refers to the same table as the

  table of the outer-most subselect. A view of this type may be used for

  inserting rows.

     -- All the columns of the view are expressions, scalar functions,

  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.

Recovery  . . . :   Change the statement to insert, delete, or update data

  into the base table of view SJ.  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. Try the request again.

*---------------------------------------------------------------------------
-*

Any pointers or clues will be looked on with great glee.

TIA

John B.
<jbrusling@alliancedev.com>

This message best viewed using a mono-spaced font like courier.
_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.