Especially if you need to manage the mapping over time (and you want to keep track of the histoy of your mapping practice) you could start to explictly store the mappings in your staging arera in a lookup table, and use the lookup step to do it. If the mapping is actually stored in a database table, you should use a database lookup step. I should point out that the value mapper is the way to go if you "just" want to map a few values.
It is first discussed in the book on page 273, "The Value Mapper Step". You can find the value mapper in the "Transform" category. In this particular case, you would have the following source/target pairs. The value mapper operates on a field in the input stream and then looks if it matches one of the listed "source" values and then returns the mapped "target" value if there is a match. In pentaho data integration, I would most probably use a "Value Mapper" step for this case.
Best practice is to make all transformations to the data as explicit as possible, and writing SQL expressions hides the logic. Regarding your scenario: Although you could solve it in the SQL statement in the input step using a CASE.WHEN expression, I would advise against that. Thanks for your interest and support! I always enjoy hearing the book is useful to someone. This would in almost all cases eliminate the need for explicit temporary tables and outer joins at all I realize It might not always be possible and some customers are truly scared by the term "Data Warehouse" but really, I am feeling more and more inclined to advise against one-by-one copies of the source systems as "reporting environments" in favor of a proper, star schema - based data warehouse. My gut feeling says that it would probably be better to set up an environment that is designed to cater to queries, and that uses some after hours to pre-process things so you know you can always efficiently get the answers whenever you need them. That said, what you are describing sounds like a pretty complex thing to do, just to run a report. Maybe you can checkout Pentaho Reporting in that case (aka JFreeReport) - Like I said I think they added it. Hi Shlomi! Don't be ashamed - maybe gigs, many tools - you can't devote everything to memory.
#MULTIPLE SELECTS IN STORED PROCEDURE MYSQL JDBC FREE#
Please feel free to continue this discussion, and maybe some of the PDI developers will chime on to provide their point of view. That said, for a data integration tool like pdi, I do not think it is terribly important to be able to capture SP result sets, more of a nice to have thing. I think that there is no technical reason not to support it - the JDBC API allows for resultsets to be returned from prepared statements, so if the Call DB Procedure uses that it should be able to capture a result set (or even multiple, although that would open a whole can of worms on its own). I can only guess but I think the answer is that in case of the "Call DB Procedure", the design considerations were simply to allow a pdi input stream to drive stored procedure execution and to obtain the return value, and possibly OUT parameter values - no more no less. This leaves the question: "Why doesn't the existing Call DB Procedure step support this", or "Why isn't there a step especially for getting result sets out of stored procedures (or table functions for that matter)".
The only thing that is odd here is the name of the step, "Table input", because it does not literally read rows from a table, but an SQL statement in general. Note that in the case of pentaho data integration, it is in fact supported to call the stored procedure directly by explicitly typing the CALL statement in the "Table input" step. I do know that, at least in the MySQL case, the application must use the prepared statement protocol when executing the procedure, else you won't be able to capture the result set. Hi Shlomi! "Do you know why calling stored procedures directly is not supported?" In case of your reporting application - I can only guess.What product did you experience this with? I think this functionality was recently added to Pentaho Reporting (JFreeReport).