This discussion is archived
11 Replies Latest reply: Dec 11, 2012 7:22 AM by user11925394 RSS

Using Parameters in SQL-Query not only in where clauses

user11925394 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points