Java dynamically builds SQL

Question

Java dynamically builds SQL

RVO

SSCertifiable

Points: 5495
More actions
March 28, 2013 at 12:32 pm

#270480

I was assigned to convert Java code with embedded SQL to stored procedures.
The plan is to remodel database (tables and relationships) and before we can even start it we need to build an interface (stored procedures).
In Java it is really dynamic. See below.
My first attempt was to mimic and what they do in Java do it in T-SQL.
But the more I write SQL (it has lots of dynamic SQL and conditions) the more I don't like the whole solution
an d this approach.
T-SQL looks very messy, hard to understand.
/**** Business behind ****/
It generates SQL for reports that represent traders commissions and comparison of their Commission totals between different years.
/*********************/
I am just wondering if there is a better approcah to solve this problem.
Looks like trying to implement all this logic inside SQL is a crazy idea but what are my choices?
If I come up with this conclusion my manager would ask what can suggest and I dodn't know.
Maybe I should talk to Java Team Lead and see what they say...
This is all Java code.
getSelectClause
private String getSelectClause() {
String excludeRRCodeQuery = " AND list.rr_code " + Settings.instance().getExcludeRRCodeQuery();
String excludeRRCodeQueryRank = " AND listRank.rr_code " + Settings.instance().getExcludeRRCodeQuery();
String tdsiRRCodeQuery = " AND " + Settings.instance().getTDSIRRCodeQuery("list.rr_code");
String tdsiRRCodeQueryRank = " AND " + Settings.instance().getTDSIRRCodeQuery("listRank.rr_code");
String sql = " SELECT list.rr_code, list.client_comp_name, list.salesp_name, list.trader_name, list.group_code, list.group_name, list.type_code, soft_dollar_rate = (list.soft_dollar_rate * 100), list.locat_code, ";
String grossNetMultiplyer = "";
String grossNetMultiRank = "";
if(getGrossNetType() == NET_TYPE) {
grossNetMultiplyer = " * list.soft_dollar_rate ";
grossNetMultiRank = " * listRank.soft_dollar_rate ";
}
else {
// no need to multiply (* 1)
}
// create SQL for each selected available year column
for(int i=getSelAvailYears().length-1; i >= 0; i--) {
if(getSelAvailYears().length() > YEAR_LENGTH) { // Pro-Rated Year is selected
//for(int i=0; i < getSelAvailYears().length; i++) { // Years in Desc Order
//if(i == 0 && getSelAvailYears().length() > YEAR_LENGTH) { // Pro-Rated Year is selected
int curProYear = Integer.parseInt(getSelAvailYears().substring(0, YEAR_LENGTH));
if(getYearType() == FISC_YEAR) {
if(isIncNewIssues()) {
// add Comm column
if(!isExclMontlyBreakdown()){
sql += getMonthSelectClause(curProYear, FISC_YEAR, grossNetMultiplyer);
}
sql += "'" + getSelAvailYears() + COMM_STR + "' = ISNULL((SELECT SUM(comm) * " + getProRatedMultiplyer() + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 0 AND year_number = " + (curProYear - 1) + " AND month_number BETWEEN 11 AND 12), 0.0) + " +
" ISNULL((SELECT SUM(comm) * " + getProRatedMultiplyer() + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 0 AND year_number = " + curProYear + " AND month_number BETWEEN 1 AND 10), 0.0), ";
monthYearHdrs.add(FISC_STR + getSelAvailYears() + COMM_STR);
// add NI column: Do Not Pro-Rate
sql += "'" + getSelAvailYears() + NI_STR + "' = ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 1 AND year_number = " + (curProYear - 1) + " AND month_number BETWEEN 11 AND 12), 0.0) + " +
" ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 1 AND year_number = " + curProYear + " AND month_number BETWEEN 1 AND 10), 0.0), ";
monthYearHdrs.add(FISC_STR + getSelAvailYears() + NI_STR);
// add Total column
sql += "'" + getSelAvailYears() + "' = (ISNULL((SELECT SUM(comm) * " + getProRatedMultiplyer() + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 0 AND year_number = " + (curProYear - 1) + " AND month_number BETWEEN 11 AND 12), 0.0) + " +
" ISNULL((SELECT SUM(comm) * " + getProRatedMultiplyer() + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 0 AND year_number = " + curProYear + " AND month_number BETWEEN 1 AND 10), 0.0) + " +
" ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 1 AND year_number = " + (curProYear - 1) + " AND month_number BETWEEN 11 AND 12), 0.0) + " +
" ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 1 AND year_number = " + curProYear + " AND month_number BETWEEN 1 AND 10), 0.0)), ";
monthYearHdrs.add(FISC_STR + getSelAvailYears() + TOTAL_STR);
}
else {
// add Comm column
if(!isExclMontlyBreakdown()){
sql += getMonthSelectClause(curProYear, FISC_YEAR, grossNetMultiplyer);
}
sql += "'" + getSelAvailYears() + "' = ISNULL((SELECT SUM(comm) * " + getProRatedMultiplyer() + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 0 AND year_number = " + (curProYear - 1) + " AND month_number BETWEEN 11 AND 12), 0.0) + " +
" ISNULL((SELECT SUM(comm) * " + getProRatedMultiplyer() + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 0 AND year_number = " + curProYear + " AND month_number BETWEEN 1 AND 10), 0.0), ";
monthYearHdrs.add(FISC_STR + getSelAvailYears() + COMM_STR);
}
}
else {
if(isIncNewIssues()) {
// add Comm column
if(!isExclMontlyBreakdown()){
sql += getMonthSelectClause(curProYear, CAL_YEAR, grossNetMultiplyer);
}
sql += "'" + getSelAvailYears() + COMM_STR + "' = ISNULL((SELECT SUM(comm) * " + getProRatedMultiplyer() + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 0 AND year_number = " + curProYear + " AND month_number BETWEEN 1 and 12), 0.0), ";
monthYearHdrs.add(CAL_STR + getSelAvailYears() + COMM_STR);
// add NI column: Do Not Pro-Rate
sql += "'" + getSelAvailYears() + NI_STR + "' = ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 1 AND year_number = " + curProYear + " AND month_number BETWEEN 1 and 12), 0.0), ";
monthYearHdrs.add(CAL_STR + getSelAvailYears() + NI_STR);
// add Total column
sql += "'" + getSelAvailYears() + "' = (ISNULL((SELECT SUM(comm) * " + getProRatedMultiplyer() + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 0 AND year_number = " + curProYear + " AND month_number BETWEEN 1 and 12), 0.0) + " +
" ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 1 AND year_number = " + curProYear + " AND month_number BETWEEN 1 and 12), 0.0)), ";
monthYearHdrs.add(CAL_STR + getSelAvailYears() + TOTAL_STR);
}
else {
// add Comm column
if(!isExclMontlyBreakdown()){
sql += getMonthSelectClause(curProYear, CAL_YEAR, grossNetMultiplyer);
}
sql += "'" + getSelAvailYears() + "' = ISNULL((SELECT SUM(comm) * " + getProRatedMultiplyer() + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 0 AND year_number = " + curProYear + " AND month_number BETWEEN 1 and 12), 0.0), ";
monthYearHdrs.add(CAL_STR + getSelAvailYears() + COMM_STR);
}
}
}
else {
int curYear = Integer.parseInt(getSelAvailYears().substring(0, YEAR_LENGTH));
if(getYearType() == FISC_YEAR) {
if(isIncNewIssues()) {
// add Comm column
if(!isExclMontlyBreakdown()){
sql += getMonthSelectClause(curYear, FISC_YEAR, grossNetMultiplyer);
}
sql += "'" + getSelAvailYears() + COMM_STR + "' = ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 0 AND year_number = " + (curYear - 1) + " AND month_number BETWEEN 11 AND 12), 0.0) + " +
" ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 0 AND year_number = " + curYear + " AND month_number BETWEEN 1 AND 10), 0.0), ";
monthYearHdrs.add(FISC_STR + getSelAvailYears() + COMM_STR);
// add NI column
sql += "'" + getSelAvailYears() + NI_STR + "' = ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 1 AND year_number = " + (curYear - 1) + " AND month_number BETWEEN 11 AND 12), 0.0) + " +
" ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 1 AND year_number = " + curYear + " AND month_number BETWEEN 1 AND 10), 0.0), ";
monthYearHdrs.add(FISC_STR + getSelAvailYears() + NI_STR);
// add Total column
sql += "'" + getSelAvailYears() + "' = ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND year_number = " + (curYear - 1) + " AND month_number BETWEEN 11 AND 12), 0.0) + " +
" ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND year_number = " + curYear + " AND month_number BETWEEN 1 AND 10), 0.0), ";
monthYearHdrs.add(FISC_STR + getSelAvailYears() + TOTAL_STR);
}
else {
// add Comm column
if(!isExclMontlyBreakdown()){
sql += getMonthSelectClause(curYear, FISC_YEAR, grossNetMultiplyer);
}
sql += "'" + getSelAvailYears() + "' = ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 0 AND year_number = " + (curYear - 1) + " AND month_number BETWEEN 11 AND 12), 0.0) + " +
" ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 0 AND year_number = " + curYear + " AND month_number BETWEEN 1 AND 10), 0.0), ";
monthYearHdrs.add(FISC_STR + getSelAvailYears() + COMM_STR);
}
}
else {
if(isIncNewIssues()) {
// add Comm column
if(!isExclMontlyBreakdown()){
sql += getMonthSelectClause(curYear, CAL_YEAR, grossNetMultiplyer);
}
sql += "'" + getSelAvailYears() + COMM_STR + "' = ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 0 AND year_number = " + curYear + " AND month_number BETWEEN 1 and 12), 0.0), ";
monthYearHdrs.add(CAL_STR + getSelAvailYears() + COMM_STR);
// add NI column
sql += "'" + getSelAvailYears() + NI_STR + "' = ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 1 AND year_number = " + curYear + " AND month_number BETWEEN 1 and 12), 0.0), ";
monthYearHdrs.add(CAL_STR + getSelAvailYears() + NI_STR);
// add NI column
sql += "'" + getSelAvailYears() + "' = ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND year_number = " + curYear + " AND month_number BETWEEN 1 and 12), 0.0), ";
monthYearHdrs.add(CAL_STR + getSelAvailYears() + TOTAL_STR);
}
else {
// add Comm column
if(!isExclMontlyBreakdown()){
sql += getMonthSelectClause(curYear, CAL_YEAR, grossNetMultiplyer);
}
sql += "'" + getSelAvailYears() + "' = ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 0 AND year_number = " + curYear + " AND month_number BETWEEN 1 and 12), 0.0), ";
monthYearHdrs.add(CAL_STR + getSelAvailYears() + COMM_STR);
}
}
}
}
// New Issue flag clause
String niFlagClause = "";
if(!isIncNewIssues()) {
niFlagClause = " AND listRank.ni_flag = 0 ";
}
// create clause for the selected Rank Year - ONE SELECTION ONLY
//int rankYear = Integer.parseInt(getSelRankYears()[0].substring(0, YEAR_LENGTH));
//String rankTotal = "";
String tmpRankTotal = "";
String rankTotal = "";
String secRankTotal = "";
for(int i=getSelAvailYears().length-1; i >= 0; i--) {
int rankYear = Integer.parseInt(getSelAvailYears().substring(0, YEAR_LENGTH));
if(getYearType() == FISC_YEAR) {
tmpRankTotal =
" rank_total_" + i + " = ISNULL((SELECT SUM(listRank.comm " + grossNetMultiRank + ") FROM " + getTableName() + " listRank " +
" WHERE listRank.group_name = list.group_name " + niFlagClause + " AND listRank.year_number = " + (rankYear - 1) + " AND listRank.month_number BETWEEN 11 AND 12 " +
getTypeInClause("listRank.type_code") +
getLocatInClause("listRank.locat_code") +
excludeRRCodeQueryRank + tdsiRRCodeQueryRank + "), 0.0) + " +
" ISNULL((SELECT SUM(listRank.comm " + grossNetMultiRank + ") FROM " + getTableName() + " listRank " +
" WHERE listRank.group_name = list.group_name " + niFlagClause + " AND listRank.year_number = " + rankYear + " AND listRank.month_number BETWEEN 1 AND 10 " +
getTypeInClause("listRank.type_code") +
getLocatInClause("listRank.locat_code") +
excludeRRCodeQueryRank + tdsiRRCodeQueryRank + "), 0.0) ";
}
else {
tmpRankTotal =
" rank_total_" + i + " = ISNULL((SELECT SUM(listRank.comm " + grossNetMultiRank + ") FROM " + getTableName() + " listRank " +
" WHERE listRank.group_name = list.group_name " + niFlagClause + " AND listRank.year_number = " + rankYear + " AND listRank.month_number BETWEEN 1 AND 12 " +
getTypeInClause("listRank.type_code") +
getLocatInClause("listRank.locat_code") +
excludeRRCodeQueryRank + tdsiRRCodeQueryRank + "), 0.0) ";
}
tmpRankTotal += ",";
if(!getSelRankYears()[0].equalsIgnoreCase(getSelAvailYears())){
secRankTotal += tmpRankTotal;
tmpRankTotal = "";
}
else{
rankTotal = tmpRankTotal;
}
}
// whole SQL statement
sql +=
rankTotal +
secRankTotal;
/*if(sql.endsWith(",")){
sql = sql.substring(0, sql.length() - 1);
}*/
sql += " list.salesp_code, list.trader_code " +
" FROM " + getTableName() + " list " +
" WHERE (group_name IS NOT NULL OR group_name <> '') " + // need this, there're number of invalid RR Codes exist in coinx_trade
getTypeInClause("list.type_code") +
getLocatInClause("list.locat_code") +
//" AND list.ni_flag = 1 " +
excludeRRCodeQuery +
tdsiRRCodeQuery +
" GROUP BY list.rr_code, list.client_comp_name, list.salesp_code, list.salesp_name, list.trader_code, list.trader_name, list.group_code, list.group_name, list.type_code, soft_dollar_rate, list.locat_code " +
" ORDER BY ";
String rankOrderSql = "";
String selYearOrderSql = "";
for(int i=0; i < getSelAvailYears().length; i++) {
if(getSelRankYears()[0].equalsIgnoreCase(getSelAvailYears())){
//sql +=" rank_total_" + i + " DESC, '" + getSelRankYears()[0] + "' DESC, ";
sql += "rank_total_" + i + " DESC, list.group_name, '" + getSelRankYears()[0] + "' DESC, list.rr_code, list.trader_name, list.salesp_name ";
}
else{
rankOrderSql +=" rank_total_" + i + " DESC, ";
selYearOrderSql += "'" + getSelAvailYears() + "' DESC, ";
}
}
//sql += rankOrderSql + selYearOrderSql + " list.rr_code, list.group_name, list.trader_name, list.salesp_name ";
//System.out.println(sql);
//sql += "rank_total_0 DESC, list.group_name, '" + getSelRankYears()[0] + "' DESC, list.rr_code, list.trader_name, list.salesp_name ";
//System.out.println(sql);
return sql;
}
getMonthSelectClause (called from getSelectClause)
private String getMonthSelectClause(int yearNumber, int yearType, String grossNetMultiplyer) {
String sql = "";
for(int i=0; i < MONTH_NUM.length; i++) {
if(getYearType() == FISC_YEAR) {
// November or December
if(i==0 || i==1) {
sql += "'" + MONTH_HDR + " " + (yearNumber - 1) + "' = ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 0 AND year_number = " + (yearNumber - 1) + " AND month_number = " + MONTH_NUM + "), 0.0), ";
monthYearHdrs.add(MONTH_HDR + " " + (yearNumber - 1));
if(isIncNewIssues()) {
// add NI column
sql += "'" + MONTH_HDR + " " + (yearNumber - 1) + "' = ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 1 AND year_number = " + (yearNumber - 1) + " AND month_number = " + MONTH_NUM + "), 0.0), ";
monthYearHdrs.add(MONTH_HDR + " " + (yearNumber - 1));
// add Total column
sql += "'" + MONTH_HDR + " " + (yearNumber - 1) + "' = ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND year_number = " + (yearNumber - 1) + " AND month_number = " + MONTH_NUM + "), 0.0), ";
monthYearHdrs.add(MONTH_HDR + " " + (yearNumber - 1));
}
}
// all the other months
else {
sql += "'" + MONTH_HDR + " " + yearNumber + "' = ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 0 AND year_number = " + yearNumber + " AND month_number = " + MONTH_NUM + "), 0.0), ";
monthYearHdrs.add(MONTH_HDR + " " + yearNumber);
if(isIncNewIssues()) {
// add NI column
sql += "'" + MONTH_HDR + " " + yearNumber + "' = ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 1 AND year_number = " + yearNumber + " AND month_number = " + MONTH_NUM + "), 0.0), ";
monthYearHdrs.add(MONTH_HDR + " " + yearNumber);
// add Total column
sql += "'" + MONTH_HDR + " " + yearNumber + "' = ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND year_number = " + yearNumber + " AND month_number = " + MONTH_NUM + "), 0.0), ";
monthYearHdrs.add(MONTH_HDR + " " + yearNumber);
}
}
}
else {
sql += "'" + MONTH_HDR + " " + yearNumber + "' = ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 0 AND year_number = " + yearNumber + " AND month_number = " + MONTH_NUM + "), 0.0), ";
monthYearHdrs.add(MONTH_HDR + " " + yearNumber);
if(isIncNewIssues()) {
// add NI column
sql += "'" + MONTH_HDR + " " + yearNumber + "' = ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND ni_flag = 1 AND year_number = " + yearNumber + " AND month_number = " + MONTH_NUM + "), 0.0), ";
monthYearHdrs.add(MONTH_HDR + " " + yearNumber);
// add Total column
sql += "'" + MONTH_HDR + " " + yearNumber + "' = ISNULL((SELECT SUM(comm) " + grossNetMultiplyer + " FROM " + getTableName() + " WHERE rr_code = list.rr_code AND group_name = list.group_name AND salesp_code = list.salesp_code AND trader_code = list.trader_code AND year_number = " + yearNumber + " AND month_number = " + MONTH_NUM + "), 0.0), ";
monthYearHdrs.add(MONTH_HDR + " " + yearNumber);
}
}
}
return sql;
}
Thanks,
Rob

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

You can certainly come up with something that will not be so ugly. However it is not possible for anybody to offer much more than vague ideas at this point because you haven't provided very much in the way of details.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

RVO SSCertifiable Points: 5495 More actions · Answer 2

Thanks for response Sean.

I included all Java code.

All the logic is there.

I don't know what else I can provide.

I know it's hard to read Java code.

Basically here is how it works.

-------------------------------

There is a web page where user selects a range of years (2000 to 2013),

Rank years, Comparison years, Business Line, Location (LONDON,HONG KONG,etc.),

YEAR TYPE (Fiscal,Calendar), Gross/Net,

include Monthly Breakdown or not.

The final report shows Rank, Totals for Commission by "month-year", business.

User can drill-down and see details for a specific client.

result set:

rr_codeclient_comp_namesalesp_nametrader_namegroup_codegroup_nametype_codesoft_dollar_ratelocat_codeNov-12Dec-12

YGRPRBIM Wiggan, Tim Stratis, George YGGRRBIM AGNY100TOR 282297.1192076.63

CAFLRBIM OPTIONS S.F. Sales S.F. Trader YGGRRBIM SF 100TOR 7350032250----------------------------------------------

When I debug java code and generate SQL and execute that SQL

I get about 9,000 records.

Please give me an idea what else I can add.

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

Actually I can read java without much effort.

Honestly I don't think converting this to straight t-sql is feasible in an online forum.

There are least a dozen methods that have not been posted.

Settings.instance()getExcludeRRCodeQuery(); --I am guessing this is a singleton

Settings.instance().getTDSIRRCodeQuery("list.rr_code"); --I am guessing this is a singleton

getGrossNetType()

NET_TYPE --this looks like an enum?!?!?

getSelAvailYears()

getYearType()

FISC_YEAR --this looks like an enum?!?!?

isIncNewIssues()

isExclMontlyBreakdown()

monthYearHdrs

getSelRankYears()

You have the distinct advantage that you can see the database, you can see all of the code, you can debug it, you have some sort of idea of the business rules and desired result. I have none of those things.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323504 More actions · Answer 4

i would consider looking at whatever query the current code returns, and build a view of THAT, and fix the code to pass parameters for the WHERE clause;

there's a lot of unnecessary looping, like where you want all the years that getSelAvailYears() returns;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RVO SSCertifiable Points: 5495 More actions · Answer 5

Thanks Lowell !

How can I do it without looping through years?

They dynamically generate column names ('Nov 2012', 'Dec 2012')

in the loop.

'Nov 2012' = Isnull((SELECT Sum(comm)

FROM xxxx_ni_monthly_comm_booking

WHERE ni_flag = 0 AND year_number = 2012 AND month_number = 11

RVO SSCertifiable Points: 5495 More actions · Answer 6

Problem solved...

This was the most complicated matter I ever dealt with.

The solution is ...

Forget about this ugly complicated Java code that builds SQL dynamically in multiple loops.

This is a very bad idea.

I just worked with the front end, generated reports using different combinations

and found out what data they need.

The best trick that helped me is STUFF() and dynamic columns.

USE COIN

IF OBJECT_ID(N'tempdb..#tblComm', N'U') IS NOT NULL

DROP table #tblComm

GO

IF OBJECT_ID(N'tempdb..#tblRank', N'U') IS NOT NULL

DROP table #tblRank

GO

DECLARE @excludeRRCodes varchar(8000)

DECLARE @includeRRCodes varchar(8000)

DECLARE @yearMonths varchar(8000)

SET @excludeRRCodes = 'CSMK, YGKK, YBZZ'

SET @includeRRCodes = 'CA ,CS ,DC ,YG ,YH ,YT ,YW ,11'

--SET @yearMonths = '2011-1,2011-2,2011-3,2011-4,2011-5,2011-6,2011-7,2011-8,2011-9,2011-10,2011-11,2011-12'

--SET @yearMonths = @yearMonths+'2012-1,2012-2,2012-3,2012-4,2012-5,2012-6,2012-7,2012-8,2012-9,2012-10,2012-11,2012-12'

--SET @yearMonths = @yearMonths+'2013-1,2013-2,2013-3,2013-4'

SET @yearMonths = '2012-01,2012-02,2012-03,2012-04,2012-05,2012-06,2012-07,2012-08,2012-09,2012-10,2012-11,2012-12'

-----------------------------------------------------

CREATE TABLE #tblComm

(

group_namevarchar(40)

, type_codevarchar(4)

, commfloat

, soft_dollar_ratefloat

, year_numberint

, month_numberint

, rank_total_GROSSfloat

, rank_total_NET float

)

INSERT INTO #tblComm (

group_name

, type_code

, comm

, soft_dollar_rate

, year_number

, month_number

)

SELECT

group_name,

type_code ,

ISNULL(SUM(ISNULL(comm, 0)), 0) AS comm,

soft_dollar_rate,

year_number,

month_number

FROM

coin_ni_monthly_comm_booking

WHERE

--group_name = 'Middlefield Compass'

--and

ni_flag = 0

AND

CAST(year_number AS varchar(5)) + '-' + RIGHT('00'+CAST(month_number AS varchar(5)),2) IN (SELECT value FROM Common..getValuesAsTable(@yearMonths, ','))

AND rr_code NOT IN ( SELECT value FROM Common..getValuesAsTable(@excludeRRCodes, ',') )

AND LEFT (rr_code, 2) IN (SELECT value FROM Common..getValuesAsTable(@includeRRCodes, ',') )

AND (group_name IS NOT NULL OR group_name <> '')

GROUP BY

type_code,

soft_dollar_rate,

year_number,

month_number,

group_name

ORDER BY

year_number,

month_number

--select * from #tblComm

--return

----------------------------------------------------------------

CREATE TABLE #tblRank

(

group_name varchar(40)

, rank_total_GROSS float

, rank_total_NET float

)

INSERT INTO #tblRank ( group_name

, rank_total_GROSS

, rank_total_NET

)

SELECT

group_name,

SUM(ISNULL(comm, 0)) AS rank_total_GROSS,

SUM(ISNULL(comm*soft_dollar_rate, 0)) AS rank_total_NET

FROM #tblComm

GROUP BY group_name

--------------------------------------------

UPDATE A

SET

rank_total_GROSS = B.rank_total_GROSS,

rank_total_NET = B.rank_total_NET

FROM

#tblComm A

INNER JOIN #tblRank B ON A.group_name = B.group_name

----------------------------------------------------

DECLARE @cols NVARCHAR(2000)

SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT

'],[' + CAST(t2.year_number AS VARCHAR(20)) + '-' + RIGHT('00'+CAST(t2.month_number AS VARCHAR(5)),2)

FROM

#tblComm AS t2

ORDER BY '],[' + CAST(t2.year_number AS VARCHAR(20))+ '-' + RIGHT('00'+CAST(t2.month_number AS VARCHAR(5)),2)

FOR XML PATH('')

), 1, 2, '') + ']'

----------------------------------------------------

DECLARE @sumcols NVARCHAR(2000)

SELECT @sumcols = STUFF(( SELECT DISTINCT TOP 100 PERCENT

'],0) + ISNULL([' + CAST(t2.year_number AS VARCHAR(5)) + '-' + RIGHT('00'+CAST(t2.month_number AS VARCHAR(5)), 2)

FROM

#tblComm AS t2

ORDER BY '],0) + ISNULL([' + CAST(t2.year_number AS VARCHAR(5))+ '-' + RIGHT('00'+CAST(t2.month_number AS VARCHAR(5)), 2)

FOR XML PATH('')

), 1, 5, '') + '],0)'

-----------------------------------------------------

DECLARE @query NVARCHAR(4000)

SET @query =

N'SELECT group_name,

type_code,

soft_dollar_rate * 100 AS soft_dollar_rate, '

+ @cols +

+', '

+ @sumcols + ' AS TotalForTheRow_GROSS ' +', '

+ '(' + @sumcols + ')' + '*soft_dollar_rate' + ' AS TotalForTheRow_NET '

+ ',

rank_total_GROSS,

rank_total_NET

FROM

(SELECT group_name, type_code, soft_dollar_rate, rank_total_GROSS, rank_total_NET,

CAST(t2.year_number AS VARCHAR(5)) + ''-'' + RIGHT(''00''+CAST(t2.month_number AS VARCHAR(5)),2) AS ym

, t2.comm

FROM #tblComm AS t2) p

PIVOT

(

MAX([comm])

FOR ym IN

( '+

@cols +' )

) AS pvt

ORDER BY

rank_total_GROSS,

group_name '

--PRINT @query

EXECUTE(@query)