11 Replies Latest reply: Dec 11, 2012 9:22 AM by user11925394 RSS

    Using Parameters in SQL-Query not only in where clauses

    user11925394
      Hi,

      I try to use Publisher parameters in the SQL Query from a Data Set.
      All of them have default values.
      So far, this is no problem, unless I try to use such a parameter value as an ordinary attribute value:

      >
      select
           case when (:pv_Group = 'no') then "DM15D_BETRIEBSTEIL"."BETR_TEIL"
                                              else :pv_some_Text end                                                                  as Betr_Teil,
           case when (:pv_Group = 'no') then "DM15D_BETRIEBSTEIL"."SUVA_NR_FORM"
                                              else :pv_some_Text end                                                        as Suva_Nr,
           case when (:pv_Group = 'no') then "DM15K_RIS_FAKTEN_PRO_BTT_JHR"."RIS_VOLLBESCH"
                                              else sum("DM15K_RIS_FAKTEN_PRO_BTT_JHR"."RIS_VOLLBESCH") end      as Vollbesch
      from "GDWH05"
      where
           "DM15D_BETRIEBSTEIL"."SUVA_NR_FORM" in (:pv_nim100)
           fetch first 65001 rows ONLY
      >

      The parameters 'pv_Group' and 'pv_nim100' are working fine. (when or where clauses)
      The parameter 'pv_some_Text' unfortunately not. (simple literals)

      When I try to validate the above SQL, I get the following Error:
      <font color="red">
      java.io.IOException: prepare query failed[nQSError: 43113] Message returned from OBIS. [nQSError: 46033] Datatype: 25 is not supported.
      </font>

      After use a cast function:

      >
      else CAST(:pv_some_Text AS CHARACTER)
      >

      I get this ERROR:
      <font color="red">
      java.io.IOException: prepare query failed[nQSError: 43113] Message returned from OBIS. [nQSError: 19002] Incorrect use of parameters. The parameters used in CAST cannot be resolved without ambiguity.
      </font>

      We use OBIEE 11.1.1.6.4 on a Win64-System.

      Thank's for any help.
        • 1. Re: Using Parameters in SQL-Query not only in where clauses
          AlexAnd
          did you try
          :pv_some_Text || ''
          instead of
          >
          CAST(:pv_some_Text AS CHARACTER)
          >
          ?
          • 2. Re: Using Parameters in SQL-Query not only in where clauses
            user11925394
            If I try your proposal, then I get the same result as in query 1.
            select
            case when (:pv_Group = 'no') then "DM15D_BETRIEBSTEIL"."BETR_TEIL"
            else :pv_some_Text end as Betr_Teil,
            case when (:pv_Group = 'no') then "DM15D_BETRIEBSTEIL"."SUVA_NR_FORM"
            else :pv_some_Text || '' end as Suva_Nr,
            case when (:pv_Group = 'no') then "DM15K_RIS_FAKTEN_PRO_BTT_JHR"."RIS_VOLLBESCH"
            else sum("DM15K_RIS_FAKTEN_PRO_BTT_JHR"."RIS_VOLLBESCH") end as Vollbesch
            from "GDWH05"
            where
            "DM15D_BETRIEBSTEIL"."SUVA_NR_FORM" in (:pv_nim100)
            fetch first 65001 rows ONLY>

            ERROR:
            <font color="red">java.io.IOException: prepare query failed[nQSError: 43113] Message returned from OBIS. [nQSError: 46033] Datatype: 25 is not supported.
            </font>

            Thank you
            • 3. Re: Using Parameters in SQL-Query not only in where clauses
              AlexAnd
              did you test your query by sql developer or sqlplus for different ":pv_Group"?

              i think your query can has some errors by "else sum("DM15K_RIS_FAKTEN_PRO_BTT_JHR"."RIS_VOLLBESCH") end as Vollbesch"

              what type of ""DM15D_BETRIEBSTEIL"."BETR_TEIL"" ?
              if string then try to use "to_char(sum("DM15K_RIS_FAKTEN_PRO_BTT_JHR"."RIS_VOLLBESCH"))"
              or try to use construction like "sum(<attribute>) over ()" or some "group by case when (:pv_Group = 'no') then "DM15D_BETRIEBSTEIL"."BETR_TEIL" else :pv_some_Text end"

              pls see also http://erpthings.blogspot.ru/2011/01/nqserror-46033-datatype-25-is-not.html

              if you can plz post sample test case

              construction like
               select :pv_some_Text as SMTH from dual
              is allowed
              • 4. Re: Using Parameters in SQL-Query not only in where clauses
                user11925394
                Hi Alex,

                let's leave away any unnecessary details.

                This is the SQL, inserted in the window 'Edit Data Set' of BIP Data Model:
                >
                select
                     '--1'     as Betr_Teil,
                     '--2'      as Suva_Nr,
                     sum("GDWH05"."DM15K_RIS_FAKTEN_PRO_BTT_JHR"."RIS_VOLLBESCH") as Vollbesch
                from "GDWH05"
                where
                     "DM15D_BETRIEBSTEIL"."SUVA_NR_FORM" in ('122-4.4')
                     fetch first 65001 rows ONLY
                >

                Everything is fine when I click OK, the script goes back to the metadata.

                Let's try this script with bind values in ORACLE SQL Developer.
                This SQL is the physical part, found in the OBIEE-Log (Log level 5), except the bind values. Therefore we find, in the where clause, the join. In the logical sql, we don't have to join, because it's handled in the Common Enterprise Information Model (CEIM)

                >
                with
                sawith0 as
                (
                select
                sum(t39617.ris_vollbesch) as c1
                from
                dm15d_betriebsteil t39455,
                dm15k_ris_fakten_pro_btt_jhr t39617
                where
                (
                t39455.id_betriebsteil = t39617.id_betriebsteil
                and t39455.suva_nr_form = '122-4.4'
                )
                )
                select
                d1.c1 as c1,
                d1.c2 as c2,
                d1.c3 as c3
                from
                (
                select
                :pv_some_text as c1,
                :pv_some_text as c2,
                sum(d1.c1) as c3
                from
                sawith0 d1
                )
                d1
                where
                rownum <= 65001
                >
                This SQL works fine, even with bind values for 'pv_some_text'.

                But, when using the following SQL in the BIP Data Model:

                >
                select
                     :pv_Text as Betr_Teil,
                     :pv_Text as Suva_Nr,
                     sum("GDWH05"."DM15K_RIS_FAKTEN_PRO_BTT_JHR"."RIS_VOLLBESCH") as Vollbesch
                from "GDWH05"
                where
                     "DM15D_BETRIEBSTEIL"."SUVA_NR_FORM" in ('122-4.4')
                     fetch first 65001 rows ONLY
                >
                The following ERROR occurs:

                <font color="red">java.io.IOException: prepare query failed[nQSError: 43113] Message returned from OBIS. [nQSError: 46008] Internal error: File server\Query\Optimizer\ServiceInterfaceMgr\Utility\Src\SQOIUTypeVisitor.cpp, line 643.</font>

                In my opinion, either I use a wrong syntax, or BIP has a problem with parsing the script.

                Thank you for your most welcome help.
                • 5. Re: Using Parameters in SQL-Query not only in where clauses
                  user11925394
                  Of course it is not 'pv_Text' furthermore it is 'pv_some_Text'.
                  Sorry…..
                  • 6. Re: Using Parameters in SQL-Query not only in where clauses
                    AlexAnd
                    heh
                    >
                    On further analysis, one of the column name is not right - the SocDescr is defined as SocDesc
                    >
                    from my above link ;)

                    so your problem is eliminated?
                    if yes then close the thread
                    • 7. Re: Using Parameters in SQL-Query not only in where clauses
                      user11925394
                      Unfortunately not, even when I type in all parameter correctly, like this:
                      select
                      :pv_some_Text as Betr_Teil,
                      :pv_some_Text as Suva_Nr,
                      sum("GDWH05"."DM15K_RIS_FAKTEN_PRO_BTT_JHR"."RIS_VOLLBESCH") as Vollbesch
                      from "GDWH05"
                      where
                      "DM15D_BETRIEBSTEIL"."SUVA_NR_FORM" in ('122-4.4')
                      fetch first 65001 rows ONLY
                      ERROR is the same:

                      <font color="red">
                      java.io.IOException: prepare query failed[nQSError: 43113] Message returned from OBIS. [nQSError: 46008] Internal error: File server\Query\Optimizer\ServiceInterfaceMgr\Utility\Src\SQOIUTypeVisitor.cpp, line 643.
                      </font>

                      It seems like, parameter used as simple attributes in a select-statement, are not supported. :(
                      • 8. Re: Using Parameters in SQL-Query not only in where clauses
                        AlexAnd
                        >
                        select
                        :pv_some_Text as Betr_Teil,
                        :pv_some_Text as Suva_Nr,
                        sum("GDWH05"."DM15K_RIS_FAKTEN_PRO_BTT_JHR"."RIS_VOLLBESCH") as Vollbesch
                        from "GDWH05"
                        where
                        "DM15D_BETRIEBSTEIL"."SUVA_NR_FORM" in ('122-4.4')
                        fetch first 65001 rows ONLY
                        >
                        look like that "GDWH05" is schema, so your from clause incorrect as and a query

                        if "GDWH05" is schema ;"DM15K_RIS_FAKTEN_PRO_BTT_JHR" and "DM15D_BETRIEBSTEIL" are tables then your query incorrect at all
                        you can not use one table in where clause, another in select and schema in from clause

                        so as idea
                        select
                        :pv_some_Text as Betr_Teil,
                        :pv_some_Text as Suva_Nr,
                        sum("RIS_VOLLBESCH") as Vollbesch
                        from "GDWH05"."DM15K_RIS_FAKTEN_PRO_BTT_JHR"
                        where 
                        exists ( select 1 from "DM15D_BETRIEBSTEIL" where "SUVA_NR_FORM" in ('122-4.4') )
                        --add
                        only now looked at syntax of your query more closly

                        Edited by: AlexAnd on Dec 10, 2012 1:02 AM
                        • 9. Re: Using Parameters in SQL-Query not only in where clauses
                          user11925394
                          Basically you're right.

                          But in this case, the datasource is a BI-Repository and all joins are made within this Repository.
                          So, "GDWH05" is the Subject Area, "DM15K_RIS_FAKTEN_PRO_BTT_JHR" and "DM15D_BETRIEBSTEIL" are Tables in it and "RIS_VOLLBESCH" and "SUVA_NR_FORM" are Table-Columns.
                          Therefore, the SQL-Syntax is a bit strange.

                          Please refer to this: http://docs.oracle.com/cd/E17904_01/bi.1111/e10540/sqlref.htm

                          FROM Clause Syntax
                          The Oracle BI Server accepts any valid SQL FROM clause syntax. To simplify FROM clause creation, you can specify the name of a subject area instead of a list of tables. The Oracle BI Server determines the proper tables and the proper join specifications based on the columns the request asks for and the configuration of the Oracle BI repository.

                          But, just to give a try, using this SQL:
                          select
                               :pv_some_Text as Betr_Teil,
                               :pv_some_Text as Suva_Nr,
                               sum("GDWH05"."DM15K_RIS_FAKTEN_PRO_BTT_JHR"."RIS_VOLLBESCH") as Vollbesch
                          from 
                               "GDWH05"."DM15D_BETRIEBSTEIL"
                               inner join "GDWH05"."DM15K_RIS_FAKTEN_PRO_BTT_JHR" on "GDWH05"."DM15D_BETRIEBSTEIL"."ID_BETRIEBSTEIL" = "GDWH05"."DM15K_RIS_FAKTEN_PRO_BTT_JHR"."ID_BETRIEBSTEIL"
                          where 
                               "GDWH05"."DM15D_BETRIEBSTEIL"."SUVA_NR_FORM" in ('122-4.4')
                          gives the same ERROR.

                          And this statement works fine:
                          select
                               'some_Text_a' as Betr_Teil,
                               'some_Text_b' as Suva_Nr,
                               sum("GDWH05"."DM15K_RIS_FAKTEN_PRO_BTT_JHR"."RIS_VOLLBESCH") as Vollbesch
                          from 
                               "GDWH05"."DM15D_BETRIEBSTEIL"
                               inner join "GDWH05"."DM15K_RIS_FAKTEN_PRO_BTT_JHR" on "GDWH05"."DM15D_BETRIEBSTEIL"."ID_BETRIEBSTEIL" = "GDWH05"."DM15K_RIS_FAKTEN_PRO_BTT_JHR"."ID_BETRIEBSTEIL"
                          where 
                               "GDWH05"."DM15D_BETRIEBSTEIL"."SUVA_NR_FORM" in ('122-4.4')
                          Thank you for your help
                          • 10. Re: Using Parameters in SQL-Query not only in where clauses
                            AlexAnd
                            what about
                            select
                                 ':pv_some_Text' as Betr_Teil,
                                 ':pv_some_Text' as Suva_Nr,
                                 sum("GDWH05"."DM15K_RIS_FAKTEN_PRO_BTT_JHR"."RIS_VOLLBESCH") as Vollbesch
                            from 
                                 "GDWH05"."DM15D_BETRIEBSTEIL"
                                 inner join "GDWH05"."DM15K_RIS_FAKTEN_PRO_BTT_JHR" on "GDWH05"."DM15D_BETRIEBSTEIL"."ID_BETRIEBSTEIL" = "GDWH05"."DM15K_RIS_FAKTEN_PRO_BTT_JHR"."ID_BETRIEBSTEIL"
                            where 
                                 "GDWH05"."DM15D_BETRIEBSTEIL"."SUVA_NR_FORM" in ('122-4.4')
                            colon and single quotes
                            change :pv_some_Text to  ':pv_some_Text'
                            or
                            post SR to support
                            • 11. Re: Using Parameters in SQL-Query not only in where clauses
                              user11925394
                              I tried this, but with single quotes and colon, it doesn't work either.
                              So, I have to find a different way to solve the problem.

                              Thank you for your help.