[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
RE: [sqr-users] Getting Specific Value from Flat File
Okay....
I found a way to grab what I need from this massive file. Check out the code
below....
*************************************************************************
Begin-Procedure HappyHalloween
Let #Loc = 0
Let #Flag = 0
While #Flag = 0
Find '\' IN $Sqr-Report #Loc #Newloc
If #Newloc = -1
Let #Flag = 1
Else
Let #Loc = #Newloc + 1
End-If
End-While
Let $Orig = Substr($Sqr-Report,1,#loc)||'test.txt'
Let $Out = Substr($Sqr-Report,1,#loc)||'test_'||datenow()||'.txt'
Open $Orig AS 1 for-reading record=32767:vary Status=#Global-Proc-ErrNbr
Open $Out As 2 for-writing record=32767:vary Status=#Global-Proc-ErrNbr
If #Global-Proc-ErrNbr != 0
Let $Global-Proc-ErrMsg = 'Could not open file '|| $Orig
Do Global-Got-Fatal-Data-Error
End-If
While 1
Read 1 into $line:32767
If #end-file
Break
End-If
If #Global-Proc-ErrNbr != 0
Let $Global-Proc-ErrMsg = 'Could not open file '|| $Out
Do Global-Got-Fatal-Data-Error
End-If
If substr($line, 1, 22) = 'Original Value: 1'
Let $Original_Value = substr($line, 22, 8)
Begin-Select
a.value &output_value
FROM TABLE 1 b, TABLE 2 a
WHERE a.ID = $Original_Value
AND a.ID = b.ID
End-Select
Show 'Original Value: '$Original_Value !-- testing only
Show ' Output Value: '&output_value !-- testing only
(what I want to see underneath the
"Original Value:" row.
End-If
Write 2 from $line
End-While
Close 1 ! Close file for reading
Close 2 ! Close file for writing
End-Procedure
*************************************************************************
This code is ghetto as hell, but based on what I see in my output window -
it's exactly what I need.
What I need help on is the following...
(1) How do I place the " Output Value: '&claim_number" text underneath
the "Original Value: '$Original_Value" row?
(2) Is there a way around the max (32767) for reading/writing? This file is
definately over the limit (not by too much, but still over).
Thank you in advance.
- Darrel
"Overcashier, Patricia" <Patricia.Overcashier@aam.com> wrote:What is in column
22 on the other lines that you don't want. Is there a way
when reading the file to check for something specific that would identify
that you are at a row where you need to get the data from column 22, run a
query, write a line, and then get the next row?
Another words, is there anything at all specifically identifying these rows?
-----Original Message-----
From: Darrel Scott [mailto:darrel_1977@yahoo.com]
Sent: Friday, October 31, 2003 12:20 PM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] Getting Specific Value from Flat File
Thanks for the tip.....but after finally getting an proper electronic sample
data file, it looks like the original data file is over 87,000 lines long!
The value I need is unique and is found every 70 lines (starting from line
14, col 22, approx. 8 chars long). This discoverery shines a whole different
light on my task ahead. I still need to insert the result from mt SQL query
on line 15 (which is completely blank).
I'm extremely new to SQR (I've only been coding for a week). I just got the
SQR In Peoplesoft book (which has helped thus far), but any help I can get
from this forum would be greatly appreciated.
Thanks,
Darrel
"Knapp, Richard" wrote:
Read the first 14 lines in and save them to an array.
Extract your query value and execute the query.
If you get the result you are looking for, write a temp file with the 1st 14
lines and write your 15th line. In a loop, read the rest of the original
file and write the temp file from those lines one line at a time.
Delete or rename the original file. Rename the temp file to the name of the
original.
Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu
-----Original Message-----
From: Darrel Scott [mailto:darrel_1977@yahoo.com]
Sent: Friday, October 31, 2003 9:03 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] Getting Specific Value from Flat File
Hello Fellow Programmers...
I'm hoping someone out there can assist me.
I'm trying to do the following:
(1) Get a value from a flat file (always found on line 14, col 22, approx. 8
chars long)
(2) Take that value and plug it into a SQL query.
(3) The value returned from that SQL query must then be written back to the
flat file placed directly underneath the original value (on line 15, col 22,
approx. 15 chars long). Along with a text label ("Output Value:") placed on
line 15, col. 1, approx. 13 chars long.
If no rows are returned from the SQL query, nothing is should happen (or an
'N/A' should be placed instead of the NULL VALUE).
Here's what I've written so far...
***********************
Begin-Procedure Identify
Let #Loc = 0
Let #Flag = 0
While #Flag = 0
Find '\' IN $Sqr-Report #Loc #Newloc
If #Newloc = -1
Let #Flag = 1
Else
Let #Loc = #Newloc + 1
End-If
End-While
Let $FhlbOrig = Substr($Sqr-Report,1,#loc)||'test.lis'
Let $FhlbIn = Substr($Sqr-Report,1,#loc)||'test_'||datenow()||'.lis'
Let $FhlbOut = Substr($Sqr-Report,1,#loc)||'test.lis'
Let #Status = exists($FhlbIn)
If #Status = 0
Show 'File ' $FhlbIn ' exists. Will be deleted. '
Let #Status = delete($FhlbIn)
End-If
Let #Status=rename($FhlbOrig,$FhlbIn)
If #Status = 0
#DEBUG 'File ' $FhlbOrig ' successfully renamed to ' $FhlbIn
Else
Show 'Error renaming ' $FhlbOrig ' to ' $FhlbIn ', Status= ' #Status
Stop
End-If
Open $FhlbIn AS 1 for-reading record=132:vary Status=#Global-Proc-ErrNbr
Open $FhlbOut As 2 for-writing record=132:vary Status=#Global-Proc-ErrNbr
If #Global-Proc-ErrNbr != 0
Let $Global-Proc-ErrMsg = 'Could not open file '|| $FhlbIn
Do Global-Got-Fatal-Data-Error
End-If
While 1
Read 1 into $Input_Record:132
If #end-file
Break
End-If
If #Global-Proc-ErrNbr != 0
Let $Global-Proc-ErrMsg = 'Could not open file '|| $FhlbOut
Do Global-Got-Fatal-Data-Error
End-If
Let $ID_Value = Position(14,22,8)
Show $ID_Value !-- test
Begin-Select
a.value &number (15,22,15)
Move &number To #number
Show 'Number = ' #number !-- testing only
FROM TABLE 1 b, TABLE 2 a
WHERE a.nbr = ID_Value
AND a.nbr = b.nbr
End-Select
If #sql-count = 0
Print 'Output Value:'(15,1,13)
Print 'N/A'(15,22,3)
Else
Print 'Output Value:'(15,1,13)
End-If
Write 2 from $Input_Record
End-While
Close 1 ! Close file for reading
Close 2 ! Close file for writing
End-Procedure
***********************
It compiles okay, but when executed the original file is "zeroed" out. The
57K file is reduced to a 0K file.
Help?
Thanks, Darrel
---------------------------------
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
---------------------------------
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**********************************************************************
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
---------------------------------
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users