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 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());
}