× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

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

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

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.