0

I have a table with a 'query' field containing a select sql and another 'parameters' field containing the sql parameters. I have merged these two fields into a new field containing a correct select sql statement. Now I need to execute this new field containing select sql, get the return from select (the output fields) and generate an excel file.

Carlos Lindberg
  • 23
  • 5

1 Answers1

1

Use Table-Input if you are interested in a query result set. Table-Input supports SQL parameters, so no need to build the statement yourself using e.g. Replace-In-String, and tripping over escapes on your way. Also, there's variable substitution, just in case you can't live with a single template.

Update 21:14 GMT

I'm not very fond of the way you try to prepare the SELECT statement, but here we go, assuming it's a single statement we have:

Create a job with a Start entry and 2 Transformation entries (T1, T2). Let T1 produce the field containing your SELECT statement and use a Set-Variables step to make the statement available to T2 as variable SELECT. In T2 use a Table-Input step referencing ${SELECT} in the SQL statement text area. Don't forget to enable option "Replace variables in script".

From now on it's a matter of taste. I would prefer to create a CSV file using Text-File-Output. Using the right field separator Excel will open the file after double-clicking it. The advantage of Text-File-Output is that you don't have to specify the fields you don't know at design-time anyway. An empty field list will just handle all fields coming in. Comparable to the total projection in a Table-Input which will create the necessary fields from the retrieved columns downstream.

If you must produce an Excel workbook, you'll have to learn about metadata injection. That would be a separate project for a beginner, though. There are samples in your Kettle installation folder. And there is a very active community if you find yourself in trouble.

marabu
  • 1,166
  • 7
  • 9
  • Marabu, my table has a field called 'query' containing 'select * from table', and another field called 'parameters' containing 'where cdclient = 12345'. I created a new field called 'Sql' containing the concatenation of the 'query' + 'parameters' fields (forming a Select Sql Statement). Now I want to execute this field 'Sql', get the return from select (the output fields) and generate an excel file. – Carlos Lindberg Jan 31 '17 at 17:44
  • What's your problem, then? How to convert a field into a Kettle variable, so you can use it with Table-Input? How to cope with a total projection and fields not known at design time? Or how to create an Excel Workbook from a Kettle rowset? Please, help me, I'm lost. – marabu Jan 31 '17 at 19:06
  • marabu, Sorry! Maybe it's not being clear enough, because I still do not know the Kettle very much, but I see that you understand me gradually. I have a Table input with a field with a Select Statement inside. How do I execute the Select Statement within the field, and get the result of select? I'm using Table input to get the table field and I have to use Excel Output to generate the excel file. I'm trying to use Execute row SQL script or Dynamic SQL row, but I'm not sure how to use it, and I also do not really know which component I should use. – Carlos Lindberg Jan 31 '17 at 19:42
  • @CarlosLindberg: If result of your query has static columns structure (always same number of columns, their order, names and types), then you can alternatively use `Dynamic SQL row` to execute SELECT statements coming from previous steps. However, if your queries may have different structure, then I don't see any ways other than reading the query into variable, then passing variable into another transformation as a parameter (via a job, as suggested by marabu, or using a Transformation Executor step). – Andrei Luksha Feb 02 '17 at 11:32
  • Hi marabu, can you give me some suggestion on question https://stackoverflow.com/questions/57306046/how-to-run-different-sql-to-get-data-according-to-the-previous-input-data-in-pen ? thanks! – Rollsbean Aug 01 '19 at 09:51