Re: Another non Series i SQL question -- MIDRANGE-L

Just looked at the system tables. The application does absolutely nutty things.
A table is created with old and new inventory ids. But, it is then immediately
used to update PREFERENCE_DETAIL. Bad part is that the update process looks at
section and subsection numbers. If the supplied number do not match what is
used, then the update is skipped. Handy, if you only wanted to change
inventory when used in certain sections or subsections. Bummer if not.

I will just go back to building a text file of UPDATES.

Thanks for the ideas. Sorry for taking up your time on this issue.

John McKee

Quoting John McKee <jmmckee@xxxxxxxxxxxxxx>:

I was sent an email with an attached spreadsheet. The spreadsheet
contained the
old and the new inventory numbers. However, the table I need to update is
PREFERENCE_DETAIL, and the column is named RESOURCE_ID. The content of the
RESOURCE_ID column is an internal number which is stored in INVENTORY_DETAIL
under SYSTEM_ID. So, I am given the INVENTORY_ID. I look up the
corresponding
SYSTEM_ID in the INVENTORY_HEADER and then, finally, UPDATE the
PREFERENCE_DETAIL table, using the original SYSTEM_ID retrieved from
INVENTORY_HEADER, replacing the existing SYSTEM_ID with the new value.

If the table that is built by the application contains both internal id
numbers,
and they aren't padded with other information so that they are not easy to get
to, then the updates would be a table being updated with the content of
another
table. I need to look at that. I hope it is a simple matter, as I am already
quite convinced that doing this process manually is not desireable. I already
have 24 items to mess with. There can be as few as zero updates per item on
PREFERENCE_DETAIL, and no upper limit on the rows to be updated. It really
would have been nice if the application designer had not tossed in the
restrictions of section and subsection numbers - or provides a mechanism where
those values could be ignored.

I might have misstated this mess earlier. Rob pointed out that I had
not listed
the second select. Reason was that it is the SAME select, just looking for
different inventory ids. In just trying to type the select to get those
numbers to write down, it was tedious enough. On the plus side, by writing
them down, I discovered that they had given me two different items to be
changed to a single new item. Not good. So, maybe the best approach is to
just hold my nose, look up the respective system id values, and manually type
the UPDATEs, one item at a time.

John McKee

Quoting darren@xxxxxxxxx:

I thought, in your example, you had an INVENTORY_HEADER file and another
file that you were writing down information from. It appeared that you
were then updating based on information you selected from those two tables.
It sounds like updating a table from a table to me.

John McKee
<jmmckee@flinthil
ls.com> To
Sent by: midrange-l@xxxxxxxxxxxx
midrange-l-bounce cc
s@xxxxxxxxxxxx
Subject
Re: Another non Series i SQL
03/27/2008 02:00 question
PM

Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>

Unfortunately, I don't currently have the luxury of updating a file
from a file.
The application does create a change file, however, which >might< work in
this
instance. I hesitate because I dimly recall that some parts of that table
are
not simple fields - thus data would need to be broken out of larger
fields. This might actually work, provided the application does not put
additional
information in the field locations. I will have to take a look. But, if
the
application behaves in this instance, I would much prefer to use a table to
update a table, instead of either a list or one at a time.

Thanks.

John McKee

Quoting darren@xxxxxxxxx:

The concept of updating a file using data from another file is a common

SQL

function.

The syntax is something like this:

update lib/file a set fld=
(select fld2 from lib/file2 b where a.key=b.key)
where exists
(select fld2 from lib/file2 b where a.key=b.key)

Is that enough to adapt it to your application? The concept is that

you're

setting a value equal to a selection from another file, and then 'where
exists' makes sure that the selection value doesn't come back null, in

case

you don't have a one-one relationship of data records in the source file.

John McKee
<jmmckee@flinthil
ls.com> To
Sent by: Midrange Systems Technical
midrange-l-bounce Discussion
s@xxxxxxxxxxxx <midrange-l@xxxxxxxxxxxx>
cc

03/27/2008 11:23 Subject
AM Another non Series i SQL question

Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>

This is a tedious update. I am wondering if there is a way to use SQL to
make
the task less tedious and, especially, less error prone.

The application provides a method to do this table maintenance, but the
application is flawed, thus, I have been asked to do this to help the
Surgery
staff. I am already thinking that the process is likely not efficient

and

am
hoping for a safer method. But, my SQL skills are way toward the novice
side.

Here is the mess: I have been given a list of inventory ids that need to
be
replaced. Sounds simple enough, a simple UPDATE. But, the table I need

to

update does not contain the inventory id. Instead, it contains an
internally
generated system id. So, the process goes like this:

SELECT INVENTORY_ID, SYSTEM_ID FROM INVENTORY_HEADER
WHERE INVENTORY_ID IN 9'',''.'','',''.......)

Write down the SYSTEM_ID next to the INVENTORY_ID.

Repeat the above SELECT for the replacement inventory ids.

Then, finally, do this:

UPDATE PREFERENCE_DETAIL
SET RESOURCE_ID = // system id from second SELECT above
WHERE RESOURCE_TYPE = 'INVENTORY' and
RESOURCE_ID = // system id from first select above

Is there a way to combine the two SELECTs with the UPDATE in a way that
does not
hose the tables? Or, is just doing this one at a time the safest, most
efficient path?

BTW, the reason the application won't work for this is that there are
additional
columns that are tested before the change is allowed, specifically
SECTION_ID
and SUBSECTION_ID. It is very tedious to use the application when the
inventory items could be on multiple sections and/or subsections. Thus
SQL.

I am resigned to doing this via the two step method, as it is what I
understand.
On the other hand, if there is a way to do this in a single step that is

a

little less likely to mess things up, I would appreciate doing it that

way.

Apparently, this will not be a one time issue. They are planning on this
being
done in weekly batches, for however long. Problem is that our corporate
office
likes to change things, regularly. Each inventory id is associated with

a

specific vendor, thus the mess.

Comments are appreciated.

John McKee

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.