SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Java dynamically builds SQL


Java dynamically builds SQL

Author
Message
RVO
RVO
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1486 Visits: 521
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62657 Visits: 17959
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.

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)
RVO
RVO
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1486 Visits: 521
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62657 Visits: 17959
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.

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)
Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71860 Visits: 40942
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
RVO
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1486 Visits: 521
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
RVO
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1486 Visits: 521
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)


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search