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

Thank you Roger and Clare.

It appears the file 'DIFIL' (Deleted Items FILe), well actually, it's a copy
of the production file used for this job only,  ...is the culprit.
(or should I say, the guy who's using the difil <g>)

Anyway, I create the difil using embedded sql and fill it using embedded sql
from the production file.
It would work for some files but there were a few exceptions.

I remembered reading something about doing UPDATES and making sure that
files used on them need to be keyed.

I changed the create table command so the difil has a key and it runs great.

John B.



--__--__--

Message: 1
From: "Clare Babcock" <cbabcock@sympatico.ca>
To: <RPG400-L@midrange.com>
Subject: SQL Problem w/ UPDATE file SET fieldname and
Date: Fri, 2 Aug 2002 13:38:35 -0500
Reply-To: rpg400-l@midrange.com

This is a multi-part message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
John B.

Is it possible that the authorities on the two sets of files "live vs test"
are different. ? Perhaps the INSERT privilege needs to be granted first?

--


--__--__--


   6. SQL Problem w/  UPDATE file   SET fieldname and   IN operator.
(Rusling, John B. (Alliance))
   7. RE: SQL Problem w/  UPDATE file   SET fieldname and   IN operator
       . (Mackie, Roger L. (Precision Press))


Message: 6
From: "Rusling, John B. (Alliance)" <jbrusling@alliancedev.com>
To: "'rpg400-l@midrange.com'" <rpg400-l@midrange.com>
Subject: SQL Problem w/  UPDATE file   SET fieldname and   IN operator.
Date: Fri, 2 Aug 2002 10:38:59 -0500
Reply-To: rpg400-l@midrange.com

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.

--__--__--

Message: 7
From: "Mackie, Roger L. (Precision Press)" <RLMackie@ppress-tc.com>
To: "'rpg400-l@midrange.com'" <rpg400-l@midrange.com>
Subject: RE: SQL Problem w/  UPDATE file   SET fieldname and   IN operator
        .
Date: Fri, 2 Aug 2002 10:50:59 -0500
Reply-To: rpg400-l@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.