10 Replies Latest reply on Jul 30, 2014 7:58 AM by Alex1

    Scape a single quote in SQL statement prompt code

    Alex1

      Hi experts!

      I'm using obiee 11.1.1.7 and I have a problem doing a SQL Statement using prompts.

       

      I have a member of my dimension that contains single quote like: L'Oreal and my prompt doesn't work propertly.

       

      in sql statement I have this code:

       

      SELECT "Dim_Product"."Description" FROM "Products" WHERE "Dim_Product"."Name" = CASE WHEN length('@{pv_name}')=0 THEN "Dim_Product"."Name" ELSE '@{pv_name}' END)

       

      I think that I have to add something like encode in my presentation variable but I don't know how --> '@{pv_name}'

       

      Any help???

        • 1. Re: Scape a single quote in SQL statement prompt code
          SriniVEERAVALLI

          Try to go with html encode for ' as %27

          or elas you may replace ' with none in where clause condition for both Prodname case statement.

          • 2. Re: Scape a single quote in SQL statement prompt code
            Alex1

            Thanks for your reply! I tried your solution but I have some errors and I can't see good results.

             

            Could you type the sentence that I have to put in my prompt?

             

            SELECT "Dim_Product"."Description" FROM "Products" WHERE "Dim_Product"."Name" = CASE WHEN length('@{pv_name}')=0 THEN "Dim_Product"."Name" ELSE '@{pv_name}' END)

             

            My problem is in ELSE because some elements of pv_name contains single quote.


            Thanks!

            • 3. Re: Scape a single quote in SQL statement prompt code
              SriniVEERAVALLI

              Try to use Double column. That would be more easy, if not let me know.

              • 4. Re: Scape a single quote in SQL statement prompt code
                Christian Berg

                You use two single quotes...not a double as indicated earlier. Copy + paste this to see it:

                 

                <saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlVersion="201201160" xmlns:sawx="com.siebel.analytics.web/expression/v1.1">
                   <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;A - Sample Sales&quot;">
                      <saw:columns>
                         <saw:column xsi:type="saw:regularColumn" columnID="ca80ba618d36b16e9">
                            <saw:columnFormula>
                               <sawx:expr xsi:type="sawx:sqlExpression">'@{pv_test}{L''Oreal}'</sawx:expr></saw:columnFormula>
                            <saw:tableHeading>
                               <saw:caption fmt="text">
                                  <saw:text>pv</saw:text></saw:caption></saw:tableHeading>
                            <saw:columnHeading>
                               <saw:caption fmt="text">
                                  <saw:text>pv_test</saw:text></saw:caption></saw:columnHeading></saw:column></saw:columns></saw:criteria>
                   <saw:views currentView="0">
                      <saw:view xsi:type="saw:compoundView" name="compoundView!1">
                         <saw:cvTable>
                            <saw:cvRow>
                               <saw:cvCell viewName="titleView!1">
                                  <saw:displayFormat>
                                     <saw:formatSpec/></saw:displayFormat></saw:cvCell></saw:cvRow>
                            <saw:cvRow>
                               <saw:cvCell viewName="tableView!1">
                                  <saw:displayFormat>
                                     <saw:formatSpec/></saw:displayFormat></saw:cvCell></saw:cvRow></saw:cvTable></saw:view>
                      <saw:view xsi:type="saw:titleView" name="titleView!1"/>
                      <saw:view xsi:type="saw:tableView" name="tableView!1" scrollingEnabled="true">
                         <saw:edges>
                            <saw:edge axis="page" showColumnHeader="true"/>
                            <saw:edge axis="section"/>
                            <saw:edge axis="row" showColumnHeader="true">
                               <saw:edgeLayers>
                                  <saw:edgeLayer type="column" columnID="ca80ba618d36b16e9"/></saw:edgeLayers></saw:edge>
                            <saw:edge axis="column" showColumnHeader="rollover"/></saw:edges></saw:view></saw:views></saw:report>
                
                • 5. Re: Scape a single quote in SQL statement prompt code
                  Alex1

                  Thanks for your answers

                   

                  I can't use double column because my prompt is contrain with another prompt that shows description. I need this description to constrain another prompt and I must use SQL code to constrain.

                   

                  I think that the best option is modify my query with encode html but I don't know how.

                  • 6. Re: Scape a single quote in SQL statement prompt code
                    Alex1

                    Finally I could avoid apostrophes making a replacement for an accent on logic layer.


                    Like ' by ´


                    Thanks for your advices!

                    • 7. Re: Scape a single quote in SQL statement prompt code
                      Christian Berg

                      Whoa that's nasty :-)  Hope the model (and all other conformed ones around it) remain(s) consistent in terms of results and usage. Also that no one has existing saved customizations which search for that string...

                      • 8. Re: Scape a single quote in SQL statement prompt code
                        Alex1

                        I know that isn't a good solution, but results are good now...I don't know how to escape ( ' ) because OBIEE uses this character for variables...

                        • 9. Re: Scape a single quote in SQL statement prompt code
                          Christian Berg

                          Well you can't inside of a presentation variable...so unless you work around that requirement with some LOV-type dimension to look up values, you won't have a lot of alternatives.

                          • 10. Re: Scape a single quote in SQL statement prompt code
                            Alex1

                            Thanks Christian

                             

                            My customer understood this issue and they are agree with my solution.