MINVERSEN

Updated 2023-10-16 20:57:12.653000

Syntax

SELECT * FROM [westclintech].[wct].[MINVERSEN] (
  <@Matrix_TableName, nvarchar(4000),>
 ,<@Matrix_Key1ColumnName, nvarchar(4000),>
 ,<@Matrix_Key2ColumnName, nvarchar(4000),>
 ,<@Matrix_DataColumnName, nvarchar(4000),>
 ,<@Matrix_GroupedColumnName, nvarchar(4000),>
 ,<@Matrix_GroupedColumnValue, sql_variant,>)

Description

Use the table-valued function MINVERSEN to calculate the matrix inverse of a square (N x N) array that has been stored in 3rd normal form.

Arguments

@Matrix _Key2ColumnName

the name, as text, of the column in the table or view specified by @Matrix _TableName that contains the ‘column number’ value used in the array.

@Matrix_GroupedColumnName

the name, as text, of the column in the table or view specified by @Matrix_TableName which will be used for grouping the results.

@Matrix_Key1ColumnName

the name, as text, of the column in the table or view specified by @Matrix_TableName that contains the ‘row number’ value used in the array.

@Matrix_GroupedColumnValue

the column value to do the grouping on.

@Matrix _DataColumnName

The name, as text, of the column in the table or view specified by @Matrix _TableName that contains the matrix values to be used in the product. Data returned from the @Matrix_DataColumnName must be of the type float or of a type that implicitly converts to float.

@Matrix_TableName

the name, as text, of the table or view that contains the values in the square (N x N) array to be used in the MINVERSEN calculation.

Return Type

table

{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "94210022-1369-4ee7-92bd-b9370e70622b", "colName": "RowNum", "colDatatype": "int", "colDesc": "The zero-based row index for the matrix"}, {"id": "6ecdda4f-bc0c-4e93-b94c-102e91c0a1b7", "colName": "ColNum", "colDatatype": "int", "colDesc": "The zero-based column index for the matrix"}, {"id": "290cc923-0c97-4e11-806a-15dd01b46a37", "colName": "ItemValue", "colDatatype": "float", "colDesc": "The value at RowNum, ColNum"}]}

Remarks

The number of columns in the array must equal the number of rows in the array.

If the matrix determinant is equal to zero an error will be returned.

Use the MINVERSEN_q function for more complex queries or to pass the matrices directly into the function without having to store them in a table.

Use MINVERSE for a table not in normal form.

If the array contains NULL, then NULL will be returned.

The function returns an error if the array contains a non-numeric value.

Examples

CREATE TABLE #i

(

    MatrixID varchar(5),

    rowno int,

    colno int,

    val float

);

INSERT INTO #i

VALUES

('1A', 0, 0, 1.0);

INSERT INTO #i

VALUES

('1A', 0, 1, 2.0);

INSERT INTO #i

VALUES

('1A', 0, 2, 1.0);

INSERT INTO #i

VALUES

('1A', 1, 0, 3.0);

INSERT INTO #i

VALUES

('1A', 1, 1, 4.0);

INSERT INTO #i

VALUES

('1A', 1, 2, -1.0);

INSERT INTO #i

VALUES

('1A', 2, 0, 0.0);

INSERT INTO #i

VALUES

('1A', 2, 1, 2.0);

INSERT INTO #i

VALUES

('1A', 2, 2, 0.0);

SELECT C.*

FROM wct.MINVERSEN('#i', 'rowno', 'colno', 'val', 'MatrixID', '1A') C;

This produces the following result.

{"columns":[{"field":"RowNum","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ColNum","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ItemValue","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"RowNum":"0","ColNum":"0","ItemValue":"0.25"},{"RowNum":"0","ColNum":"1","ItemValue":"0.25"},{"RowNum":"0","ColNum":"2","ItemValue":"-0.75"},{"RowNum":"1","ColNum":"0","ItemValue":"0"},{"RowNum":"1","ColNum":"1","ItemValue":"0"},{"RowNum":"1","ColNum":"2","ItemValue":"0.5"},{"RowNum":"2","ColNum":"0","ItemValue":"0.75"},{"RowNum":"2","ColNum":"1","ItemValue":"-0.25"},{"RowNum":"2","ColNum":"2","ItemValue":"-0.25"}]}

If we wanted to return the results in matrix form, we can use the PIVOT function, though this requires knowing the number of columns returned by the function (which is equal to the number of rows).

SELECT [0],

       [1],

       [2]

FROM

(

    SELECT C.*

    FROM wct.MINVERSEN('#i', 'rowno', 'colno', 'val', 'MatrixID', '1A') C

) M

PIVOT

(

    MAX(ItemValue)

    FOR colnum IN ([0], [1], [2])

) AS pvt

ORDER BY rownum;

This produces the following result.

{"columns":[{"field":"0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"0.25","1":"0.25","2":"-0.75"},{"0":"0","1":"0","2":"0.5"},{"0":"0.75","1":"-0.25","2":"-0.25"}]}