Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2009-05-20
9
1,830 Views
Last Modified: 2013-11-23
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
Comment
Question by:aman0711
  • 5
  • 3
9 Comments
 
LVL 86

Accepted Solution

by:
CEHJ earned 250 total points
ID: 24433759
Make sure you're running against the exact same db as the exact same user
0
 
LVL 10

Author Comment

by:aman0711
ID: 24433910
Hi Charles,

                    Everything is same :-(
0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 250 total points
ID: 24434039
Do you know which statement the error is returned for? You have 2 queries in the code above.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 10

Author Comment

by:aman0711
ID: 24434062
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
 
LVL 10

Author Comment

by:aman0711
ID: 24434845
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
 
LVL 10

Author Comment

by:aman0711
ID: 24442435
Hi Charles, myjoltcola,

                  are you there?
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24442983
Yes, but busy on a project at the moment. Sorry. I'll return to the thread as soon as I can.
0
 
LVL 10

Author Comment

by:aman0711
ID: 24443029
Thanks :-)
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24569374
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question