Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Parameter in order by for datasource

  1. #1

    Default Parameter in order by for datasource

    I am trying to pass a parameter to order by in mysql jndi query, as follows, but it didn't work. Any help is appreciated.

    select w.account_id,
    c.name,
    -w.payment_amount/100 as payment,
    w.mrc_amount/100 as mrc,
    w.amount_due/100 as due,
    w.mou
    from warehouse as w, dim_customer as c
    where substring(w.date_key,1,6)=${yearmonth}
    and w.account_id=c.account_id
    order by ${measure} desc limit 10;

    The yearmonth parameter is work fine, but measure for order by is not working. The measure will take {payment, mdc, due, mou} as I defined as values array for select component.

    Thanks a lot.

  2. #2
    Join Date
    Jul 2013
    Posts
    5

    Default

    Quote Originally Posted by brightlee6 View Post
    I am trying to pass a parameter to order by in mysql jndi query, as follows, but it didn't work. Any help is appreciated.

    select w.account_id,
    c.name,
    -w.payment_amount/100 as payment,
    w.mrc_amount/100 as mrc,
    w.amount_due/100 as due,
    w.mou
    from warehouse as w, dim_customer as c
    where substring(w.date_key,1,6)=${yearmonth}
    and w.account_id=c.account_id
    order by ${measure} desc limit 10;

    The yearmonth parameter is work fine, but measure for order by is not working. The measure will take {payment, mdc, due, mou} as I defined as values array for select component.

    Thanks a lot.
    When you use ${measure} in the query the value write in the query is 'payment', 'mdc', 'due' ou 'mou' and not payment, mdc, due or mou. Try to do other query.

  3. #3

    Default

    Thanks for your reply. Any direct way to solve the issue or just make it work as I expected. Thanks.

  4. #4
    Join Date
    Jul 2013
    Posts
    19

    Default

    I'm not 100% sure it will work, but you can try to write your ORDER BY as follows :

    ORDER BY CASE ${measure} WHEN 'payment' THEN payment WHEN 'mdc' THEN mdc WHEN 'due' THEN due ELSE w.mou END

    I wrote w.mou because you didn't rename it in the select part.
    Last edited by rempan; 08-02-2013 at 03:18 AM.

  5. #5

    Default

    In the order by clause you have to use a column which exists in your select statement.

    I think you can do something like that :

    Code:
    SELECT
        T.name, ..., T.mou
    FROM (
            SELECT
                measure, name, ..., mou
            FROM MYTABLE
            WHERE substring(w.date_key,1,6)=${yearmonth}
            ...
            AND measure in  (${measure})
            order by 1 DESC limit 10
    ) T

  6. #6

    Default

    Just had a chance to test on this and it worked. Thanks!!!

Tags for this Thread

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.