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. Vaibhav says:

    Hello I am new to SQR : wanted to know if we can add up new fields to be written as output in the bank file
    ? The SQR is somewhat as follows :
    !***********************************************************************
    !print the output in report
    !PRINT-HEADER
    !***********************************************************************
    BEGIN-PROCEDURE PRINT-HEADER
    SHOW ‘ENTER PRINT-HEADER’

    LET #NO_OF_REC = ”
    LET #TOTAL_AMOUNT = ”

    !Move #record_cnt TO $record_cnt 99999
    !Move #grand_total TO $grand_total 9999999.99

    if #length = 3.000000
    show ‘if_$total_amt1: ‘$total_amt
    !Let $total_amt = ltrim($total_amt,’0′) ! modify by retina on 23-March-09 to remove leading zero
    show ‘if_$total_amt2: ‘$total_amt
    LET $total_amt = EDIT($total_amt,’9999999999.9′)
    !Let $total_amt = rpad($total_amt,10,’0′) ! modify by retina on 23-March-09 to add zero at behind
    show ‘if_$total_amt3: ‘ $total_amt
    let $total_amt = replace($total_amt, ‘.’, ”)
    show ‘if_$total_amt4: ‘$total_amt
    else
    if #length = 4.000000
    let $total_amt = $total_amt
    show ‘else_if_$total_amt1: ‘$total_amt
    else
    show ‘else_$total_amt1: ‘$total_amt
    !Let $total_amt = ltrim($total_amt,’0′) ! modify by retina on 23-March-09 to remove leading zero
    show ‘else_$total_amt2: ‘$total_amt
    LET $total_amt = EDIT($total_amt,’9999999999.00′)
    !Let $total_amt = rpad($total_amt,10,’0′) ! modify by retina on 23-March-09 to add zero at behind
    show ‘else_$total_amt3: ‘ $total_amt
    let $total_amt = replace($total_amt, ‘.’, ”)
    show ‘else_$total_amt4: ‘$total_amt
    end-if
    end-if

    WRITE #file FROM
    ‘F’:1
    ‘110251352001′:12
    ‘N01′:3 !Modified by shivaram for January 2010 Bonus run.
    !’N02′:3 Modified by shivaram for January 2010 Regular run.
    $pymt_date:12
    $pay_date:6
    ‘K’:1
    !’********’:8
    ‘********’:8 ! Low Tze Xiang 08 June 2009
    $record_cnt:5
    !$total_amt:13
    $total_amt:10 ! Low Tze Xiang 08 June 2009
    ‘ ‘:7
    ‘ ‘:12
    ‘ ‘:2
    ‘1′:1

    SHOW ‘EXIT PRINT-HEADER’
    END-PROCEDURE PRINT-HEADER

    !***********************************************************************
    !print the output in report
    !
    BEGIN-PROCEDURE WRITE-OUTPUT-RECORD
    !***********************************************************************

    !LET #NETPAY = #NETPAY * 100
    !Move #NETPAY To $DisplayAmount 99999999

    SHOW ‘EMPLID’ &EMPLID
    SHOW ‘$BANKID’ $BANKID
    SHOW ‘$BANK_BRANCH’ $BANK_BRANCH
    SHOW ‘$ACCOUNT_NO’ $ACCOUNT_NO
    !WRITE #file FROM
    ! ‘ ‘:1
    ! &EMPLID:12
    ! $ACCOUNT_NAME:20
    ! $BANKID:3
    ! $BANK_BRANCH:3
    ! $ACCOUNT_NO:9
    ! $netpay:10
    ! ‘ ‘:4
    ! ‘ ‘:6
    ! $pymt_date:12

    let $EMPLID = Rpad(&EMPLID,12,’ ‘)
    let $ACCOUNT_NAME_1 = Rpad($ACCOUNT_NAME,20,’ ‘)
    let $BANKID_1 = Rpad($BANKID,3,’ ‘)
    let $BANK_BRANCH_1 = Rpad($BANK_BRANCH,3,’ ‘)
    let $ACCOUNT_NO_1 = Rpad($ACCOUNT_NO,9,’ ‘)
    let $netpay_1 = Rpad($netpay,10,’ ‘)
    !let $pymt_date_1 = Rpad($pymt_date,12,’ ‘)
    !let $pymt_date_1 = Rpad(’ ‘,12,’ ‘) ! Low Tze Xiang 08 June 2009
    let $pymt_date_1 = Rpad($pymt_date,12,’ ‘) ! Low Tze Xiang 10 June 2009
    ! $ACCOUNT_NAME:20
    ! $BANKID:3
    ! $BANK_BRANCH:3
    ! $ACCOUNT_NO:9
    ! $netpay:10
    ! ‘ ‘:4
    ! ‘ ‘:6
    ! $pymt_date:12

    Let $String = ‘ ‘ || $EMPLID || $ACCOUNT_NAME_1 || $BANKID_1 ||$BANK_BRANCH_1 || $ACCOUNT_NO_1 || $netpay_1 || ‘ ‘ || ‘ ‘|| $pymt_date_1
    show $String

    Write #file FROM
    $String

    ! Add 1 to #Tot_Recs

    END-PROCEDURE WRITE-OUTPUT-RECORD

    • administrator says:

      I’m sorry I didn’t answer this. I didn’t understand your question and the program looked too long for a quick response. I approved your comment and meant to get back to it – then I forgot.

      It looks like this program was using the write command to output multiple character values on a single line, then it was changed to concatenate those values into a single string and write it on a single line. Either way can work, with a few caveats.

      1. You haven’t shown the command that opens the file. It defines the maximum length of each line, and the write statements cannot exceed that length.
      2. Writing individual values will truncate or pad those strings to make them the length you specify. If you concatenate the variables into a single string you don’t have that length control (unless you do it yourself with a rpad() and substr() function for each variable.
      3. If you want to add fields to line, go ahead. SQR only checks on the total length of the line.

      Good luck!

  2. guest says:

    hi,

    how to hardcode year in sqr program. but only year.

  3. guest says:

    hi,

    how to write code in sqr .(year)

    • administrator says:

      You haven’t explained your question very thoroughly, so I will answer what I think you’ve asked. If I’ve missed your point, please ask again with more information.

      Suppose you want today’s date, but in 2010. If the program runs on October 10, 2011, you want 10/10/10. If it runs on February 1, 2012, you want 2/1/10. Here is one way to do it:

      let $wanted_date = strtodate(edit($current-date, ‘mm/dd’) || ‘/2010′, ‘mm/dd/yyyy’)

      We use the built-in variable $current-date to get the date the program is running. We use the edit function to extract the month and day. We concatenate the hardcoded year. We convert the string to a date variable with the strtodate function.

      Good luck!

  4. Geoffrey says:

    Hi there,

    Came upon your website and perhaps you can offer some assitance. When we upgraded a while back from PeopleSoft v8.8 to v9, we also upgraded SQL Server to 2008. Part of this was to update selected SQR programs that utilize outer joins. Most of them I could deal with however it seems that SQR programs fail when they have bind variable(s) in the outer join portion. The identical statement runs just fine in our Query Analyzer tool when I replace the bind variables:

    begin-SELECT
    CT.WRKDAY_ID
    CT.DUR
    P.TRC
    P.TL_QUANTITY
    FROM PS_SCH_CLND_VW CT LEFT OUTER JOIN PS_TL_RPTD_TIME AS P ON P.DUR = CT.DUR AND P.EMPLID = &_J.EMPLID AND P.EMPL_RCD = &_J.EMPL_RCD AND P.TRC = $Sck_Cd
    WHERE CT.SCHEDULE_ID = &WT.SCHEDULE_ID
    AND CT.DUR BETWEEN $Q_Begin AND $Q_End
    ORDER BY CT.DUR DESC
    end-SELECT

    This is what I get in the log file:
    (SQR 1303) Error in SQL (perhaps missing &name after expression):
    SELECT CT.WRKDAY_ID, CT.DUR, P.TRC, P.TL_QUANTITY FROM PS_SCH_CLND_VW CT LEFT OUTER JOIN PS_TL_RPTD_TIME AS P ON P.DUR = CT.DUR AND P.EMPLID = ? AND P.EMPL_RCD = ? AND P.TRC = ? WHERE CT.SCHEDULE_ID = ? AND CT.DUR BETWEEN ? AND ? ORDER BY CT.DUR DESC
    (SQR 1304) Check SELECT columns, expressions and ‘where’ clause for syntax.

    SQR for PeopleSoft: Program Aborting.

    This is executed within a local procedure. That said, the old way of doing the outer join used the variables and *= . This worked fine.

    In Query Analyzer with the bind variables replaced:
    SELECT
    CT.WRKDAY_ID,
    CT.DUR,
    P.TRC,
    P.TL_QUANTITY
    FROM PS_SCH_CLND_VW CT LEFT OUTER JOIN PS_TL_RPTD_TIME AS P ON P.DUR = CT.DUR AND P.EMPLID = ‘0717′ AND P.EMPL_RCD = 0 AND P.TRC = ‘PS1′
    WHERE CT.SCHEDULE_ID = ‘STNDFIREB’
    AND CT.DUR BETWEEN ‘2012-02-06′ AND ‘2012-02-20′
    ORDER BY CT.DUR DESC

    Yields:

    OFF 2012-02-20 00:00:00.000 NULL NULL
    OFF 2012-02-19 00:00:00.000 NULL NULL
    OFF 2012-02-18 00:00:00.000 NULL NULL
    OFF 2012-02-17 00:00:00.000 NULL NULL
    WRKDAY14HR 2012-02-16 00:00:00.000 PS1 14.000000
    WRKDAY14HR 2012-02-15 00:00:00.000 PS1 14.000000
    WRKDAY10HR 2012-02-14 00:00:00.000 PS1 10.000000
    WRKDAY10HR 2012-02-13 00:00:00.000 PS1 10.000000
    OFF 2012-02-12 00:00:00.000 NULL NULL
    OFF 2012-02-11 00:00:00.000 NULL NULL
    OFF 2012-02-10 00:00:00.000 NULL NULL
    OFF 2012-02-09 00:00:00.000 NULL NULL
    WRKDAY14HR 2012-02-08 00:00:00.000 PS1 14.000000
    WRKDAY14HR 2012-02-07 00:00:00.000 PS1 14.000000
    WRKDAY10HR 2012-02-06 00:00:00.000 NULL NULL

    Thx,
    Geoffrey

    • Nirav Nandu says:

      Need help with the same issue for my requirements. If you have resolved it.

      The issue is with using the datetype variables in the SQR statements for MS SQL Server 2008