Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: user defined variable in mysql

  1. #1

    Default user defined variable in mysql

    look at the below SQL in mysql database. it is working in sqlplus. but didn't work in database join step in kettle. Can somebody shed some lights on this?
    do we have to do something in kettle?
    thanks in advance


    select B.ACCT,B.salary from (
    select
    @salary lag_salary,@salary:=sc.salary curr_salary,



    PH.ACCT, sc.salary from packet_header ph
    inner join salary_configuration sc
    on sc.salary=ph.salary
    where ph.status = "active" order by salary) b
    where b.lag_salary !=b.curr_salary

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by jdsignature View Post
    but didn't work in database join step in kettle.
    Didn't work as expected or threw an exception?
    So long, and thanks for all the fish.

  3. #3

    Default

    it didn't return any exception. I expected to work but it didn't from Kettle. the kettle just skip the database join step and move to next step. the above sql with embedded user defined variables worked as expected from any sql tools. i am wondering if we have to let kettle know by configuration: for instance the jdbc property file or spoon script.

  4. #4
    Join Date
    Apr 2008
    Posts
    4,683

    Default

    Quote Originally Posted by jdsignature View Post
    select B.ACCT,B.salary from (
    select
    @salary lag_salary,@salary:=sc.salary curr_salary, PH.ACCT, sc.salary from packet_header ph
    inner join salary_configuration sc
    on sc.salary=ph.salary where ph.status = "active" order by salary) b
    where b.lag_salary !=b.curr_salary
    Try it in a Table Input step rather than in a DB Join.

  5. #5
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Typically with Database-Join some theta join is implemented via SQL parameters unless a cartesian product is what you need.
    Also, reading and writing a variable (@salary) in the same SELECT statement is non-deterministic.
    I would check the step metrics, if the right numbers show up.
    And finally, I would look at the SQL statement showing up in the server log to be sure the assignment operator survived JDBC.
    So long, and thanks for all the fish.

  6. #6

    Default

    tried using the table step as well. it didn't work either. no exception/errors returned.

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.