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

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

--

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