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).