Are dynamic variables possible in SQR (not dynamic SQL)

Question

I'm writing an SQR program to send a vendor a file containing employee info. The file contains a number of fields for which I've assigned the variables $Code_1 $Code_2 $Code_3 ....

Each code has an associated rate, and I've assigned similar variables ($Rate_1, $Rate_2, etc...)

I have a lookup table that has the columns EMPLID, JOBCODE, HOURLY_RT. I need to loop through for each employee to get all of the codes/rates. It's possible that some employees will have more/fewer than others. Is it possible to have "dynamic" variables, like we do for dynamic sql? For example, something like $Code_[$i]? The thought was to do something like this:

let #i = 1

begin-select
EC.JOBCODE
EC.HOURLY_RT

   let $Code_[$i] = &EC.JOBCODE
   let $Rate_[$i] = &EC.HOURLY_RT

   let #i = #i + 1
FROM PS_ACME_LOOKUP EC
WHERE EC.EMPLID = &J.EMPLID
end-select

This doesn't work, but I wondering if there's a similar (or better) way to accomplish this. I suppose I could do an evaluate of the counter: when #i = 1, $Code_1 = ... when #i=2, $Code_2 =... But I'm hoping there's a better way.

Thanks

Edit - Just for added clarification, for each employee, a single line will be written to a file, with the fields for each of these values (populated or not) - so the line will have: $EMPLID $Code_1 $Code_2 $Code_3.....$Rate_1 $Rate_2 $Rate_3

For further clarification the lookup table will have multiple rows for each employee, so the table might look like this:

EMPLID    JOBCODE    HOURLY_RT
0001      ABC        10.50
0001      DEF        9.75
0001      GHI        9.50

When I populate the variables, looping through the table, I would want $Code_1 = 'ABC', $Rate_1 = 10.50, $Code_2 = 'DEF', Rate_2 = 9.75 etc...

Do the positions of the codes mean anything? For example, if EMPLID 0001 doesn't have a JOBCODE of DEF, should the file still reserve that space, or would you just fill it with GHI? Depending on your answer, I think you can do this using SQL in one of two ways. — Bob, Oct 11 '16 at 02:56

cardmagik · Accepted Answer · 2016-10-10T21:56:03.440

1

You can use arrays in SQR.

To set up the array:

Create-Array Name=WorkArray Size = 100
Field=Code
Field=Rate

Let #NumCodesForEmp = 0

To add data in your Select Block - also use on-break before and after procedures:

Begin-Select
EC.Emplid () on-break print=never before=Init-Emp After=Process-Emp
       Let $Emplid = &EC.Emplid
       add 1 to #NumCodesForEmp
       Put &EC.JobCode &EC.Rate into WorkArray(#NumCodesForEmp) Code Rate

Write the before procedure to initialize:

Begin-Procedure Init-Emp
   Let #NumCodesForEmp = 0
End-Procedure

When done with the employee:

Begin-Procedure Process-Emp
    Let #I = 1
    Let $OutputLine = $Emplid
    While #I <= #NumCodesForEmp 
       Get $Code $Rate From WorkArray(#I) Code Rate
       Let $OutputLine = $Outputline || ',' || $Code || ',' || $Rate
       add 1 to #I
    End-While
    ! This assumes that file number 10 is open
    Write #10 from $OutputLine
End-Procedure

However, I think you could do everything without an array - use the before and after procedures as so:

Begin-Procedure Init-Emp
   Let $OutputLine = &EC.Emplid
End-Procedure

Begin-Procedure Process-Emp
   Write #10 from $OutputLine
End-Procedure

Then the Select Block would look like this:

Begin-Select
EC.Emplid () on-break print=never before=Init-Emp After=Process-Emp
EC.JobCode
EC.Rate
       Let $OutputLine = $OutputLine || ',' || &EC.Jobcode || ',' || &EC.Rate

When using on-break, make sure you sort by emplid. This is much simpler if your need is just to write a file from data from a table.

edited Oct 10 '16 at 21:56

answered Oct 10 '16 at 15:21

cardmagik

1,698
19
17

Thank you - I was starting to head down the path of arrays, but I'm wondering how to get each element into their respective variables - $Code_1, $Code_2 etc... – Chip Oct 10 '16 at 15:26
@chip Did this help then? Or do you have any other questions? – cardmagik Oct 10 '16 at 15:50
well, it's still unclear how to get the values from the array into each of the variables. Your example loops through the entire array, loading the values into the same variable each time - I have 10 variables I need to populate - is it possible to change which variable is getting updated on each pass through the loop? – Chip Oct 10 '16 at 16:16
@chip Sorry, not sure what your question is. Can you show some pseudo code of what the process is you need? I can't get it out of your example. – cardmagik Oct 10 '16 at 18:55
I added some detail to the question - does that clarify things? – Chip Oct 10 '16 at 20:22
@chip - updated my answer - 2 solutions - the second one is preferable because it doesn't need an array. Note I've used comma-delimited output not spaces. Also, this will need some work - perhaps to change Rate to a character string. Finally, I'd like to point out that your initial question is not necessarily what you needed to solve this problem. It really doesn't need dynamic variables but instead needs processing logic to solve the problem. To paraphrase the book Code Complete, always do your design first and perhaps write PseudoCode and not just code away. – cardmagik Oct 10 '16 at 21:57

Are dynamic variables possible in SQR (not dynamic SQL)

1 Answers1