Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1838
  • Last Modified:

java.sql.SQLException: Invalid column name ERROR, Even though my column names are correct

Hi experts,
           
                I have the following java code, which is used to display 7 days data. When I am trying to run this code, I am getting the error Invalid column name.
                I ran the same queries on my DB and its working perfectly fine. Where could be the possible error?      
table border="1" cellspacing="1" bgcolor="#FFFFFF" width="100%">
	<tr>
		<th><b>Transactions</b></th>
		<th align='center'><b> Metric </b></th>
		<%  
		        java.util.ArrayList days = dz.getDays();    // 7 days Column header dates
                for (int i = 0; i < days.size(); i++) {
                %>
		<th align="right"><%=days.get(i)%></th>
		<%
                }
                %>
		<th align="right"><b>SLA </b></th>
	</tr>
	<%
                Connection conn = null;
                conn = db.getConnection();               // Database connection from the bean
 
                String sql = "";
                PreparedStatement pstmt = null;
                ResultSet rs = null;
 
                try {
                       
                        // CHANNELGROUP to SLAMIN lookup table
                        Hashtable slaMinList = new Hashtable();         // hashtable for coloring cell red in case value falls below SLA
                        sql = "SELECT METRIC, SLA_MIN FROM APM.APM_WEB_SLA_MIN WHERE APPLICATION='QPZE'";
 
                        pstmt = conn.prepareStatement(sql);
                        rs = pstmt.executeQuery(sql);
                        while (rs.next()) {
                                String key = rs.getString(1);
                                float value = rs.getFloat(2);
                                slaMinList.put(key, new Float(value));
                        }
                        rs.close();
                        pstmt.close();
                        ///////// NEW /////////
 
                        sql = "SELECT HOST_ID, AVGOFCPU_UTL, AVGOFMEM_UTL, NVL(TO_CHAR(d, 'mm/dd/yyyy'), ' ') DATA_DATE from  APM.APM_PRCV_QP_HWMET_90D, (SELECT TRUNC(SYSDATE - 1) - LEVEL + 1 d FROM DUAL CONNECT BY LEVEL <= 7) WHERE DATA_DATE(+) = d ORDER BY HOST_ID, d";
                        pstmt = conn.prepareStatement(sql);
                        rs = pstmt.executeQuery(sql);
                        ArrayList success = new ArrayList();         //ArrayList to hold success rate
                        ArrayList volume = new ArrayList();          //ArrayList to hold volume 
                        ArrayList hightlight = new ArrayList();		 //ArrayList for highlighting cell as red
                        float min = 0; 
                        String chggrp = "";                // Variable used to store Channel groups
                        if (rs.next()) {
                                chggrp = rs.getString(1);
                        }
                        boolean loop = true;
                        int rowcolor = 0; /// Alternate row color variable
						int daycount =0; //index for dates.
                        while (loop) {
                                boolean innerloop = true;
        %>
	<%
        if (rowcolor % 2 == 0) {
        %>
	<tr>
		<%
                } else {
                %>
	
	<tr style='background-color:#D0CECE'>
		<%
                }
                %>
 
		<th rowspan=2><%=chggrp%></th>
		<td align='center'><b>Volume</b></td>
		<%
                                        java.text.DecimalFormat nv = new java.text.DecimalFormat(
                                        "##,##,###");                 // Format volume value                              
                                        java.text.DecimalFormat np = new java.text.DecimalFormat(
                                        "#0.00%");                    // Format success rate upto two decimal digits
                                             
                                        while (innerloop) {
									String thisDate = rs.getString("EVENT_DATE");//get the record date								
                                while(!thisDate.equals(days.get(daycount)))
								{      //My dates dont match so we must be missing data, insert zeros
								success.add(new Float(1.0));
 
                                volume.add(new Integer(0));
			                                           	//also increase the day by one here
                                  daycount++;
                                 
								}
								float f = rs.getFloat(2);                   
								if (f==0.00 && rs.getInt(3)==0){              // if volume is zero then Success rate should be 100%
								f=1;
								}
                              
                                Object ftemp = slaMinList.get(chggrp);          // Cell Coloring code in case value falls below SLA
 
                                if (null != ftemp) {
                                        min = ((Float) ftemp).floatValue();
                                } else {
                                        min = 111;             // Set the minimum value at 111 because the actual value would never be 111.
                                }
 
                                if (f < min && min != 111)
                                        ///////// NEW /////////
                                        hightlight.add(new Integer(1)); // true
                                else
                                        hightlight.add(new Integer(0)); // false
                                success.add(new Float(f));
 
                                volume.add(new Integer(rs.getInt(3)));
 				daycount++;
                                if (!rs.next()) {
                                        innerloop = false;
                                        loop = false;
                                        break;
                                }
                                if (!rs.getString(1).equals(chggrp))
                                        innerloop = false;
                                chggrp = rs.getString(1);
                                        }
                                        //Filling Zeros in case data is mission, method is defined in DataZeroPad.java
                                       
                                        success = dz.PadZeros(success, 7 - success.size(),1);
                                        hightlight = dz.PadZeros(hightlight, 7 - hightlight.size(),0);
                                        volume = dz.PadZeros(volume, 7 - volume.size(),0);
                                        for (int v = 0; v < volume.size(); v++) {
 
                                out.print("<td align='right'>" + nv.format(volume.get(v)) + "</td>");
                                        }
 
                                        ///////// NEW /////////
                                        out.print("<td align='right'>NA</td>");
                                        ///////// NEW /////////
                %>
	</tr>
	<%
        if (rowcolor % 2 == 0) { /// color code
        %>
	<tr>
 
		<%
                } else {
                %>
	
	<tr style='background-color:#D0CECE'>
		<%
                }
                %>
		<td align='center'><b>Success Rate</b></td>
 
		<%
                                for (int s = 0; s < success.size(); s++) {
                                int h = ((Integer) hightlight.get(s)).intValue();
                                if (h == 1)
                                        
                                        out.print("<td align='right' style='background-color:#FF0000'>"
            
                                        + np.format(success.get(s))
                                        + "</span></td>");
                                else
                                        out.print("<td align='right'>" + np.format(success.get(s))
                                        + "</td>");
                                        }
                                        if (min != 111) {
                                out.print("<td align='right'>" + np.format(min) + "</td>");
                                        } else {
                                out.print("<td align='right'>NA</td>");
                                        }
                %>
	</tr>
	<%                         
	                        //Clearing all the Arraylists and reseting variables
                                volume.clear();           
                                success.clear();
                                hightlight.clear();
				daycount=0;
                                rowcolor++;
                        }
 
                        conn.close();
                        rs.close();
                        pstmt.close();
 
                } catch (Exception e) {
 
                        e.printStackTrace();
                        conn = null;
 
                }
        %>
 
</table>

Open in new window

0
aman0711
Asked:
aman0711
  • 5
  • 3
2 Solutions
 
CEHJCommented:
Make sure you're running against the exact same db as the exact same user
0
 
aman0711Author Commented:
Hi Charles,

                    Everything is same :-(
0
 
mrjoltcolaCommented:
Do you know which statement the error is returned for? You have 2 queries in the code above.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
aman0711Author Commented:
I guess I figured out the reason, but I need help from you folks to put in the correct logic.
I will explain the problem
0
 
aman0711Author Commented:
hi Charles, mrjoltcola,

               please help me out with this logic.

               As you can see the code, there are two queries. We use to compare the values and if value falls below SLA_MIN then color the Cell as red.

               In my all other applications, the first column of second query was compared with first column of first query, but in this case, First column of both the queries are different.

         how do I still achieve the comparison?

0
 
aman0711Author Commented:
Hi Charles, myjoltcola,

                  are you there?
0
 
mrjoltcolaCommented:
Yes, but busy on a project at the moment. Sorry. I'll return to the thread as soon as I can.
0
 
aman0711Author Commented:
Thanks :-)
0
 
mrjoltcolaCommented:
Hi aman, looks like you did not get satisfactory resolution on this one as neither of us followed up. If you want to continue work on this please clarify your last post on 5/20, and if you are still having errors, and I will try to help.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now