public void test02_SelectFrom() throws Exception { KettleEnvironment.init(); String sqlQuery = "SELECT Category, Country, products_sold as nr, sales_amount as sales FROM Service"; SqlTransExecutor executor = new SqlTransExecutor(sqlQuery, getServices()); final List<RowMetaAndData> rows = new ArrayList<RowMetaAndData>(); // print the eventual result rows... // executor.executeQuery( new RowAdapter() { @Override public void rowWrittenEvent(RowMetaInterface rowMeta, Object[] row) throws KettleStepException { rows.add(new RowMetaAndData(rowMeta, row)); } }); // Now the generated transformation is waiting for input so we // can start the service transformation // executor.waitUntilFinished(); assertEquals(8, rows.size()); }
public void test06_SelectFromGroupByOrderBy() throws Exception { KettleEnvironment.init(); String sqlQuery = "SELECT Country, SUM(products_sold) as count, SUM(sales_amount) as sales FROM Service GROUP BY Country ORDER BY SUM(sales_amount) DESC"; SqlTransExecutor executor = new SqlTransExecutor(sqlQuery, getServices()); final List<RowMetaAndData> rows = new ArrayList<RowMetaAndData>(); // collect the eventual result rows... // executor.executeQuery( new RowAdapter() { @Override public void rowWrittenEvent(RowMetaInterface rowMeta, Object[] row) throws KettleStepException { rows.add(new RowMetaAndData(rowMeta, row)); } }); // Now the generated transformation is waiting for input so we // can start the service transformation // executor.waitUntilFinished(); assertEquals(4, rows.size()); // Validate results... // int rowNr = 0; assertEquals("Germany", rows.get(rowNr).getString("Country", null)); assertEquals(76, rows.get(rowNr).getInteger("count", -1)); assertEquals(12697, Math.round(rows.get(rowNr).getNumber("sales", -1.0))); rowNr++; assertEquals("Great Britain", rows.get(rowNr).getString("Country", null)); assertEquals(18, rows.get(rowNr).getInteger("count", -1)); assertEquals(11657, Math.round(rows.get(rowNr).getNumber("sales", -1.0))); rowNr++; assertEquals("France", rows.get(rowNr).getString("Country", null)); assertEquals(24, rows.get(rowNr).getInteger("count", -1)); assertEquals(2021, Math.round(rows.get(rowNr).getNumber("sales", -1.0))); rowNr++; assertEquals("Belgium", rows.get(rowNr).getString("Country", null)); assertEquals(10, rows.get(rowNr).getInteger("count", -1)); assertEquals(780, Math.round(rows.get(rowNr).getNumber("sales", -1.0))); }
public void test10_SelectFromGroupSumAlias() throws Exception { KettleEnvironment.init(); String sqlQuery = "SELECT Category, SUM(sales_amount) as \"Sales\"\nFROM Service\nGROUP BY Category\nORDER BY SUM(sales_amount) DESC"; SqlTransExecutor executor = new SqlTransExecutor(sqlQuery, getServices()); final List<RowMetaAndData> rows = new ArrayList<RowMetaAndData>(); // collect the eventual result rows... // executor.executeQuery( new RowAdapter() { @Override public void rowWrittenEvent(RowMetaInterface rowMeta, Object[] row) throws KettleStepException { rows.add(new RowMetaAndData(rowMeta, row)); } }); // Save to temp file for checking // File file = new File("/tmp/gen.ktr"); FileOutputStream fos = new FileOutputStream(file); fos.write(org.pentaho.di.core.xml.XMLHandler.getXMLHeader().getBytes("UTF-8")); fos.write(executor.getGenTransMeta().getXML().getBytes("UTF-8")); fos.close(); // Now the generated transformation is waiting for input so we // can start the service transformation // executor.waitUntilFinished(); assertEquals(2, rows.size()); // Validate results... // int rowNr = 0; assertEquals("A", rows.get(rowNr).getString("Category", null)); assertEquals(19013, Math.round(rows.get(rowNr).getNumber("Sales", -1.0))); rowNr++; assertEquals("B", rows.get(rowNr).getString("Category", null)); assertEquals(8142, Math.round(rows.get(rowNr).getNumber("Sales", -1.0))); }
public void test09_SelectFromGroupMaxHaving() throws Exception { KettleEnvironment.init(); String sqlQuery = "SELECT Country, MAX(products_sold) max_count FROM Service GROUP BY Country HAVING MAX(products_sold) > 10 ORDER BY max_count DESC"; SqlTransExecutor executor = new SqlTransExecutor(sqlQuery, getServices()); final List<RowMetaAndData> rows = new ArrayList<RowMetaAndData>(); // collect the eventual result rows... // executor.executeQuery( new RowAdapter() { @Override public void rowWrittenEvent(RowMetaInterface rowMeta, Object[] row) throws KettleStepException { rows.add(new RowMetaAndData(rowMeta, row)); } }); // Save to temp file for checking // File file = new File("/tmp/gen.ktr"); FileOutputStream fos = new FileOutputStream(file); fos.write(org.pentaho.di.core.xml.XMLHandler.getXMLHeader().getBytes("UTF-8")); fos.write(executor.getGenTransMeta().getXML().getBytes("UTF-8")); fos.close(); // Now the generated transformation is waiting for input so we // can start the service transformation // executor.waitUntilFinished(); assertEquals(2, rows.size()); // Validate results... // int rowNr = 0; assertEquals("Germany", rows.get(rowNr).getString("Country", null)); assertEquals(38, Math.round(rows.get(rowNr).getNumber("max_count", -1.0))); rowNr++; assertEquals("France", rows.get(rowNr).getString("Country", null)); assertEquals(12, Math.round(rows.get(rowNr).getNumber("max_count", -1.0))); }
public void test07_SelectFromGroupCountDistinct() throws Exception { KettleEnvironment.init(); String sqlQuery = "SELECT Category, COUNT(DISTINCT Country) as \"Number of countries\" FROM Service GROUP BY Category ORDER BY COUNT(DISTINCT Country) DESC"; SqlTransExecutor executor = new SqlTransExecutor(sqlQuery, getServices()); final List<RowMetaAndData> rows = new ArrayList<RowMetaAndData>(); // collect the eventual result rows... // executor.executeQuery( new RowAdapter() { @Override public void rowWrittenEvent(RowMetaInterface rowMeta, Object[] row) throws KettleStepException { rows.add(new RowMetaAndData(rowMeta, row)); } }); // Now the generated transformation is waiting for input so we // can start the service transformation // executor.waitUntilFinished(); assertEquals(2, rows.size()); // Validate results... // int rowNr = 0; assertEquals("A", rows.get(rowNr).getString("Category", null)); assertEquals(4, rows.get(rowNr).getInteger("Number of countries", -1)); rowNr++; assertEquals("B", rows.get(rowNr).getString("Category", null)); assertEquals(4, rows.get(rowNr).getInteger("Number of countries", -1)); }
public void test03_SelectFromWhere() throws Exception { KettleEnvironment.init(); String sqlQuery = "SELECT Category, Country, products_sold as nr, sales_amount as sales FROM Service WHERE Category = 'A'"; SqlTransExecutor executor = new SqlTransExecutor(sqlQuery, getServices()); final List<RowMetaAndData> rows = new ArrayList<RowMetaAndData>(); // print the eventual result rows... // executor.executeQuery( new RowAdapter() { @Override public void rowWrittenEvent(RowMetaInterface rowMeta, Object[] row) throws KettleStepException { rows.add(new RowMetaAndData(rowMeta, row)); } }); // Now the generated transformation is waiting for input so we // can start the service transformation // executor.waitUntilFinished(); // Save to temp file for checking // File file = new File("/tmp/gen.ktr"); FileOutputStream fos = new FileOutputStream(file); fos.write(org.pentaho.di.core.xml.XMLHandler.getXMLHeader().getBytes("UTF-8")); fos.write(executor.getGenTransMeta().getXML().getBytes("UTF-8")); fos.close(); assertEquals(4, rows.size()); }
public void test13_DistinctOrderFromPMD() throws Exception { KettleEnvironment.init(); String sqlQuery = "SELECT DISTINCT BT_SERVICE_SERVICE.Category AS COL0 ,BT_SERVICE_SERVICE.Country AS COL1 FROM Service BT_SERVICE_SERVICE ORDER BY COL0"; SqlTransExecutor executor = new SqlTransExecutor(sqlQuery, getServices()); final List<RowMetaAndData> rows = new ArrayList<RowMetaAndData>(); // collect the eventual result rows... // executor.executeQuery( new RowAdapter() { @Override public void rowWrittenEvent(RowMetaInterface rowMeta, Object[] row) throws KettleStepException { rows.add(new RowMetaAndData(rowMeta, row)); } }); // Save to temp file for checking // File file = new File("/tmp/gen.ktr"); FileOutputStream fos = new FileOutputStream(file); fos.write(org.pentaho.di.core.xml.XMLHandler.getXMLHeader().getBytes("UTF-8")); fos.write(executor.getGenTransMeta().getXML().getBytes("UTF-8")); fos.close(); // Now the generated transformation is waiting for input so we // can start the service transformation // executor.waitUntilFinished(); assertEquals(8, rows.size()); }
public void test11_NotSelectedHaving() throws Exception { KettleEnvironment.init(); String sqlQuery = "select \"Service\".\"Category\" as \"c0\", \"Service\".\"Country\" as \"c1\" from \"Service\" as \"Service\" where (\"Service\".\"Category\" = 'A') group by \"Service\".\"Category\", \"Service\".\"Country\" having (NOT((sum(\"Service\".\"sales_amount\") is null)) OR NOT((sum(\"Service\".\"products_sold\") is null)) ) order by CASE WHEN \"Service\".\"Category\" IS NULL THEN 1 ELSE 0 END, \"Service\".\"Category\" ASC, CASE WHEN \"Service\".\"Country\" IS NULL THEN 1 ELSE 0 END"; SqlTransExecutor executor = new SqlTransExecutor(sqlQuery, getServices()); final List<RowMetaAndData> rows = new ArrayList<RowMetaAndData>(); // collect the eventual result rows... // executor.executeQuery( new RowAdapter() { @Override public void rowWrittenEvent(RowMetaInterface rowMeta, Object[] row) throws KettleStepException { rows.add(new RowMetaAndData(rowMeta, row)); } }); // Save to temp file for checking // File file = new File("/tmp/gen.ktr"); FileOutputStream fos = new FileOutputStream(file); fos.write(org.pentaho.di.core.xml.XMLHandler.getXMLHeader().getBytes("UTF-8")); fos.write(executor.getGenTransMeta().getXML().getBytes("UTF-8")); fos.close(); // Now the generated transformation is waiting for input so we // can start the service transformation // executor.waitUntilFinished(); assertEquals(4, rows.size()); }