Solved

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

Posted on 2009-05-20
9
1,832 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
[X]
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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
This video shows how to recover a database from a user managed backup
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses

752 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