topic Problem with string literals in QlikView

Problem with string literals

Fri, 12 Aug 2011 11:46:37 GMT

Sometimes I am using QC to create SQL strings to be send to an Oracle-DB. It becomes often difficult when the string contains characters that can be interpreted by Oracle.

The current problem (as anexample and attached):

// Load data from data source

CommentsFromDataSource:

LOAD * INLINE [

Comments

This

That

] ;

// concat into one field

AllCommentsTogether:

Load Distinct

concat(distinct Comments, '; ') as AllComments

Resident CommentsFromDataSource ;

// assign to variable

LET vAllComments = chr(39)&peek('AllComments')&chr(39) ;

// use variable in insert string

InsertString:

LOAD

'insert into TABLE (Field) value ($(vAllComments));' as InsertString

Autogenerate (1) ;

The result I want to get is: insert into TABE (Field) value ('This; That');

What I get is: insert into TABLE (Field) value (''This; That'');

wih duplicated literals which are not accepted by Oracle.

Any Idea how to tell QV not to duplicate the literal when adding the string from the variable?

Thanks in advance

Christian

Problem with string literals

kaushiknsolanki — Fri, 12 Aug 2011 12:01:47 GMT

Hi,

Try this.

// Load data from data source

CommentsFromDataSource:

LOAD * INLINE [

Comments

This

That

] ;

// concat into one field

AllCommentsTogether:

Load Distinct

concat(distinct Comments, '; ') as AllComments

Resident CommentsFromDataSource ;

// assign to variable

LET vAllComments = chr(39) & peek('AllComments') & chr(39);

// use variable in insert string

InsertString:

LOAD

'insert into TABLE (Field) value ('& chr(39) & $(vAllComments)& chr(39) &');' as InsertString

Autogenerate 1 ;

Regards,

Kaushik Solanki

Re: Problem with string literals

Fri, 12 Aug 2011 12:40:01 GMT

Dear Kaushik,

yes this works fine in the simple example, thank you. Now a bit more advanced: As Oracle does not like all cahracters in a text string I think I have to replace them in advance with the corresponding chr(xx) as in QV.

xCommentsFromDataSource_t0:

LOAD * INLINE [

xComments

This & That

Today & Tomorrow

] ;

CommentsFromDataSource:

LOAD

replace( xComments, chr(38), chr(39)&'||chr(38)||'&chr(39) ) as Comments

RESIDENT xComentsFromDataSource_t0 ;

DROP TABLE xCommentsFromDataSource_t0 ;

AllCommentsTogether:

LOAD DISTINCT

concat( distinct Comments, '; ') as AllComments

RESIEDENT CommentsFromDataSource ;

LET vAllComments = chr(39)&peek('AllComments')&chr(39) ;

InsertString:

LOAD

'insert into TABLE (Field) value ($(vAllComments));' as InsertString

AUTOGENERATE (1)

This results in: insert into TABLE(Field) value (''This ''||chr(38)||'' That; Today ''||chr(38)||'' Tomorrow'');

with double literals before/after the ||

instead of insert into TABLE (Field) value (This '||chr(38)||' THAT; Today....

what would be needed for Oracle. In this case adding the chr(39) before/after the variable doesn't help because the other double literals are no affected and anyway, the script crashes...

Any further ideas ?

Thanks Again

Christian

Problem with string literals

kaushiknsolanki — Sat, 13 Aug 2011 04:46:28 GMT

Hi,

Can you tell me what query oracle will accept in this case.

I will try to make it through the Qlikview.

Regards,

Kaushik Solanki

Problem with string literals

Mon, 15 Aug 2011 07:45:18 GMT

Hi,

Oracle would accept < insert into TABLE (Field) value ('This '||chr(38)||' That; Today '||chr(38)||' Tomorrow'); >

Thanks

Christian

Re: Problem with string literals

Mon, 15 Aug 2011 07:46:39 GMT

Hi,

Oracle would accept < insert into TABLE (Field) value ('This '||chr(38)||' That; Today '||chr(38)||' Tomorrow'); >

Thanks

Christian

Problem with string literals

kaushiknsolanki — Tue, 16 Aug 2011 05:01:02 GMT

Hi,

Try this.

// Load data from data source
xCommentsFromDataSource_t0:
LOAD * INLINE [
xComments
This & That
Today & Tomorrow
] ;

CommentsFromDataSource:
LOAD
replace(xComments, chr(38), chr(39)&'||chr(38)||'&chr(39)) as Comments
RESIDENT xCommentsFromDataSource_t0 ;
// Drop table xCommentsFromDataSource_t0 ;

// concat into one field
AllCommentsTogether:
Load distinct
concat( distinct Comments, ';') as AllComments
Resident CommentsFromDataSource ;

// assign to variable
LET vAllComments = peek('AllComments',0,'AllCommentsTogether') ;

// use variable in insert string
InsertString:
LOAD
'insert into TABLE (Field) value ($(vAllComments));' as InsertString
Autogenerate (1) ;

Regards,

Kaushik Solanki

Re: Problem with string literals

Tue, 16 Aug 2011 08:34:46 GMT

Hi Kaushik,

thanks for thinking aboutit again - but unfortunately the result is the same. It seems, that QV dublicates every literal in a variable when the variable is inserted in a string.

The way to this solve in my example seems to be to replace the literals created by QV with anohter statement like:

InsertString:

LOAD

replace( InsertString, chr(39)&chr(39), chr(39) ) as InsertString ;

LOAD

'insert into TABLE (Field) value ($(vAllComments));' as Insert String

Autogenerate (1)

Of course this can be done in one line as well...

Christian