This discussion is archived
3 Replies Latest reply: Mar 29, 2013 10:14 AM by Srini VEERAVALLI RSS

SQL Expression in Filter conditon Error in OBIEE 10g

Suman OTN Newbie
Currently Being Moderated
Hi

I am getting an error while giving the sql in the sql expression in filter conditon.

The sql expression I am giving is:SELECT MAX("Task Runs"."Start Time") saw_4 FROM "Analysis"'

Error:

Error Codes: YQCO4T56:OPR4ONWY:U9IM8TAC:OI2DL65P
Odbc driver returned an error (SQLExecDirectW).
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 27002] Near <SELECT>: Syntax error [nQSError: 26012] . (HY000)
SQL Issued: {call NQSGetLevelDrillability('set variable disable_cache_hit=1; SELECT "Task"."Task Name" saw_0, "Task Runs"."Time in Seconds" saw_1, TOPN("Task Runs"."Time in Seconds", 10) saw_2, "Task Runs"."Start Time" saw_3 FROM "Analysis" WHERE "Task Runs"."Start Time" >= SELECT MAX("Task Runs"."Start Time") saw_4 FROM "Analysis"')}

But when I individualy select start time column and check max of it the data comes fine, but if I am giving in sql expression it errors out.


Please suggest.
  • 1. Re: SQL Expression in Filter conditon Error in OBIEE 10g
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Try something as in with 2 queries
    http://www.cool-bi.com/Tweaks/JoinsinAnswers.php

    or else
    "Task Runs"."Start Time" -->Filter->Advanced button->Convert this in SQL->
    WHERE "Task Runs"."Start Time" >= MAX("Task Runs"."Start Time" by "Task"."Task Name")
    not sure how it works but this is other way of your sql in BI

    Edited by: Srini VEERAVALLI on Mar 29, 2013 8:47 AM
  • 2. Re: SQL Expression in Filter conditon Error in OBIEE 10g
    Suman OTN Newbie
    Currently Being Moderated
    Hi Sini,

    I have tried taking the second suggestion but I get no result for that

    "Task Runs"."Start Time" -->Filter->Advanced button->Convert this in SQL->
    WHERE "Task Runs"."Start Time" >= MAX("Task Runs"."Start Time" by "Task"."Task Name")

    Here I did not understand the code Task Runs"."Start Time" by "Task"."Task Name". If possible elaborate.

    And 1st suggestion the query formed just looks the query u suggested.

    Unable to figure out what exactly is the problem.


    Try something as in with 2 queries
    http://www.cool-bi.com/Tweaks/JoinsinAnswers.php

    or else
    "Task Runs"."Start Time" -->Filter->Advanced button->Convert this in SQL->
    WHERE "Task Runs"."Start Time" >= MAX("Task Runs"."Start Time" by "Task"."Task Name")
    not sure how it works but this is other way of your sql in BI
  • 3. Re: SQL Expression in Filter conditon Error in OBIEE 10g
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Change the below query as per your subject area and columns and test this with one task

    SELECT "Task Runs"."Task Name" saw_0,
    "Task Runs"."Start Time" saw_1,
    MAX("Task Runs"."Start Time" by "Task Runs"."Task Name") saw_2,
    "Task Runs"."Elapsed Time in Seconds" saw_3,
    TOPN("Task Runs"."Elapsed Time in Seconds",
    10) saw_4
    FROM "DAC Analysis" WHERE ("Task Runs"."Task Name" = 'SDE_Activity')
    AND ("Task Runs"."Start Time" <= MAX("Task Runs"."Start Time" by "Task Runs"."Task Name")) ORDER BY saw_1 DESC

    Note: In where should be <= I guess ;)

    If helps mark as correct :)

    MAX("Task Runs"."Start Time" by "Task Runs"."Task Name")

    Is like selec max(start time) from <table>
    group by Task Name

Legend

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