Dynamic column references in SQR

Lets say you are given a snapshot of data as shown in Table-1.

TABLE-1

Emplid Effdt Field Value
100 01-Jan-2000 COMPANY 203
100 01-Jan-2000 BUSINESS_UNIT 00203
100 01-Jan-2000 JOBCODE 320032
100 01-Jan-2000 DEPTID 20302
100 01-Jan-2000 COMPRATE 50000
100 11-Jan-2000 JOBCODE 340045
100 11-Jan-2000 DEPTID 20301
100 01-Feb-2001 COMPRATE 55000

and you are asked to transpose the data, as represented by TABLE-2

TABLE-2

Emplid Effdt Company BU Deptid Jobcode Comprate
100 01-Jan-2000 203 00203 20302 320032 50000
100 11-Jan-2000 20301 340045
100 01-Feb-2001 55000

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: