Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
imark
New Contributor II

Dollar sign conflict between QV and Snowflake

I am building a small script to:

  1. Fetch a few tables from a Postgre database
  2. Shape the data into a single string so that it can be passed into VALUES
  3. Insert them into a Snowflake database; INSERT INTO ... VALUES (), (), ()

What makes things complicated is that some of the text fields in the source tables have all possible types of characters, including the single quote ' as part of a string. E.g. 

 

 

 

field1 || field2 || field5
1 || Spot ||Let's go out tonight

 

 

 

In order insert them properly, during point 1 I already make provisions to escape such fields based on Snowflake's documentation, see here.

However this seems to jam when there actually is a single quote in the string, and this does not seem to be a Snowflake issue because by doing it in the console on hardcoded values works fine. So it must be that, for some reason the combination of the double dollar sign $$ + a single quote throws off QV.

Here a picture from the log when the script crashes. Note how, for some reason,  single quotes appear to double.

QV_Snowflake_error.PNG

And here the script (some fields are anonomised)

 

 

 

ODBC CONNECT TO Postgres;

PgTable:
SQL
--// 2 fields in total
--// '''' is PG's way of escaping the single quote
SELECT 
		'(' || '''' || ci1_group  || '''' || ',' || ci1_id || ',' || '$$ ' || ci1_info || ' $$' || ',' || '''' || ci1_source || '''' || ',' || COALESCE('$$' || ci1_label || '$$','NULL') || ')'  AS single_string
		,ROW_NUMBER() OVER() AS rn1	
	FROM schema.table_1
	;
	

FOR i=1 TO NoOfRows('PgTable') // about 1400

tempt:
LOAD single_string AS ss RESIDENT PgTable WHERE rn1 = $(i);
LET vValues = PEEK('ss');
DROP TABLES tempt;

TRACE $(i);
TRACE $(vValues);

ODBC CONNECT TO Snowflake;
SQL
INSERT INTO "database"."schema".SF_table_1 (ci1_group, ci1_id, ci1_info, ci1_source, ci1_label) 
		VALUES $(vValues)
		;
DISCONNECT;

NEXT


EXIT SCRIPT