*/
protected Map<String, String> distinctGenerateSql(
final SqlQuery outerSqlQuery,
boolean countOnly)
{
final Dialect dialect = outerSqlQuery.getDialect();
final Dialect.DatabaseProduct databaseProduct =
dialect.getDatabaseProduct();
final Map<String, String> groupingSetsAliases =
new HashMap<String, String>();
// Generate something like
//
// select d0, d1, count(m0)
// from (
// select distinct dim1.x as d0, dim2.y as d1, f.z as m0
// from f, dim1, dim2
// where dim1.k = f.k1
// and dim2.k = f.k2) as dummyname
// group by d0, d1
//
// or, if countOnly=true
//
// select count(m0)
// from (
// select distinct f.z as m0
// from f, dim1, dim2
// where dim1.k = f.k1
// and dim2.k = f.k2) as dummyname
final SqlQuery innerSqlQuery = newSqlQuery();
if (databaseProduct == Dialect.DatabaseProduct.GREENPLUM) {
innerSqlQuery.setDistinct(false);
} else {
innerSqlQuery.setDistinct(true);
}
// add constraining dimensions
RolapStar.Column[] columns = getColumns();
int arity = columns.length;
for (int i = 0; i < arity; i++) {
RolapStar.Column column = columns[i];
RolapStar.Table table = column.getTable();
if (table.isFunky()) {
// this is a funky dimension -- ignore for now
continue;
}
table.addToFrom(innerSqlQuery, false, true);
String expr = column.generateExprString(innerSqlQuery);
StarColumnPredicate predicate = getColumnPredicate(i);
final String where = RolapStar.Column.createInExpr(
expr,
predicate,
column.getDatatype(),
innerSqlQuery);
if (!where.equals("true")) {
innerSqlQuery.addWhere(where);
}
if (countOnly) {
continue;
}
String alias = "d" + i;
alias = innerSqlQuery.addSelect(expr, null, alias);
if (databaseProduct == Dialect.DatabaseProduct.GREENPLUM) {
innerSqlQuery.addGroupBy(expr, alias);
}
final String quotedAlias = dialect.quoteIdentifier(alias);
outerSqlQuery.addSelectGroupBy(quotedAlias, null);
// Add this alias to the map of grouping sets aliases
groupingSetsAliases.put(
expr,
dialect.quoteIdentifier(
"dummyname." + alias));
}
// add predicates not associated with columns
extraPredicates(innerSqlQuery);
// add measures
for (int i = 0, count = getMeasureCount(); i < count; i++) {
RolapStar.Measure measure = getMeasure(i);
Util.assertTrue(measure.getTable() == getStar().getFactTable());
measure.getTable().addToFrom(innerSqlQuery, false, true);
String alias = getMeasureAlias(i);
String expr = measure.generateExprString(outerSqlQuery);
innerSqlQuery.addSelect(
expr,
measure.getInternalType(),
alias);
if (databaseProduct == Dialect.DatabaseProduct.GREENPLUM) {
innerSqlQuery.addGroupBy(expr, alias);
}
outerSqlQuery.addSelect(
measure.getAggregator().getNonDistinctAggregator()
.getExpression(dialect.quoteIdentifier(alias)),
measure.getInternalType());
}
outerSqlQuery.addFrom(innerSqlQuery, "dummyname", true);
return groupingSetsAliases;
}