Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2010
    Posts
    6

    Unanswered: Need to have where clause in textual query editor before the Group By Clause.

    Hi Actuate Folks,
    I need to have a where clause before the group by statement in the obtain select statement of textual query editor.

    In my Obtain Select Statement I’m creating where clause as follows:

    If Not IsNull(NewReportApp:aram_program_id) And NewReportApp:aram_program_id <> "" Then
    selectStatement = selectStatement & " and a.program_id = '" & NewReportApp:aram_program_id & "'"
    End If

    But when I run the report it’s complaining that where clause is coming after group by clause and query looks
    Like as follows from ShowFactoryStatement and it errors out:


    where a.project_id = b.project_id(+)

    group by a.release, a.release_commit,a.project_id, a.project_code, a.project_name, a.task_name, a.task_start_date, a.task_end_date
    ,a.charge_code, a.workGroup_level1, a.workGroup_level2, a.discipline, a.prprimaryroleid, a.resource_name,a.program_name,a.program_id,a.plan ningyear,a.workcity,a.IDCFlag and a.program_id= ‘25900’

    Please advice how to modify this to have where clause before the group by clasue,like as below:

    where a.project_id = b.project_id(+) and a.program_id= ‘25900’



    Please advice and reply at your earliest convenience.

    Thanks and Best Regards,
    -Ram Jois

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    this is just plain unreadable.
    perhaps you could format and also post the final sql statement that is failing with the actual error.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Ram Jois,
    Did you know that there's an Actuate Forum? You have to have be a paying customer of Actuate to use it, but it's probably a better place for Actuate specific questions:

    Actuate Customer Forums - Powered by vBulletin

    You can get suthorization to have an account by contacting support@actuate.com

    This forum is a place to ask questions about why your SQL stmt doesn't work, once you've got the Actuate stuff figured out.

    To the answer *****************************************
    In the Textual Query Editor, put something bogus in the WHERE clause, like

    Code:
    SELECT ...
    FROM ...
    WHERE OSS1
    GROUP BY ...
    We use "OSS" to identify that we're going to be replacing that string in the ObtainSelectStatement( ), so a followup developer knows what's going on. The "1" in "OSS1" indicates that there could be several strings in the SQL that might be getting replaced.

    Then, in the ObtainSelectStatement( ), use VB to do a string replacement. If you don't need to replace it with anything, replace it with at least "1=1".

    --=Chuck
    Last edited by chuck_forbes; 02-26-10 at 13:13.

  4. #4
    Join Date
    Feb 2010
    Posts
    6

    Thanks Chuck I was able to solve the issue.

    Thanks Chuck I was able to solve the issue.

    Thanks and Best Regards,
    -Ram Jois

  5. #5
    Join Date
    Feb 2010
    Posts
    6

    Still having some other issue.

    Hi Chuck,

    when I created a static parameter param_program_id along with obtainselectstatement where clasues the
    actuate is not returning any output if I do not supply any value for param_program_id :

    And my static parameter syntax is as follows:

    where a.project_id = b.project_id(+) and a.program_id = aram_program_id
    group by a.release, a.release_commit,a.project_id, a.project_code, a.project_name, a.task_name, a.task_start_date, a.task_end_date
    ,a.charge_code, a.workGroup_level1, a.workGroup_level2, a.discipline, a.prprimaryroleid, a.resource_name,a.program_name,a.program_id,a.plan ningyear,a.workcity,a.IDCFlag

    But I do have other parameters described in ObtainSelectStatement, so when I run for a particular param_program_id program id it’s returning out put.

    But when I leave param_program_id blank and run for all parameters9Other parameters in Obainselectstatement as where clauses) it’s not returning any out put at all.

    Please advice how to resolve and get out put for all conditions eventhough I do not enter value for static parameter param_program_id.

    Please advice.

    Thanks and Best Regards,
    -Ramprasad Jois

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    Don't use a static parameter, use an Adhoc parameter

    Code:
    where a.project_id = b.project_id(+) and a.program_id = :param_program_id
    becomes
    Code:
    where a.project_id = b.project_id(+) and :?param_program_id
    You have to do some additional work to map an adhoc variable to an actual field (at the bottom of the Textual Query Editor on the Adhoc tab there). But, with an adhoc, Actuate will replace the "{colon}{question mark}param_program_id" with "1=1" at runtime if you don't pass in a value.

    So ... adhoc variable syntax in the Textual Query Editor is strange at first. The way that I learned it, was to just start another disposable report, and write some SQL using the Graphical Query Editor. Make sure you add an Adhoc parm. Then, in the menu, go to SQL>Edit SQL, to convert that to a Textual Query Editor, and see how Actuate converts your Adhoc variable in the Graphical Editor, to an Adhoc in the Textual.

    --=cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •