Dynamic column references in SQR
January 21, 2011 Leave a comment
Lets say you are given a snapshot of data as shown in Table-1.
and you are asked to transpose the data, as represented by TABLE-2
To achieve this in SQR, the crudest solution is to use a combination of BEGIN-SELECT and BEGIN-SQL.
BEGIN-SELECT EMPLID EFFDT FIELD VALUE do UPDATE-TABLE-2 FROM TABLE-1
Now, lets focus on what would go in UPDATE-TABLE-2 procedure. Every row returned by the BEGIN-SELECT warrants an update on a different column in TABLE-2. A quick solution is to create an update statement for each of the columns BU, DEPTID, JOBCODE etc., and make them execute only when that value occurs in FIELD column.
BEGIN-PROCEDURE UPDATE-TABLE-2 If &FIELD = 'BU' Begin-SQL UPDATE TABLE-2 SET BU = &VALUE WHERE EMPLID = &EMPLID AND EFFDT = &EFFDT End-SQL end-if IF &FIELD = 'DEPTID' Begin-SQL UPDATE TABLE-2 SET DEPTID = &VALUE WHERE EMPLID = &EMPLID AND EFFDT = &EFFDT End-SQL end-if !...and continue to create an IF condition each for COMPANY, JOBCODE, COMPRATE etc. END-PROCEDURE
The crudeness of the solution shows up, as the number of columns in TABLE-2 increases. SQR packs a beautiful technique for dynamic column references. With this technique, the UPDATE-TABLE-2 procedure is compressed and light-weighted as show below:
BEGIN-PROCEDURE UPDATE-TABLE-2 Begin-SQL UPDATE TABLE-2 SET [&FIELD] = &VALUE WHERE EMPLID = &EMPLID AND EFFDT = &EFFDT End-SQL END-PROCEDURE
Any variable surrounded by square brackets “” is treated as database column in a SQL statement.