RE: Update a column in a table from a flat file

John

Where does this file live? On the iSeries or on the PC? You can't easily run SQL against text files - although there IS a text ODBC driver in Windows that you could use - you end up defining the row - just like I-specs, sort of.

Vern
-------------- Original message --------------
From: John McKee <jmmckee@xxxxxxxxxxxxxx>

My definitiion of a flat file is just a plain text file. No indexes,
not a PF. It is the output generated from Crystal Report Writer. How
would I specifity
this non database file? The nonkeyed file will contain two columns: the row
identifier in FACILITY_PATIENT, and the value to change a selected column to.


UPDATE FACILITY_PATIENT SET FMRN = xxx
WHERE MEDICAL_RECORD_NUMBER = yyy

The yyy value is the first column in the nonkeyed file. The xxx value is the
value to apply to the named column of the selected row.

But, how is this non SQL file identified?

John McKee

Quoting vhamberg@xxxxxxxxxxx:

Alan

You know all this, I'm just piggy-backing on your post.

If "flat file" means a one-field record, substr(field, 5, 10) works -
the contents starting at position 5 for 10 characters.

If a "non-keyed file", no different from any other physical file.

I really wish we'd stop talking about flat files anymore - that term
is pretty meaningless, seems to me, in this day and age. A PF is a PF
is a table is a table.

HTH
Vern

-------------- Original message --------------
From: Alan Shore

John,
Without sounding sarcastic, YOU tell the system how to update based upon
whatever match conditions apply.
When you said "flat" file, do you mean :-
a non-keyed file?
a file with one field in the record?


Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill
midrange-l-bounces@xxxxxxxxxxxx wrote on 02/13/2008 10:46:30 AM:

I am not at all proficient in SQL. I was not aware that SQL could update

a

table, based on a flat file. How would SQL know where the values where

in the

flat file?

John McKee

Quoting "John Arnold (MFS)" :

For the column where you want the new value use,

Case when mrc = yyy then xxx end as id

In your select statement that creates the flat file.


John Arnold
(301) 354-2939


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of John McKee
Sent: Tuesday, February 12, 2008 11:34 PM
To: Midrange Systems Technical Discussion
Subject: Update a column in a table from a flat file

My earlier question has been resolved. Can't skip a key field in a
database and get decent performance.

Now, I wondering:

Run the Crystal report. Export the rsssults to a flat file. First
field would be a complete primary key to the MS-SQL database. Third
field would be the new value for a single specified field.

One possibility would be to modify the text file to read like this:

update clinical set id= xxx where mrc=yyy

The above line would be modified so that a text file would have several
thousand individual update commands. The file would be input to a
command line program, name eludes me for the moment.

Is there a way to do all the updates with a single command?

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.