|
November 22, 2006
Frequent SQR Questions
While gathering questions and answers for sqr-info.com
(especially the specific SQR topics page),
I see many repetitions of the same old questions. Here, in no
particular order, are some of the most frequently asked.
How do I generate email from SQR? This seems to be a common
question for good reasons—PeopleSoft runs in both Windows
and Unix/Linux operating systems, sometimes attachments are required,
we must deal with different server configurations, etc. Generally
the solutions involve using mailx, blat.exe or psemail.exe, depending
on circumstances. I've dealt with this requirement in the past and have also
run into timing issues and other fun stuff. The specific topics page contains links
to some sample code.
How do I use CALL SYSTEM? Why do I get this return code? Why isn't
my command being executed? CALL SYSTEM executes an operating system
command or other program. It is useful but tricky. Following these
three steps will eliminate most of the problems. First, try your command outside of SQR first, directly within the
operating system's command shell! Many people do not do this and then
assume it's an SQR problem. Second, package your command(s) within
a shell script or batch file. This generally works better than trying
to execute the commands directly, and it's a more flexible approach.
Third, if using Windows, execute your batch file under a new
command processor by using COMSPEC and /c (see the listings for sample code).
How do I read/write .CSV and .XLS files? Why are leading zeros
being dropped? What about formatting in Excel? Commas? Quotes?
I did a fairly detailed summary of how to create these files
here
and here.
Leading zeros are dropped by Excel itself. This can be avoided by telling
Excel that a column is "text" and then reloading the file, although
there are other methods. Formatting can be done using two more involved
methods known as the XML and SYLK methods (see the second post above
for links).
Reading and parsing .CSV files is easy if you have very simple and predictable data
with no strings containing embedded commas and quotes—just
use UNSTRING. In other situations, write you own parser or search for the various
sample implementations that have been posted.
In general, I like to use tab-delimited input files
to avoid many of the parsing issues. It's easy to UNSTRING those and then
LTRIM and RTRIM any surrounding quotes.
What's that junk at the top of my .LIS file? It's just printer control characters
that tell an HP printer how to format the page. If you see these characters
on paper, try copying the file directly to the
printer instead of printing the file from a text editor. If you
want to copy the report file to a new text file (without printer control characters)
at the end of your program, be
sure to close the report output file by using NEW-REPORT before trying to
OPEN it. That's the source of many other frequently asked questions.
How do I get rid of "special" or "bad" characters? For example, how can I remove
hyphens from SSNs or phone numbers? How do I insert a single quote or exclamation
point into a string? Many people want to replace foreign/accented
characters (é, ç, ñ, etc.) with "English" equivalents. This is easily done
in one step using the TRANSLATE function. Since TRANSLATE can also
change specified characters to "nothing" (empty strings), it can be
used to remove characters embedded in a string. Unfortunately, some of
the answers to these questions on forums contain convoluted
solutions involving loops, substrings and concatenation.
As for including a quote (') or exclamation point (!) in a string, it's
very simple. Just use two quotes (or exclamation points) to stand for
one. Once again, the forums usually attract overly complicated ENCODE
and concatenation solutions.
let $Answer = 'Don''t make it more difficult than it needs to be!!'
Until next time...
|
