XMLAGG
Translation specification for transforming the XMLAGG function to Snowflake LISTAGG
Description
Construct an XML value by performing an aggregation of multiple rows. For more information check XMLAGG.
XMLAGG (
XML_value_expr
[ ORDER BY order_by_spec [,...] ]
[ RETURNING { CONTENT | SEQUENCE } ]
)
order_by_spec := sort_key [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
Sample Source Patterns
Setup data
Teradata
create table orders (
o_orderkey int,
o_totalprice float);
insert into orders values (1,500000);
insert into orders values (2,100000);
insert into orders values (3,600000);
insert into orders values (4,700000);
Snowflake
CREATE OR REPLACE TABLE orders (
o_orderkey int,
o_totalprice float)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;
INSERT INTO orders
VALUES (1,500000);
INSERT INTO orders
VALUES (2,100000);
INSERT INTO orders
VALUES (3,600000);
INSERT INTO orders
VALUES (4,700000);
XMLAGG transformation
Teradata
select
xmlagg(o_orderkey order by o_totalprice desc) (varchar(10000))
from orders
where o_totalprice > 5;
Snowflake
SELECT
LEFT(TO_VARCHAR(LISTAGG ( o_orderkey, ' ')
WITHIN GROUP(
order by o_totalprice DESC NULLS LAST)), 10000)
from
orders
where o_totalprice > 5;
Known Issues
1. The RETURNING clause is currently not supported.
The user will be warned that the translation of the returning clause will be added in the future.
Related EWIs
No related EWIs.
Last updated