UDTF in Lua: Example 2

This example creates a function called unpivot_final that unpivots sales data that is stored in a row containing sales by quarter into four rows of data, one for each quarter. It also returns an additional row that contains the total value of all sales.

Code

Put the following code into /tmp/udtf_example2.nzl:

-- Usage Example:
-- select * from table (unpivot_final(2010,100,200,300,400));

total=0
function processRow(y,q1,q2,q3,q4)
    sls = {}
    sls[1] = { y, 1, q1 }
    sls[2] = { y, 2, q2 }
    sls[3] = { y, 3, q3 }
    sls[4] = { y, 4, q4 }
    total = q1 + q2 + q3 + q4
    return null
end

function outputRow(rownum)
    if sls[rownum] == null then return null end
    return sls[rownum]
end

function outputFinalRow(rownum)
    if rownum > 1 then return null end
    return { null, null, total }
end

function getType()
    return "UDTF"
end

function getName()
    return "unpivot_final"
end

function getArgs()
    return {{ "Year",     "integer"},
            { "q1sales",  "double" },
            { "q2sales",  "double" },
            { "q3sales",  "double" },
            { "q4sales",  "double" }}
end

function getShape()
    return {{ "Year",    "integer" },
            { "Quarter", "integer" },
            { "Sales",   "double"  }}
end

Compression

Compress the file /tmp/udtf_example2.nzl into a file with the name /tmp/udtf_example2.tar.

Deployment

Deploy the UDX to a Db2® instance by issuing the following CLPPlus command:

SQL> IDA DEPLOYUDX SRC /tmp/udtf_example2.tar LANGUAGE lua PROJECT example2

Running

Use this function in a SELECT statement, for example:

SELECT * FROM TABLE(unpivot_final(2010,100,200,300,400))

Year        Quarter     Sales
----------- ----------- ------------------------
       2010           1   +1.00000000000000E+002
       2010           2   +2.00000000000000E+002
       2010           3   +3.00000000000000E+002
       2010           4   +4.00000000000000E+002