Solved

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

Posted on 2009-05-20
9
1,824 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now