Prepared statment executeQuery() error.

[Karthik Krishna]

Hi,

I have the following query in a class, and when this class hits the line ps.executeQuery() I get an exception which says " ORA-01006: bind variable does not exist "

public class ReportDAO 

{

PreparedStatement ps;

  String sqlQuery = "  select "
+ "LKP.A,"
+ "SUM ( CASE "
+  "      WHEN   INPUT_DATES.FIRST_DAY >= TRUNC(XYZ.close_dt,'MM' "
+  "     THEN  1 "
+  "   ELSE 0 "
+ "         END  ) Start "

+ "FROM "

+" LKP,"
+ " (SELECT "
      + "                 TO_DATE(?,'MM/YYYY') first_day, "
      + "                 ADD_MONTHS(TO_DATE(?,'MM/YYYY'),1)-1 last_day "
      + "         FROM dual) INPUT_DATES,"
+  " (SELECT "
      + "                 X, "
      + "                 SUM(NVL(num1, 0)) a, "
      + "                 SUM(NVL(num2, 0)) b, "
      + "                 SUM(NVL(num3, 0)) c, "
      + "                 SUM(NVL(num4,0)) d "
      + "         FROM "
      + "                 HELLO.DAYS_SAVED "
      + "         WHERE "
      + "                 TRUNC(SAVE_dt,'MM') = TO_DATE(?,'MM/YYYY') "
      + "         GROUP BY "
      + "                X) mcds  " 
+ " WHERE "
+  " LKP.A > '10' ;


String forSP = " and LKP.A in ('50','84','85','88','89' ";

String grpBy = " GROUP BY " 
+ "         LKP.A " ;

String allRowsNotZero = "SELECT * FROM "
	  + " ( "
	  + sqlQuery
	  + forSP
	  + " )"
	  + " WHERE "
          + " 	Start != 0 "

public String getData(Connection conn, String type,
      String monthYear, String fId) throws Exception
  {
    PreparedStatement ps;
    String xmlOutput = null;

    String forOff = null;

    try
    {

      if (type.equals("1"))
      {
        sqlQuery = allRowsNotZero ;
      }
      
      sqlQuery = sqlQuery + grpBy;

      logger.debug(sqlQuery);

      ps = conn.prepareStatement(sqlQuery,
          ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
      ps.setString(1, monthYear);
      ps.setString(2, monthYear);
      ps.setString(3, monthYear);

      if (!fId.equals("91"))
      {
        ps.setString(4, fId);
      }

      ResultSet rset = ps.executeQuery();

      if (rset.next())
      {
        rset.beforeFirst();
        xmlOutput = this.toXml(rset, monthYear).toString();
      }
      

      ps.close();
    }
    catch (Exception e)
    {
      throw e;

    }

    return xmlOutput;

  }
}

I have been trying to work towards different solutions but i do not understand why the ps.executeQuery fails ? does it have anything to do with the nested queries ? Can I have an inline query and the " ps.setString(1, monthYear);ps.setString(2, monthYear);ps.setString(3, monthYear); " can still substitute the values in the inside query ?

Could some one help me out ?

[Jeanne Boyarsky]

I've added code tags to make it more readable.

I only see 3 "?" bind variables in the query. How does the fourth one fit in?

[Karthik Krishna]

Sorry Jeanne

I am just mimicking a situation which I am encountering and for a few reasons I cannot copy paste the code in here so I am just replicating the issue in here.
You could just comment out the following from line 77 through 80.


# if (!fId.equals("91"))
# {
# ps.setString(4, fId);
# }

And another tiny correction line 38 is missing a bracket.