Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: MS SQL top N query in report designer

  1. #1
    Join Date
    Feb 2008
    Posts
    23

    Default MS SQL top N query in report designer

    Hi all,
    i'm trying to implement "N" as a parameter in query like this
    Code:
    select top N * from table 1
    so, my query looks like this
    Code:
    select top ${TopCount} * from table1
    But it doesn't work in report Designer 3.6.1, fails with error "Invalid syntax near '@p0'".
    From the other hand, the same parameter syntax looks fine in "where" clause

    Code:
    select * from table1 where col1 = ${parm}
    I guess problem appears when parameter preceded the metadata in the query, and this prevents PRD to parse the report correctly.

    Questions are following:
    1. Is this a bug or i failed somethere in configuration?
    2. If it's a bug, what can be a workaround?

    Any help will be appriciated, thanks.

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Prepared Parameter cannot replace structural content.

    However you can rewrite your query as something like:

    SELECT DISTINCT * FROM TABLE

    or so ..
    and then use the "query-limit" attribute along with a formula like =[TopCount] to limit the number or rows returned. The query-limit attribute has the same effect as a LIMIT clause on the query itself.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3
    Join Date
    Feb 2008
    Posts
    23

    Default

    Thanks a lot, Taqua, "query-limit" solved my trouble))
    BTW, trick with top N parameter works fine in xAction, at least in v3.0 platform))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.