Problem with string literals

[#1]

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

[kaushiknsolanki]

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

[#3]

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

[kaushiknsolanki]

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

[#5]

Hi,

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

Thanks

Christian

[#6]

Hi,

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

Thanks

Christian

[kaushiknsolanki]

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

[#8]

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