• Subject: Re: Need SQL Help
  • From: Rob Berendt <rob@xxxxxxxxx>
  • Date: Mon, 29 Nov 1999 9:24:45 -0500

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


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

This mailing list archive is Copyright 1997-2019 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 here. If you have questions about this, please contact [javascript protected email address].