I m using the Database Value Lookup step on a View in MS SQL Server. The step returns incorrect values in certain records. If I reproduce the query in SQL, everything is fine. I m comparing 4 fields, 2 of which are date values, where I try to find a matching record. Also the results are never the same. After each run of the transformation, I get different results. If I put the content of the view in a static table, everything works perfect.
Are there any known issues with Database Value Lookup and Views? I have to use Pentaho 6.x still, because it is productive and we cant switch anytime soon.
Thanks!
The problem was an incomplete ORDER BY within a view. The SQL engine gave a different result after every run.