MMULT_q

Updated 2023-10-17 13:14:47.130000

Syntax

SELECT * FROM [westclintech].[wct].[MMULT_q] (
  <@Matrix_A_RangeQuery, nvarchar(max),>
 ,<@Matrix_B_RangeQuery, nvarchar(max),>)

Description

Use MMULT_q to calculate the matrix product of two arrays. The result is a TABLE where the maximum row number is equal to the number of rows in the first array and the maximum column number is the number of columns in the second array

Arguments

@Matrix_B_RangeQuery

the select statement, as text, used to determine the 'B' matrix to be used in this function. When selecting data from a TABLE or a VIEW, specify the column names. If you just want to pass the values directly to the function, you can simply specify the values in the SELECT statement and use UNION ALL to enter the next row(s). Make sure that the entered values are of the type float or of a type that implicitly converts to float. Data returned from a TABLE or a VIEW in the @Matrix_B_RangeQuery select must be of the type float or of a type that implicitly converts to float.

@Matrix_A_RangeQuery

the select statement, as text, used to determine the 'A' matrix to be used in this function. When selecting data from a TABLE or a VIEW, specify the column names. If you just want to pass the values directly to the function, you can simply specify the values in the SELECT statement and use UNION ALL to enter the next row(s). Make sure that the entered values are of the type float or of a type that implicitly converts to float. Data returned from a TABLE or a VIEW in the @Matrix_A_RangeQuery select must be of the type float or of a type that implicitly converts to float.

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": "7fc4bb6f-2e82-455d-8cc3-06239ac130bd", "colName": "RowNum", "colDatatype": "int", "colDesc": "The zero-based row index for the matrix"}, {"id": "2d1331c6-0167-4e5d-926e-e3df8150cb23", "colName": "ColNum", "colDatatype": "int", "colDesc": "The zero-based column index for the matrix"}, {"id": "8b9b31ed-473e-4ec6-8a7e-eef0bdffdb87", "colName": "ItemValue", "colDatatype": "float", "colDesc": "The value at RowNum, ColNum"}]}

Remarks

The number of columns in the 'A' array must be equal to the number of rows in the 'B' array or an error will be returned.

Use the MMULT function for simpler queries.

Use MMULTN_q for a table in normal form.

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

If the array contains a blank, it will be treated as zero.

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

Examples

In this example, we calculate the matrix product directly from the SELECT statement.

SELECT C.*

FROM wct.MMULT_q(

                    'SELECT 5,6,7,8 UNION ALL

      SELECT 9,10,-11,12 UNION ALL

      SELECT 16,15,14,13',

                    'SELECT 1,8,9,13 UNION ALL

      SELECT 2,7,10,14 UNION ALL

      SELECT -3,6,11,15 UNION ALL

      SELECT 4,5,12,16'

                ) 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":"28"},{"RowNum":"0","ColNum":"1","ItemValue":"164"},{"RowNum":"0","ColNum":"2","ItemValue":"278"},{"RowNum":"0","ColNum":"3","ItemValue":"382"},{"RowNum":"1","ColNum":"0","ItemValue":"110"},{"RowNum":"1","ColNum":"1","ItemValue":"136"},{"RowNum":"1","ColNum":"2","ItemValue":"204"},{"RowNum":"1","ColNum":"3","ItemValue":"284"},{"RowNum":"2","ColNum":"0","ItemValue":"56"},{"RowNum":"2","ColNum":"1","ItemValue":"382"},{"RowNum":"2","ColNum":"2","ItemValue":"604"},{"RowNum":"2","ColNum":"3","ItemValue":"836"}]}