7 Replies Latest reply: Aug 16, 2011 4:34 AM by Christian Henke RSS

    Problem with string literals

      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
          Kaushik Solanki

          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

              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
                  Kaushik Solanki

                  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

                      Hi,

                       

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

                       

                      Thanks

                       

                      Christian

                        • Problem with string literals
                          Kaushik Solanki

                          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

                              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

                          • Re: Problem with string literals

                            Hi,

                             

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

                             

                            Thanks

                             

                            Christian