Row Constructor (or Table-Valued Constructor) as Derived Table [CORE3880]

[firebird-automations]

Submitted by: @sim1984

Votes: 4

Add support Row Constructor (or Table-Valued Constructor) as Derived Table

SELECT * FROM (VALUES (1, 3), (1, 5)) AS T(A, B)

Result: A B 1 3 1 5

Row Constructor (or Table-Valued Constructor) as Source in a MERGE Statement

MERGE Currency AS Target USING (VALUES ('USD', 'U.S. Dollar'), ('EUR', 'Euro'), ('CAD', 'Canadian Dollar'), ('JPY', 'Japanese Yen')) AS Source ( CurrencyCode, CurrencyName ) ON Target.CurrencyCode = Source.CurrencyCode WHEN MATCHED THEN UPDATE SET CurrencyName = Source.CurrencyName WHEN NOT MATCHED THEN INSERT ( CurrencyCode, CurrencyName ) VALUES ( Source.CurrencyCode, Source.CurrencyName )

[firebird-automations]

Modified by: @sim1984

priority: Major \[ 3 \] =\> Minor \[ 4 \]

[firebird-automations]

Modified by: @sim1984

description: Add support derivative tables on the basis of values of constants SELECT \* FROM \(VALUES \(1, 3\), \(1, 5\)\) AS T\(A, B\) Result: A B 1 3 1 5 =\> Add support Row Constructor \(or Table\-Valued Constructor\) as Derived Table SELECT \* FROM \(VALUES \(1, 3\), \(1, 5\)\) AS T\(A, B\) Result: A B 1 3 1 5 summary: Derivative tables on the basis of values of constants =\> Row Constructor \(or Table\-Valued Constructor\) as Derived Table

[firebird-automations]

Modified by: @sim1984

description: Add support Row Constructor \(or Table\-Valued Constructor\) as Derived Table SELECT \* FROM \(VALUES \(1, 3\), \(1, 5\)\) AS T\(A, B\) Result: A B 1 3 1 5 =\> Add support Row Constructor \(or Table\-Valued Constructor\) as Derived Table SELECT \* FROM \(VALUES \(1, 3\), \(1, 5\)\) AS T\(A, B\) Result: A B 1 3 1 5 Row Constructor \(or Table\-Valued Constructor\) as Source in a MERGE Statement MERGE Currency AS Target USING \(VALUES \('USD', 'U\.S\. Dollar'\), \('EUR', 'Euro'\), \('CAD', 'Canadian Dollar'\), \('JPY', 'Japanese Yen'\)\) AS Source \( CurrencyCode, CurrencyName \) ON Target\.CurrencyCode = Source\.CurrencyCode WHEN MATCHED THEN UPDATE SET CurrencyName = Source\.CurrencyName WHEN NOT MATCHED THEN INSERT \( CurrencyCode, CurrencyName \) VALUES \( Source\.CurrencyCode, Source\.CurrencyName \)

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov \[ dimitr \]

[sim1984]

The full syntax is described in ISO/IEC 9075-2:1999 7.3 <table value constructor>

[mrotteveel]

@sim1984 Lets not use outdated specifications, and instead use ISO/IEC 9075-2:2023 (still section 7.3).