Re: Need SQL Help -- MIDRANGE-L

Should have said we are on V4R3.  Tried your statement tonight and it
updated more than on  row before I got;
Result of SELECT INTO or subquery more than one row.
I think it might be because an item might be in several locations, but there
is only one record  for each item in MAPITEM.  I did not have any more
trouble with a null not being allowed.  It's kind of moot now though, as I
did a quick and dirty RPG to do the update, as it had to be done today.
I learned from your example and want to thank you for your assistance.
Thanks, Mary Jo

-----Original Message-----
From: Rob Berendt <rob@dekko.com>
To: MIDRANGE-L@midrange.com <MIDRANGE-L@midrange.com>
Date: Monday, November 29, 1999 6:43 AM
Subject: Re: Need SQL Help


>First of all, to do the requested update from one file into another you
need to be
>running V4R3 or later.  Are you?
>
>The problem is that there are records in your MAPITEM file that have no
matching
>records in the MAPILOC file.  Use the following:
>update usrmjw/mapiloc set (ilpchg) = (select itchrg from
>     usrmjw/mapitem where mapitem.ititem = mapiloc.ilitem)
>  where ilitem in (select ititem from mapitem)
>
>You might also want to consider
>Select ititem from mapitem where ititem not in(select ilitem from mapiloc)
>to determine which MAPITEM items have no MAPILOC records.
>
>
>
>
>
>
>
>mjwhit@cyberportal.net on 11/28/99 12:02:45 PM
>Please respond to MIDRANGE-L@midrange.com@Internet
>To: MIDRANGE-L@midrange.com@Internet
>cc:
>Fax to:
>Subject: Need SQL Help
>
>I am trying to learn SQL.  I am trying to update a field in a file with
>information from a field in another file for our Inventory Managment
module.
>File MAPITEM has a field ITITEM with the item number and a field ITCHRG
with
>the patient charge number in it.  It has only one record for each item.
>File MAPILOC has a field ILITEM for the item number and ILPCHG that is
>supposed to have the patient charge number in it.  It has a record for each
>item and for each location that the item is in.
>The charge number is missing in many of the MAPILOC records and this is
>causing patients to not be charged.  I thought this would be an excellent
>time to use SQL, however I keep getting a message that I am trying to
update
>more than one row.  It's right, I am trying to update any row that has a
>missing charge number.
>
>  update usrmjw/mapiloc set (ilpchg) = (select itchrg from
> usrmjw/mapitem where mapitem.ititem = mapiloc.ilitem and
> mapiloc.ilpchg is not null)
>
>I've looked SQL By Example and the SQL Reference manual and am getting
>nowhere.  I've tried ANY and ALL and get invalid token.  The not null is in
>there because I got an error that ILITEM cannot be null without it.  The
>field shoud be blank, not null, but it bombs when I put in mapiloc.ilpchg =
>' '
>
>Is it possible to update any and all instances of ilpchg with the charge
>number from mapitem with SQL? Any help is gratefully appreciated.
>Mary Jo
>
>
>+---
>| This is the Midrange System Mailing List!
>| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
>| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
>| To unsubscribe from this list send email to
MIDRANGE-L-UNSUB@midrange.com.
>| Questions should be directed to the list owner/operator:
david@midrange.com
>+---
>
>
>+---
>| This is the Midrange System Mailing List!
>| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
>| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
>| To unsubscribe from this list send email to
MIDRANGE-L-UNSUB@midrange.com.
>| Questions should be directed to the list owner/operator:
david@midrange.com
>+---
>

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---