About
Welcome to the “Peoplesoft and SQR” web log. My name is Steven Alexander. I have worked on Peoplesoft HCM since November 1997 in various installations running with Oracle DBMS on UNIX file servers and Microsoft Windows client PCs.
I started this blog in January 2009 with the name “Peoplesoft SQR.” For the first year, it was about the SQR programming language and how best to use it. I wrote about techniques and algorithms, commands, and how to thrive within the Peopletools environment.
Adding a conjunction to the blog title reflects a wider range of topics. This year (2010), I will continue to write about the SQR programming language. I will write about Peopletools as well. Also, I will write about using SQL (that’s an L, not an R) with the data in the Peoplesoft HCM modules (Human Resources, Base Benefits, Payroll, and Time & Labor).
I hope you, dear reader, will write for this blog as well. It’s not the best place to bring specific problems (e.g. how do I get my Application Engine program to send an e-mail with an encrypted attachment to Lotus Notes?). There are several other websites with worldwide legions of Peoplesoft developers who can respond to those questions around the clock. It is a good place to bring ideas that enhance a programmer’s productivity or a user’s Peoplesoft experience (e.g. here’s a SQL statement for finding the Portal path to the run control component for a given process), or to improve on my ideas in the weekly posts.
Steve,
Nice blog, I love SQR and looking forward for more on SQR.
Regards,
Bala.
Thanks, Bala. Peoplesoft SQR programmers should visit Bala’s blog at psadmin.wordpress.com. It covers Peoplesoft Administration and all the tools we use; SQR, Peopletools, Peoplecode, Integration Broker, etc.
Hi,
Thanks for all about sqr.
I am a new comer to the sqr environment; I would like to communicate with you in order to grow in sqr language using with your help.
Please I am waiting for your reply.
Best regards
Welcome Daniel. There is a discussion group dedicated to SQR at http://www.sqrug.org. Also, you can write to me privately at steven@peoplesoftsqr.com.
I am new to Sqr and have a question.
what does SQR use for wildcards?
what would be the SQR syntax equivalent to “like abc%” in SQL?
Thanks
Welcome to my blog, Elias. SQR supports wildcards in its embedded SQL (begin-select and begin-sql). The syntax is exactly the same as SQL itself. Here’s an example:
begin-selectNAME
ADDRESS
CITY
STATE
ZIP
from CUSTOMERS
where ZIP like '345%'
end-select
Thanks for the info.
I was trying to embed it in a print procedure in an if…end-if statement where it doesn’t work I assume. any ideas?
begin-procedure print_drug#debug show 'PRINT_DRUG PROCEDURE'
print &frequency ( , {FREQUENCY})
if (&class = 'RESP') or ( &misc_string2 like '08%')
print 'Requires order' ( , {RENEW})
else
print 'DC / RENEW' ( , {RENEW})
end-if
end-procedure !print_drug
The wildcard feature only works as part of a SQL where clause. In an if statement like this:
if (&class = 'RESP') or ( &misc_string2 like '08%')Try this:
if (&class = 'RESP') or (substr(&misc_string2, 1, 2) = '08')03/17/09 Update:
I just had another idea, although it still depends on SQL:
let #is_like = 0begin-select
DUMMY
let #is_like = 1
from DUAL
where $test_value like 'A%B'
end-select
if #is_like
...
end-if
I see this is for comments.
In the future I will communicate with you directly or with the discussion group.
Thanks for the help, Great Site.
Would you please provide some suggestions into how to eliminate duplicate rows from a flat file before loading them into your staging tables? I do not want to eliminate the keys on my work/staging table..
Congratulations! You wrote the 100th comment on this blog. I’m on vacation this week, but when I return, I’ll try to think of something. Can you provide more details about your task?
05/21/09 Update:
The way to eliminate duplicates is to read the file row by row, store it in an array, and write each row to a new file except for the duplicates. Now the question is, what is the easiest way to determine whether a row is a duplicate?
It will be easier if you have a program that will sort the file before you read it. Then you don’t have to compare each row to every other row, just to the previous row. Look out for cases of more than two identical rows.
UNIX comes with at least one sort utility, although I don’t know what it’s called or how to use it. Try searching the Internet for “unix sort.” If you are working in Windows or some other operating system, search the Internet for software for your environment. Or use the SQR Quicksort that I described in an earlier post.
Alternately, you can take advantage of unique violation errors in SQL. Create a unique key field in your scratch table that consists of the entire row from your file. Add an on-error clause to your begin-sql command so the unique violation won’t end your program. The database itself will (slowly) weed out all your duplicate rows.
I am trying to print an image (bmp) from SQR runnning from PeopleSoft8.8/Web. It works from SQRW.exe icon but not from within PeopleSoft. What gives?
!—————————————————————————————!
begin-setup
!—————————————————————————————!
Declare-Printer HP
type = HP
symbol-set = 0N
font = 4
point-size = 8
End-Declare
Declare-Layout default
orientation = Portrait
paper-size = ( letter )
left-margin = 0.5
top-margin = 0.5
!right-margin = 0.5
max-lines = 336 !***** was 180 !**** 330 / 6 == 55 lines per page
max-columns = 93
line-height = 2.25 !*****was 4.5
!***char-width = 4.2
End-Declare
Declare-Report default_report
Layout = default
Printer-Type = HP
End-Declare
end-setup
let $LOGO = ‘j:\sqr\test.bmp’
if $LOGO ”
print-image (6,1)
type=bmp-file
source=$LOGO
image-size=(23,8)
end-if
I’m happy to give this problem a try, but you should consider joining the discussion group at http://www.sqrug.org. That would give you access to many experienced SQR programmers, and the answers would come through your e-mail. As to your problem, what operating system does your file server use (Unix, Windows, or something else)? Does your file server have access to the “J” drive? Does it use the letter “J” to refer to that drive?
Hello Steve,
I am a peoplesoft developer and was going through your Blog.
I found your blog very useful.
Can you share the skills that you have with the artcles with a site that we have developed for PeopleSoftCommunity.
Please reply me soon.
Thanks in Advance.
Regards,
Anand
Hi Steve,,
Nice to see your website regarding SQR. I have been working on SQR since last one year. But very much anxious to know its hidden features discovered by you.
Regards,
Hi Dhaneshwar, thank you for writing. I really appreciate hearing from people who read my blog.
Steven
In response to your SQR to Excel article, have you considered using SQR2XML? It is a sourceforge project (free and open source). It allows a developer to call procedures from an SQC library, and the output is rich Excel output, complete with colors, fonts, multiple tabs, and formulas.
Project: http://www.sourceforge.net/projects/sqr2xml
I have considered it but so far my customers haven’t needed the power that SQR2XML seems to offer. Thank you for the link; people should take a look.
Thanks Steve. Good portal about SQR. You know of any open-source tools that can create a flow chart for a given SQR?
Cheers,
Rao.