× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.