1 2 Previous Next 22 Replies Latest reply: Mar 3, 2014 10:11 AM by rukbat Go to original post RSS
      • 15. Re: ignore null values
        Tshifhiwa

        AM GETING java.sql.SQLException: Invalid column name

         

        public synchronized String getValueFromTableDate(String sSchema, String sTable, String sColumn1,String sValue1,String sReturn,String sColumn3,String sColumn4,String sColumn5)throws SQLException

             {

                 Connection conn = getConnection(sSchema);

                 String s_returned= "";

               

                 PreparedStatement ps_getValue;

                 String s_getValue = "SELECT NVL(MAX("+sReturn+"),SYSDATE) FROM "+sTable+" WHERE "+sColumn1+" = ? AND "+sColumn3+" IS NOT NULL AND  SUB_CATEGORY_FLOW = '"+sColumn4+"' AND SUB_CATEGORY_BOP_VERSION = '"+sColumn5+"'" ;

              

               

                  System.out.println("column table selected passed "+sReturn);

                System.out.println("tablename passed "+sTable);

                System.out.println("column1 name passed "+sColumn1);

                 System.out.println("column3 name passed "+sColumn3);

                System.out.println("column4 name passed "+sColumn4);

                   System.out.println("column5 name passed "+sColumn5);

              

                    System.out.println("queryResults******* "+s_getValue);

                 

                 ResultSet rs ;

               

                 if(conn != null)

                 {

                 try

                 {

                     ps_getValue = conn.prepareStatement(s_getValue);

                     ps_getValue.setString(1, sValue1);

                     //ps_getValue.setString(2, sValue1);

                  

                     rs = ps_getValue.executeQuery();

                     System.out.println("queryResultsAfter Query******* "+rs);

                   

                     while(rs.next())

                     {

                         s_returned = rs.getString(sReturn);

                     }

                   

                 }catch(SQLException sqe)

                 {

                     logger.log(Level.FATAL, "Errror found retrieving a value ",sqe);

                     throw new SQLException(sqe);

                 }finally

                 {

                     try

                     {

                         conn.close();

        //                 rs.close();

        //                 ps_getValue.close();

                     }catch(SQLException sqe)

                     {

                         logger.log(Level.FATAL, "Errror found closing statement and resultset",sqe);

                         throw new SQLException(sqe);

                     }

                 }

                 }

                  System.out.println("FinalqueryResultsAfter Query*******"+s_returned);

                 return s_returned;

             }

         

        and my printout is

        column table selected passed SUB_CATEGORY_DATE_ACTIVE

        tablename passed SUB_CATEGORY

        column1 name passed SUB_CATEGORY_CAT_CODE

        column3 name passed SUB_CATEGORY_DATE_INACTIVE

        column4 name passed OUT

        column5 name passed 3

        queryResults******* SELECT NVL(MAX(SUB_CATEGORY_DATE_ACTIVE),SYSDATE) FROM SUB_CATEGORY WHERE SUB_CATEGORY_CAT_CODE = ? AND SUB_CATEGORY_DATE_INACTIVE IS NOT NULL AND  SUB_CATEGORY_FLOW = 'OUT' AND SUB_CATEGORY_BOP_VERSION = '3'

        queryResultsAfter Query******* com.mchange.v2.c3p0.impl.NewProxyResultSet@1cb7a1

        03 Mar 2014 09:42:30,890[490] - [091] DEBUG com.mchange.v2.c3p0.impl.NewPooledConnection  - com.mchange.v2.c3p0.impl.NewPooledConnection@157aa53 handling a throwable.

        java.sql.SQLException: Invalid column name

            at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3711)

            at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:2763)

            at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:494)

            at com.mchange.v2.c3p0.impl.NewProxyResultSet.getString(NewProxyResultSet.java:3326)

        • 16. Re: ignore null values
          Partha Sarathy S

          Are these correct column names?

           

          String s_getValue = "SELECT NVL(MAX("+sReturn+"),SYSDATE) FROM "+sTable+" WHERE "+sColumn1+" = ? AND "+sColumn3+" IS NOT NULL AND  SUB_CATEGORY_FLOW = '"+sColumn4+"' AND SUB_CATEGORY_BOP_VERSION = '"+sColumn5+"'" ;

          I am not expert in Java. But looking at the following error message you are getting it seems there is problem with the column name and not with the function.

           

          java.sql.SQLException: Invalid column name

              at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3711)

              at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:2763)

              at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:494)

            at com.mchange.v2.c3p0.impl.NewProxyResultSet.getString(NewProxyResultSet.java:3326)

          • 17. Re: ignore null values
            onkar.nath

            As already indicated above, MAX function ignores null value by default and to ignore NULL values for other fields used in the filter , you can use NVL function. If you have hard-coded value in filter like 104 for SUB_CATEGORY_CAT_CODE ( I am assuming that all the columns are VARCHAR2 type as the values passed in them is in single-quotes even though they are number), then you can think of using NVL2 function for fields like SUB_CATEGORY_CAT_CODE,SUB_CATEGORY_CODE etc

             

             

            Onkar

            • 18. Re: ignore null values
              Tshifhiwa

              its a dynamic query in java

              String s_getValue = "SELECT NVL(MAX("+sReturn+"),SYSDATE)SUB_CATEGORY_DATE_ACTIVE FROM "+sTable+" WHERE "+sColumn1+" = ? AND "+sColumn3+" IS NOT NULL AND  SUB_CATEGORY_FLOW = '"+sColumn4+"' AND SUB_CATEGORY_BOP_VERSION = '"+sColumn5+"'" ;

               

              its geting

              queryResults******* SELECT NVL(MAX(SUB_CATEGORY_DATE_INACTIVE),SYSDATE)SUB_CATEGORY_DATE_ACTIVE FROM SUB_CATEGORY WHERE SUB_CATEGORY_CAT_CODE = ? AND SUB_CATEGORY_DATE_INACTIVE IS NOT NULL AND  SUB_CATEGORY_FLOW = 'OUT' AND SUB_CATEGORY_BOP_VERSION = '3'

              • 19. Re: ignore null values
                Partha Sarathy S

                Fine. When you execute this query, you don't get NULL values right? Then how do you get NULL values when executed from Java?

                • 20. Re: ignore null values
                  Tshifhiwa

                  because when i run second query

                  column table selected passed SUB_CATEGORY_DATE_ACTIVE

                  tablename passed SUB_CATEGORY

                  column1 name passed SUB_CATEGORY_CAT_CODE

                  column3 name passed SUB_CATEGORY_DATE_INACTIVE

                  column4 name passed OUT

                  column5 name passed 3

                  queryResults******* SELECT NVL(MAX(SUB_CATEGORY_DATE_ACTIVE),SYSDATE)SUB_CATEGORY_DATE_ACTIVE FROM SUB_CATEGORY WHERE SUB_CATEGORY_CAT_CODE = ? AND SUB_CATEGORY_DATE_INACTIVE IS NOT NULL AND  SUB_CATEGORY_FLOW = 'OUT' AND SUB_CATEGORY_BOP_VERSION = '3'

                  queryResultsAfter Query******* com.mchange.v2.c3p0.impl.NewProxyResultSet@1d4c265

                  03 Mar 2014 09:54:57,285[236] - [091] DEBUG com.mchange.v2.async.ThreadPoolAsynchronousRunner  - com.mchange.v2.async.ThreadPoolAsynchronousRunner@1ac2f9c: Adding task to queue -- com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@12679ed

                  03 Mar 2014 09:54:57,285[1747] - [091] DEBUG com.mchange.v2.resourcepool.BasicResourcePool  - trace com.mchange.v2.resourcepool.BasicResourcePool@106082 [managed: 3, unused: 1, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@1786ce)

                  FinalqueryResultsAfter Query*******2014-03-03 09:54:57 i get first value

                  03 Mar 2014 09:54:57,286[1747] - [091] DEBUG com.mchange.v2.resourcepool.BasicResourcePool  - trace com.mchange.v2.resourcepool.BasicResourcePool@106082 [managed: 3, unused: 1, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@1786ce)

                  column table selected passed SUB_CATEGORY_DATE_INACTIVE

                  tablename passed SUB_CATEGORY

                  column1 name passed SUB_CATEGORY_CAT_CODE

                  column3 name passed SUB_CATEGORY_DATE_INACTIVE

                  column4 name passed OUT

                  column5 name passed 3

                  queryResults******* SELECT                                 am geting error because second value is diffirent NVL(MAX(SUB_CATEGORY_DATE_INACTIVE),SYSDATE)SUB_CATEGORY_DATE_ACTIVE FROM SUB_CATEGORY WHERE SUB_CATEGORY_CAT_CODE = ? AND SUB_CATEGORY_DATE_INACTIVE IS NOT NULL AND  SUB_CATEGORY_FLOW = 'OUT' AND SUB_CATEGORY_BOP_VERSION = '3'

                  queryResultsAfter Query******* com.mchange.v2.c3p0.impl.NewProxyResultSet@f3f73a

                  03 Mar 2014 09:54:57,297[490] - [091] DEBUG com.mchange.v2.c3p0.impl.NewPooledConnection  - com.mchange.v2.c3p0.impl.NewPooledConnection@937e07 handling a throwable.

                  java.sql.SQLException: Invalid column name

                      at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3711)

                      at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:2763)

                      at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:494)

                      at com.mchange.v2.c3p0.impl.NewProxyResultSet.getString(NewProxyResultSet.java:3326)

                   

                  another thing the query is returning wrong value

                  with this query i can see the true value in database not just sysdate

                  select (max(sub_category_date_active)), (max( SUB_CATEGORY_DATE_INACTIVE))  from sub_category

                  where SUB_CATEGORY_CAT_CODE = '104'

                  and  SUB_CATEGORY_CODE = '10'

                  and  SUB_CATEGORY_FLOW = 'OUT'

                  and SUB_CATEGORY_BOP_VERSION = '3'

                   

                  (MAX(SUB_CATEGORY_DATE_ACTIVE)) (MAX(SUB_CATEGORY_DATE_INACTIVE))

                  ------------------------- -------------------------

                  01/MAY/13                                          

                   

                  1 rows selected

                  • 21. Re: ignore null values
                    rp0428

                    DUPLICATE THREAD!    

                     

                    Please don't create duplicate threads. This is the third thread you have created for this same question.

                    you can also follow this discussion

                    calling oracle sql  with a  not null where cluee in java

                    Yes - you are already being helped in that thread.

                    https://community.oracle.com/thread/3522453

                     

                    Mark this thread, and your other new one, ANSWERED and continue using your original thread.

                    • 22. Re: ignore null values
                      rukbat

                      Moderator Action:

                      Duplicate and triplicate locked.

                       

                      O.P.,

                       

                      Stay with that original thread.  

                      That will keep all relevant information in one place.

                      Multiple threads easily gets relevant information lost in a matter of hours (if not minutes).

                      1 2 Previous Next