3 Replies Latest reply: Mar 29, 2013 12:14 PM by Srini VEERAVALLI RSS

    SQL Expression in Filter conditon Error in OBIEE 10g

    Suman OTN
      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
          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
            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
              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