Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server MDETERM function


MDETERMN_q
 
Updated: 17 August 2010

Use MDETERMN_q to calculate the determinant of an N x N matrix, where N specifies the number of columns in the matrix. For matrices in de-normalized form, use the MDETERM_q function.
Syntax
SELECT [wctMath].[wct].[MDETERMN_q] (
   <@Matrix_RangeQuery, nvarchar(max),>)
Arguments
 
@Matrix_RangeQuery
the select statement, as text, used to determine the N x N matrix to be used in this function. The query needs to specify the ‘row number’ column name, the ‘column number’ column name, and data column name, if you are selecting data from a table or a view. If you are passing the data directly into the function using then each SELECT statement will contain three pieces of data in the following order: row number; column number; value. The value data must be of the type float or of a type that implicitly converts to float. Data returned from the @Matrix_RangeQuery select must be of the type float or of a type that implicitly converts to float.
Return Types
float
Remarks
·         If the number of rows in the matrix is not equal to the number of columns, MDETERM will return an error.
·         No GROUP BY is required for this function even though it produces aggregated results.
·         Use the MDETERMN function for simpler queries.
·         Use MDETERM_q for a table not in normal form.
·         The function returns an error if the matrix contains NULL.
·         You can directly pass the matrix into @Matrix_RangeQuery in the form of a SELECT statement without referring to a table or a view.
Examples
CREATE TABLE #m(
      rowno int,
      colno int,
      val   float
)
 
INSERT INTO #m VALUES (0,0,1)
INSERT INTO #m VALUES (0,1,2)
INSERT INTO #m VALUES (0,2,3)
INSERT INTO #m VALUES (0,3,10)
INSERT INTO #m VALUES (1,0,6)
INSERT INTO #m VALUES (1,1,4)
INSERT INTO #m VALUES (1,2,4)
INSERT INTO #m VALUES (1,3,11)
INSERT INTO #m VALUES (2,0,7)
INSERT INTO #m VALUES (2,1,8)
INSERT INTO #m VALUES (2,2,9)
INSERT INTO #m VALUES (2,3,12)
INSERT INTO #m VALUES (3,0,16)
INSERT INTO #m VALUES (3,1,15)
INSERT INTO #m VALUES (3,2,14)
INSERT INTO #m VALUES (3,3,13)
 
SELECT wct.MDETERMN_q(
      'SELECT rowno
      ,colno
      ,val
      FROM #m'
      ) as [|A|]
This produces the following result
                   |A|
----------------------
                   208
 
(1 row(s) affected)

We could have also entered data directly to the function without having to insert it into a table or create a VIEW.
SELECT wct.MDETERMN_q(
      'SELECT 0,0,1 UNION ALL
      SELECT 0,1,2 UNION ALL
      SELECT 0,2,3 UNION ALL
      SELECT 0,3,10 UNION ALL
      SELECT 1,0,6 UNION ALL
      SELECT 1,1,4 UNION ALL
      SELECT 1,2,4 UNION ALL
      SELECT 1,3,11 UNION ALL
      SELECT 2,0,7 UNION ALL
      SELECT 2,1,8 UNION ALL
      SELECT 2,2,9 UNION ALL
      SELECT 2,3,12 UNION ALL
      SELECT 3,0,16 UNION ALL
      SELECT 3,1,15 UNION ALL
      SELECT 3,2,14 UNION ALL
      SELECT 3,3,13'
      ) as [|A|]
 

This produces the following result

                   |A|
----------------------
                   208
 

(1 row(s) affected)

See Also

·         Blog Post - SQL Server Matrix Functions


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service