About

Welcome to the “Peoplesoft and SQR” web log. My name is Steven Alexander. I worked on Peoplesoft HCM from November 1997 to October 2011 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 reflected a wider range of topics. In 2010, I continued to write about the SQR programming language. I also wrote about Peopletools and 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 changed employers in April 2010.  My new employer was a consulting firm where we earned our living by selling our intellectual property.  I stopped this blog in deference to my new role.

I started my own business in November 2011, outside the world of Information Technology.  My wife and I created and operate Pee Wee Bees, an indoor playground and birthday party place.  We provide a comfortable environment and an enormous toy collection to children between 6 months and 7 years old.  It is very exciting and enjoyable.

I am my own Information Technology department now.  I created our website, www.PeeWeeBees.com.  I created our customer database in the Microsoft SQL Server database that came with our Denali Accounting package. Since I don’t have access to a Peoplesoft environment, I cannot supply code samples. Also, the work I can share now pertains to SQL and Excel challenges.

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.

44 Comments

  1. Bala says:

    Steve,
    Nice blog, I love SQR and looking forward for more on SQR.

    Regards,
    Bala.

    • administrator says:

      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.

    • Daniel says:

      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

  2. Elias says:

    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

    • administrator says:

      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-select
      NAME
      ADDRESS
      CITY
      STATE
      ZIP
       from CUSTOMERS
      where ZIP like '345%'
      end-select

  3. Elias says:

    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

    • administrator says:

      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 = 0
      begin-select
      DUMMY
        let #is_like = 1
       from DUAL
      where $test_value like 'A%B'
      end-select
      if #is_like
      ...
      end-if

  4. Elias says:

    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.

  5. RN says:

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

    • administrator says:

      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.

  6. John Chemers says:

    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

    • administrator says:

      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?

  7. ANAND says:

    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

  8. Dhaneshwar says:

    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,

    • administrator says:

      Hi Dhaneshwar, thank you for writing. I really appreciate hearing from people who read my blog.

      Steven

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

    • administrator says:

      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.

  10. Mutyala Rao says:

    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.

  11. Charlie says:

    Hello Steve, Great blog!, I need a favor of you, recently I bought a License of SQR 11 (also I have the 11 media), but I really need is the version 9.3.1 for Windows, because a software is certified on this version, Where I can found it? I search on Oracle homepage and the download link for “Hyperion SQR Production Reporting – System 9 Release 9.3.1 Microsoft Windows x86 Installer” has been expired!.

    Please I need help! :S

    I’ll appreciate your collaboration,

    • administrator says:

      I was hoping that someone would step forward with a suggestion, because I don’t know anything about purchasing SQR, and I don’t know of any second-hand market.

      SQR is a mature product and you might try using 11 in place of 9.3.1. I there were only a few months between them in 2008 and there was no version 10. While version 11 has many new features, it might be compatible with version 9.3.1.

  12. Satya says:

    Hi Steve,

    I want to insert a CLOB(Character Large Object) datatype field into a PeopleSoft record through SQR. The data that is fetching from different tables in SQR is upto 50K characters, Since SQR varaible can carry only 32K characters, I tried concataneting all the three varaibles in the insert statement like this:

    INSERT INTO PS_XX_XXXX_RESUME(
    EMPLID,
    AI_EE_RESUME_TEXT)
    VALUES(
    $Emplid,
    $Data||$Data1||$Dat a2)

    But it is not allowing me to insert and giving error ORA-01461: can bind a LONG value only for insert into a LONG column. Please suggest is there any syntax while inserting CLOB datatype into the record or suggest me is there any way to handle this problem.

    Thanks
    Satya

    • administrator says:

      Hi Satya,

      SQR can’t handle CLOB or LONG fields very well. As you said, string variables are limited to 32K character strings (actually a bit shorter than that). It was a good idea to try concatenating strings, but SQR would have to hold the result in a bind variable before sending it to Oracle. You’ve discovered that SQR sends it as a LONG. Perhaps you can change the field to a LONG.

      Another problem with CLOB values is that they can contain single bytes that are equal to zero. Some commands and functions in SQR treat a zero byte as a string terminator.

      SQL isn’t much better at manipulating CLOB, but you might try an approach like this:

      INSERT INTO PS_XX_XXX_RESUME VALUES ($Emplid, $Data);

      UPDATE PS_XX_XXX_RESUME
      SET AI_EE_RESUME_TEXT = AI_EE_RESUME_TEXT || $Data1
      WHERE EMPLID = $Emplid;

      UPDATE PS_XX_XXX_RESUME
      SET AI_EE_RESUME_TEXT = AI_EE_RESUME_TEXT || $Data2
      WHERE EMPLID = &Emplid;

      Or insert the three $Datax variables into three fields. You may be able to combine the fields with a SQL command, or leave them separate.

      Let me know what works. Good luck!

      Steven

  13. Joe says:

    Hi Steven,

    I come to your blog recently by chance, and like info on here. I’m actually just start to learn SQR on Peoplesoft.
    I have created a simple SQR script file hello.sqr to do “Hello World” for example.
    I have add a new process named HELLO on peoplesoft.
    I know there is a subpage PRSCRUNCNTL_SBP I can use to initiate the process.
    But I can not get the process HELLO to appear in the “Process Scheduler Request” page.

    I know there are plenty SQR reference on internet. But I just can’t find anywhere show me how to link the process with the page. Would you please give me a help, or point me to the right direction?
    Much appreciated!

    Joe

  14. Joe says:

    please ignore my previous question, i have figured out.
    but in the end, i still can not test my SQR, as peoplesoft only search SQR file from certain directory, and I do not have access to them. Is there anyway I can test?
    Thanks!

    Joe

    • administrator says:

      I assume your second message means that you found the Process Definition page.

      You’re right, the Peoplesoft Process Scheduler has a list of directories in which to search for SQR files, and if you cannot put a file in those directories, you cannot get the Process Scheduler to run it. Do you have a good relationship with the file server administrator or the Peoplesoft administrator? Perhaps the former will move your file to the appropriate directory or the latter will add your directory to the list of directories that the Process Scheduler scans.

      If you have access to the SQRW.EXE file, you can execute SQR programs from a DOS or UNIX command line.

  15. Shankar says:

    Hi Steve,

    I am a new comer to your site as well as peoplesoft too. But i am eager in learning the peoplesoft and SQR. Is there is any single user or minimal user version to practice peoplesoft applications. Or anything like simulations.

    Please Help me on this.

    Shankar.S

    • administrator says:

      Sadly, the makers of SQR do very little to encourage people to use it. There are two versions of SQR, and neither is sold as a separate product. Nor is there a single user or self-contained version. There are third party products that execute SQR programs, but they won’t work without the official SQR product on your system.

  16. Joe Johnson says:

    Great blog!

    I am also looking in sqrug, but I have a basic question.

    For Oracle/PeopleSoft SQR, where does the Oracle logo reside and get called. I presume it is in a header sqc or two, but I’m having trouble locating the logo. My coa mpny wants to put their logo in it’s place.

    Thanks,

    Joe

    • administrator says:

      Thanks, Joe.

      I don’t understand your question. The Oracle logo appears on Peoplesoft pages, but it doesn’t appear on the output of an SQR program. It’s possible to take the logo off Peoplesoft pages, but I’ve never paid attention to the explanations I’ve seen about it. Sorry – you might try an Internet search.

  17. Nans says:

    hi freind, will u tell me the code ,written for ,the last visitor of admin page or any database….

    • administrator says:

      I’m not sure I understand your question. Are you referring to an administrative page of database management software? I don’t know about those, and the answer would be different for each database product.

  18. Ruth Wannek says:

    Hi there!

    I’ve been trying to add embedded fonts into the PDF output from an SQR process. Have you ever seen this done? I’ve modivied the PSsqr.ini to include Embed All or Embed=5 (which is the font I want to embed) but I can’t get it to work.

    Any thoughts?

    Thanks,
    Ruth

    • administrator says:

      Ruth, my customers have usually been so grateful to get useful information that they’ve never even asked about font changes. I tried adding boldface and color to their reports and they didn’t even notice. So I don’t have any experience with the advanced features you’re already using. I would have pointed you to pssqr.ini, but you’re already there. The DECLARE-PRINTER documentation talks about adding fonts to a POSTSCRI.STR file. If that’s too sketchy, you could join the discussion group on http://www.sqrug.org. (SQR User Group) Good luck.

  19. Deepu R says:

    Hi

    Very nice site. I m a beginner to SQR. Able to get so many useful information.
    Thanks

  20. Aditya says:

    Hi,
    I am a beginner to Peoplesoft & SQR, and have just started developing SQRs.
    I heard from my colleagues that there is no readily available reference for functions in SQR. It would be very tedious if I have to look into every .sqc file just to find a function that serves my purpose. I would like to have a compilation of SQR functions for reference, along with the corresponding .sqc file that needs to be included.
    Please let me know of any solution

    • administrator says:

      Welcome, Aditya. The term “function” usually refers to the functions built-in to the SQR language like edit(), datetostr(). The procedures (or subroutines) that Peoplesoft delivers are ordinary SQR like you would write for yourself. I don’t think Oracle documents them, and many of them are specific to a few SQRs. The ones I use in every program are in stdenv.sqc, setup01.sqc (or setup02.sqc), datetime.sqc, and stdapi.sqc.