concatenate 2 columns data to use as a one as a condition in joining statement(Snowflake procedure)

Question

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

Answer 1

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;