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

The table I am needing to update resides on an MS-SQL server. It is named
FACILITY_PATIENT.

The file exported by Crystal Report Writer is a text file. No index, and
unknown to MS-SQL. I refer to it as a "flat file" as that is the database
language I was trained to use when referring to a non-keyed, non-database file.
It is just plain text. FACILITY_PATIENT is an SQL table which reside on an
MS-SQL server.

This is not relevant to System i, but I thought there was enough common with
System i SQL and MS-SQL that i could get these updates performed.

John McKee


Quoting Alan Shore <AlanShore@xxxxxxxx>:

John
I am not really too sure what you are asking for when you say
"how is this non SQL file identified?"
What I think you mean is , how is this file identified by SQL?
The simplest answer is - because its on the system.

I am assuming that the output file generated from Crystal Report writer is
FACILITY_PATIENT
and that you have interactive SQL
Therefore, enter the command
STRSQL
and on the SQL command line type
select * from FACILITY_PATIENT
and press Enter
This will display the contents of the file
F12 out of there and then press F9 to re-display the same command
select * from FACILITY_PATIENT
move your cursor to this same command and press F4 (for prompt)
on the next screen, move your cursor to the * on the line SELECT fields .
. . . . . . and press F4 (for prompt)
This will display the fields associated with that file

If you already know what I have written and you are reading this thinking
to yourself, What does this guy think I am? Well then my apologies


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 02:22:22 PM:

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 <AlanShore@xxxxxxxx>
>
>> 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
>> > >
>> > > --