FWDSUB

Updated 2023-10-16 19:18:28.100000

Syntax

SELECT [westclintech].[wctMath].[wct].[FWDSUB](
  <@Matrix1, nvarchar(max),>
 ,<@Matrix2, nvarchar(max),>)

Description

Use the scalar function FWDSUB to return a solution to the equation A*x=b, when A is a lower-triangular matrix.

FWDSUB expects a string representation of the matrix, with columns separated by commas and rows separated by semi-colons.

Arguments

@Matrix1

a string representation of a lower-triangular matrix.

@Matrix2

a string representation of a vector having the same number of rows as @Matrix1.

Return Type

nvarchar(max)

Remarks

The number of rows in @Matrix1 must equal the number of rows in @Matrix2.

The string representations of @Matrix1 and @Matrix2 must only contain numbers, commas (to separate the columns), and semi-colons to separate the rows.

Consecutive commas will generate an error.

Consecutive semi-colons will generate an error.

Non-numeric data between commas will generate an error

Non-number data between semi-colons will generate an error

To convert non-normalized data to a string format, use the Matrix2String or the Matrix2String_q function.

To convert normalized data to a string format, us the NMatrix2String or the NMatrix2String_q function.

To convert the string result to a table, us the table-valued function MATRIX.

Examples

Let’s assume that we had the following matrices A and y, and we want to calculate the vector x such that A*x=y.

A = [3,0,0,0;-1,1,0,0;3,-2,-1,0;1,-2,6,2]y = [5;6;4;2]

We could enter the following SQL to perform the calculation.

DECLARE @A as varchar(max);

DECLARE @y as varchar(max);

SET @A = '3,0,0,0;-1,1,0,0;3,-2,-1,0;1,-2,6,2';

SET @y = '5;6;4;2';

SELECT wct.FWDSUB(@A, @y) as x;

This produces the following result.

{"columns":[{"field":"x"}],"rows":[{"x":"1.66666666666667;7.66666666666667;-14.3333333333333;50.8333333333333"}]}

Which will produce exactly the same result.

The matrices do not have to assigned variables before passed into the FWDSUB function; the string can be passed in directly.

SELECT wct.FWDSUB('3,0,0,0;-1,1,0,0;3,-2,-1,0;1,-2,6,2', '5;6;4;2') as x;

This produces the following result.

{"columns":[{"field":"x"}],"rows":[{"x":"1.66666666666667;7.66666666666667;-14.3333333333333;50.8333333333333"}]}

In this example, the matrix values are stored on a table in the database and are converted to a string value using the MATRIX2STRING function.

/* Put matrices into a table */

SELECT *

INTO #A

FROM

(

    SELECT 3,

           0,

           0,

           0,

           5

    UNION ALL

    SELECT -1,

           1,

           0,

           0,

           6

    UNION ALL

    SELECT 3,

           -2,

           -1,

           0,

           4

    UNION ALL

    SELECT 1,

           -2,

           6,

           2,

           2

) A(xo, x1, x2, x3, y);

/* Do the forward substitution */

SELECT wct.FWDSUB(wct.MATRIX2STRING('#A', 'xo,x1,x2,x3', '', NULL), wct.MATRIX2STRING(

          '#A', 'y', '', NULL)) as x;

This produces the following result.

{"columns":[{"field":"x"}],"rows":[{"x":"1.66666666666667;7.66666666666667;-14.3333333333333;50.8333333333333"}]}

If we wanted to return the matrix product as a normalized table, we can use the table-valued function MATRIX to do that.

SELECT *

FROM wct.MATRIX(wct.FWDSUB('3,0,0,0;-1,1,0,0;3,-2,-1,0;1,-2,6,2', '5;6;4;2'));

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":"1.66666666666667"},{"RowNum":"1","ColNum":"0","ItemValue":"7.66666666666667"},{"RowNum":"2","ColNum":"0","ItemValue":"-14.3333333333333"},{"RowNum":"3","ColNum":"0","ItemValue":"50.8333333333333"}]}