0
276
Viewsconcatenate 2 columns data to use as a one as a condition in joining statement(Snowflake procedure)
I am working on the snowflake procedure.
I have 2 tables i.e ASSET_DEC table and ASSET_DATA table.
The column serviceid_versionid is there in ASSET_DATA having value like 1234_5678.
Now, the column in ASSET_DEC are serviceid(value = 1234) and versionid(value = 5678).
I need to write a query where I need to join these 2 tables with the condition like
left outer join logs.public.ASSET_DATA asd1 on asd1.serviceid_versionid = concat_ws('_',ASSET_DEC.serviceid,ASSET_DEC.versionid).
The error I am getting on using concat_ws is:
JavaScript compilation error: Uncaught SyntaxError: Unexpected identifier in PROD_TEST at
'"left outer join logs.public.ASSET_DATA asd1 on asd1.serviceid_versionid = concat_ws('_',ASSET_DEC.serviceid,ASSET_DEC.versionid) ";' position 124
How can I achieve this inside the snowflake procedure?
Update after @nick pointed out:
New Error Message:
Execution error in store procedure PROD_TEST: SQL compilation error: error line 6 at position 1,527 invalid identifier '_' At Snowflake.execute, line 66 position 20
·
Santiago Gelvez
1 answers
Answer question0
It is possible to use expression as part of ON condition inside JavaScript stored procedure:
CREATE OR REPLACE TABLE PUBLIC.ASSET_DATA(serviceid_versionid VARCHAR)
AS
SELECT '1234_5678';
CREATE OR REPLACE TABLE PUBLIC.ASSET_DEC(serviceid INT, versionid INT)
AS
SELECT '1234', '5678';
Proc:
CREATE OR REPLACE PROCEDURE test1 ()
RETURNS VARCHAR NOT NULL
LANGUAGE javascript
EXECUTE AS CALLER
AS
$$
var sql_stmt = `
CREATE OR REPLACE TABLE test
AS
SELECT serviceid_versionid, serviceid, versionid
FROM PUBLIC.ASSET_DATA asd1
LEFT JOIN PUBLIC.ASSET_DEC ASSET_DEC
ON asd1.serviceid_versionid = concat_ws('_',ASSET_DEC.serviceid,ASSET_DEC.versionid)
`;
var statement = snowflake.createStatement( {sqlText:sql_stmt} );
var rs = statement.execute();
$$;
Test:
CALL test1();
SELECT * FROM test;
·
Santiago Gelvez
Report
Answer question