Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Java dynamically builds SQL Expand / Collapse
Author
Message
Posted Thursday, March 28, 2013 12:32 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 21, 2014 4:05 AM
Points: 79, Visits: 188
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()[i].length() > YEAR_LENGTH) { // Pro-Rated Year is selected
//for(int i=0; i < getSelAvailYears().length; i++) { // Years in Desc Order
//if(i == 0 && getSelAvailYears()[i].length() > YEAR_LENGTH) { // Pro-Rated Year is selected
int curProYear = Integer.parseInt(getSelAvailYears()[i].substring(0, YEAR_LENGTH));

if(getYearType() == FISC_YEAR) {
if(isIncNewIssues()) {
// add Comm column
if(!isExclMontlyBreakdown()){
sql += getMonthSelectClause(curProYear, FISC_YEAR, grossNetMultiplyer);
}
sql += "'" + getSelAvailYears()[i] + 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()[i] + COMM_STR);
// add NI column: Do Not Pro-Rate
sql += "'" + getSelAvailYears()[i] + 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()[i] + NI_STR);
// add Total column
sql += "'" + getSelAvailYears()[i] + "' = (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()[i] + TOTAL_STR);
}
else {
// add Comm column
if(!isExclMontlyBreakdown()){
sql += getMonthSelectClause(curProYear, FISC_YEAR, grossNetMultiplyer);
}
sql += "'" + getSelAvailYears()[i] + "' = 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()[i] + COMM_STR);
}
}
else {
if(isIncNewIssues()) {
// add Comm column
if(!isExclMontlyBreakdown()){
sql += getMonthSelectClause(curProYear, CAL_YEAR, grossNetMultiplyer);
}
sql += "'" + getSelAvailYears()[i] + 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()[i] + COMM_STR);
// add NI column: Do Not Pro-Rate
sql += "'" + getSelAvailYears()[i] + 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()[i] + NI_STR);
// add Total column
sql += "'" + getSelAvailYears()[i] + "' = (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()[i] + TOTAL_STR);
}
else {
// add Comm column
if(!isExclMontlyBreakdown()){
sql += getMonthSelectClause(curProYear, CAL_YEAR, grossNetMultiplyer);
}
sql += "'" + getSelAvailYears()[i] + "' = 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()[i] + COMM_STR);
}
}
}
else {
int curYear = Integer.parseInt(getSelAvailYears()[i].substring(0, YEAR_LENGTH));

if(getYearType() == FISC_YEAR) {
if(isIncNewIssues()) {
// add Comm column
if(!isExclMontlyBreakdown()){
sql += getMonthSelectClause(curYear, FISC_YEAR, grossNetMultiplyer);
}
sql += "'" + getSelAvailYears()[i] + 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()[i] + COMM_STR);
// add NI column
sql += "'" + getSelAvailYears()[i] + 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()[i] + NI_STR);
// add Total column
sql += "'" + getSelAvailYears()[i] + "' = 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()[i] + TOTAL_STR);
}
else {
// add Comm column
if(!isExclMontlyBreakdown()){
sql += getMonthSelectClause(curYear, FISC_YEAR, grossNetMultiplyer);
}
sql += "'" + getSelAvailYears()[i] + "' = 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()[i] + COMM_STR);
}
}
else {
if(isIncNewIssues()) {
// add Comm column
if(!isExclMontlyBreakdown()){
sql += getMonthSelectClause(curYear, CAL_YEAR, grossNetMultiplyer);
}
sql += "'" + getSelAvailYears()[i] + 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()[i] + COMM_STR);
// add NI column
sql += "'" + getSelAvailYears()[i] + 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()[i] + NI_STR);
// add NI column
sql += "'" + getSelAvailYears()[i] + "' = 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()[i] + TOTAL_STR);
}
else {
// add Comm column
if(!isExclMontlyBreakdown()){
sql += getMonthSelectClause(curYear, CAL_YEAR, grossNetMultiplyer);
}
sql += "'" + getSelAvailYears()[i] + "' = 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()[i] + 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()[i].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()[i])){
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()[i])){
//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()[i] + "' 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[i] + " " + (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[i] + "), 0.0), ";
monthYearHdrs.add(MONTH_HDR[i] + " " + (yearNumber - 1));
if(isIncNewIssues()) {
// add NI column
sql += "'" + MONTH_HDR[i] + " " + (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[i] + "), 0.0), ";
monthYearHdrs.add(MONTH_HDR[i] + " " + (yearNumber - 1));
// add Total column
sql += "'" + MONTH_HDR[i] + " " + (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[i] + "), 0.0), ";
monthYearHdrs.add(MONTH_HDR[i] + " " + (yearNumber - 1));
}
}
// all the other months
else {
sql += "'" + MONTH_HDR[i] + " " + 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[i] + "), 0.0), ";
monthYearHdrs.add(MONTH_HDR[i] + " " + yearNumber);
if(isIncNewIssues()) {
// add NI column
sql += "'" + MONTH_HDR[i] + " " + 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[i] + "), 0.0), ";
monthYearHdrs.add(MONTH_HDR[i] + " " + yearNumber);
// add Total column
sql += "'" + MONTH_HDR[i] + " " + 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[i] + "), 0.0), ";
monthYearHdrs.add(MONTH_HDR[i] + " " + yearNumber);
}
}
}
else {
sql += "'" + MONTH_HDR[i] + " " + 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[i] + "), 0.0), ";
monthYearHdrs.add(MONTH_HDR[i] + " " + yearNumber);
if(isIncNewIssues()) {
// add NI column
sql += "'" + MONTH_HDR[i] + " " + 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[i] + "), 0.0), ";
monthYearHdrs.add(MONTH_HDR[i] + " " + yearNumber);
// add Total column
sql += "'" + MONTH_HDR[i] + " " + 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[i] + "), 0.0), ";
monthYearHdrs.add(MONTH_HDR[i] + " " + yearNumber);
}
}
}

return sql;
}



Thanks,
Rob
Post #1436657
Posted Thursday, March 28, 2013 12:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 11,970, Visits: 10,995
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1436669
Posted Thursday, March 28, 2013 1:29 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 21, 2014 4:05 AM
Points: 79, Visits: 188
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_code	client_comp_name	salesp_name	trader_name	group_code	group_name	type_code	soft_dollar_rate	locat_code	Nov-12	Dec-12
YGRP RBIM Wiggan, Tim Stratis, George YGGR RBIM AGNY 100 TOR 282297.1 192076.63
CAFL RBIM OPTIONS S.F. Sales S.F. Trader YGGR RBIM SF 100 TOR 73500 32250

----------------------------------------------
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.
Post #1436692
Posted Thursday, March 28, 2013 1:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 11,970, Visits: 10,995
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1436705
Posted Thursday, March 28, 2013 1:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:46 PM
Points: 12,744, Visits: 31,065
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1436712
Posted Thursday, March 28, 2013 2:08 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 21, 2014 4:05 AM
Points: 79, Visits: 188
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

Post #1436721
Posted Thursday, April 04, 2013 1:19 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 21, 2014 4:05 AM
Points: 79, Visits: 188
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_name varchar(40)
, type_code varchar(4)
, comm float
, soft_dollar_rate float
, year_number int
, month_number int
, rank_total_GROSS float
, 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)

Post #1438995
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse