Search code examples
databasepeoplesoftsqr

Alter variable format using SQR programming language

I am new to Peoplesoft and Peoplecode/SQR in general and I want to ask a question.

What I wish to do, is to try and output a large (clob) string variable on a PDF file using SQR programming language, on 3 columns on the page, just as you can change the layout of a text in Microsoft Word by going to Layout -> Columns -> Three

What I have until now is this: 

 begin-select
   TXT.U_PO_TXT &TXT.PO_TXT
   from PS_U_PO_DISC_TXT TXT
   where TXT.BUSINESS_UNIT = &PO.BUSINESS_UNIT
   and TXT.PO_ID = &PO.PO_ID
 end-select

So, 

 TXT.U_PO_TXT

is a CLOB stored in the database of 10000 characters and I want to output 

 &TXT.PO_TXT

on the PDF using the layout described above.

I have tried using COLUMNS and NEXT-COLUMN SQR commands but to no avail. I find the documentation of SQR on the Oracle website poorly written.

This is the example I have found, applied to my case: 

columns 10 50 110
move 55 to #bottom_line
begin-select
 TXT.U_PO_TXT &TXT.PO_TXT
 if #current-line >= #bottom_line
 next-column goto-top=1 at-end=newpage
 else
 position (+1,1) !what is this even, it throws me an error "Unknown command"
 end-if
 from PS_U_PO_DISC_TXT TXT 
  where TXT.BUSINESS_UNIT = &PO.BUSINESS_UNIT
  and TXT.PO_ID = &PO.PO_ID
end-select

I couldn't find an answer anywhere. Please help me.

Solution

  • I don't think this is possible by using COLUMNS. Defining and printing to SQR columns determines where you start printing, but the text won't automatically wrap once it hits the end of the column. It'll just keep printing.

    You can, however, use the WRAP parameter to PRINT to accomplish this. Here's an example

    LET $left = 'This is the left column. This is the left column. This is the left column. This is the left column. This is the left column. This is the left column.'
LET $middle = 'This is the middle column. This is the middle column. This is the middle column. This is the middle column. This is the middle column. This is the middle column. This is the middle column. This is the middle column. This is the middle column. This is the middle column. This is the middle column. This is the middle column.'
LET $right = 'This is the right column. This is the right column. This is the right column. This is the right column. This is the right column. This is the right column.'

! These variables are used to track which column is the tallest and then 
! move the current position (#start-line) down by the largest number of
! lines that were printed
LET #line-offset = 0
LET #start-line = 1
LET #start-line-offset = #current-line - #start-line

! Print the left column
PRINT $left (#start-line, 1) WRAP 40 100
IF #current-line - #start-line-offset > #line-offset
    LET #line-offset = #current-line - #start-line-offset
END-IF

! Print the middle column
PRINT $middle (#start-line, 50) WRAP 50 100
IF #current-line - #start-line-offset > #line-offset
    LET #line-offset = #current-line - #start-line-offset
END-IF

! Print the right column
PRINT $right (#start-line, 110) WRAP 40 100
IF #current-line - #start-line-offset > #line-offset
    LET #line-offset = #current-line - #start-line-offset
END-IF

! Update #start-line to be the original line position, plus the 
! number of lines that were printed in the tallest column
LET #start-line = #start-line + #line-offset

PRINT 'Rest of the report goes here' (#start-line, 1)

    The first number after WRAP is the character width of that column.

    The second number after WRAP is the maximum number of lines that will be printed.

    Most of the complexity in my code comes from that fact that you'll have to track which column printed the most lines (is the largest vertically) and then move your current cursor position down that many lines. The difficulty comes from that fact that your tallest column may be either the left or center column, so you need to track which column is your tallest rather than simply moving the cursor downward after printing your right column. In my example, the middle column is the tallest.

    The reason I'm using #line-offset and #start-line-offset is because #current-line is the current line position in your report including the offset for the header. For example, if your header is 5 lines tall, then #current-line will start equal to 6 (the first line after your header) and doing PRINT 'hello' (#current-line, 1) will actually print to the 11th line of your report (6 lines down from the 5 line header). Also, assigning a value to #current-line doesn't seem to work, so I don't think your can manipulate the current line position directly.

    If, however, you don't need to print anything else after your three columns, then you can use the KEEP-TOP parameter to WRAP so that your current line position doesn't change between each PRINT and you don't have to do any of the line offset tracking.

    In your situation, you'll have to parse &TXT.PO_TXT into three separate variables (one for each column) based on how your text and column position data is stored in that field.

    One thing to watch out for is that if you have two or more spaces in a row within the data you're printing, SQR may put a space as the first character of a line because word breaks seem to only be determined by the first space, rather than a sequence of one or more spaces.