Pentaho Kettle Excel template formatting & SQL data retrieval

Question

I need to populate an excel template using pentaho kettle. I am Retrieving data from sql server & oracle DB. but my template is in row vise, like I have to retrieve name and ID columns from a table and put it into 2 rows. for that I am using 2 separate select sqls and UNION. I need to know is there any other simple way instead of using UNION because there are almost 200 rows and i need to use 200 select sqls, even though I am retrieving data from just 4 or 5 tables.

below is the sample select i have used,

SELECT EMP_COMMON_NAME "EmpDetail" FROM TBL_Emp WHERE emp_modified_Date = getdate() AND emp_id = '11' UNION ALL
SELECT EMP_UWI FROM TBL_Emp WHERE emp_modified_Date = getdate() AND emp_id = '11' UNION ALL
SELECT EMPBORE_NAME FROM TBL_EmpBORE WHERE emp_modified_Date = getdate() AND emp_id = '11' UNION ALL
SELECT TO_CHAR(EMPBORE_NO) FROM TBL_EmpBORE WHERE emp_modified_Date = getdate() AND emp_id = '11' UNION ALL
SELECT DESIGNATION FROM TBL_Emp WHERE emp_modified_Date = getdate() AND emp_id = '11' UNION ALL
SELECT TO_CHAR(GEO_LATITUDE) FROM TBL_Emp WHERE emp_modified_Date = getdate() AND emp_id = '11' UNION ALL
SELECT TO_CHAR(WATER_DEPTH) FROM TBL_Emp WHERE emp_modified_Date = getdate() AND emp_id = '11'

excel template output

should return single row alone. its a single employee detail — lourdh
Here each row has different column result right (Eg:- first row result has Emp Name, second row result has Emp UWI etc,. ). If sql returns column wise not row wise, In these criteria you have any problem? — Venkatesh Panabaka
If sql returns column wise data, i would not be able to place it into my template. so I am taking each column value in a separate sql and i want to know, is there any more options available instead using UNION — lourdh

Venkatesh Panabaka Venkatesh Panabaka · Accepted Answer · 2015-05-28T11:53:39

I think there is no solution to combines the results set of two or more select queries into a single result set.

Possible solution is UNION or UNION ALL.

UNION combines the results set of two or more select into single result-set which includes all the rows from all the queries int he union, where as JOINS, retrieve data from two or more tables based on logical relationships between the tables.

I think these information is useful to you.

Thank you.

Pentaho Kettle Excel template formatting & SQL data retrieval

1 Answers