10 Replies Latest reply: Apr 9, 2014 12:52 AM by Tshifhiwa RSS

    calling oracle sql  with a  not null where cluee in java

    Tshifhiwa

      hi am trying to call oracle sql with a whith a clue not null ,i only what to select values which does not have null value in column inactivedate,but some how the calling is not working even if i have put not null in my where clue

       

       

        • 1. Re: calling oracle sql  with a  not null where cluee in java
          TPD-Opitz

          what does your statement look like after its been fully costructed?

           

          did you try this outcome in sqlplus?

           

          by

          TPD

          • 2. Re: calling oracle sql  with a  not null where cluee in java
            rp0428
            some how the calling is not working

            Neither Java nor Oracle ever says 'not working' when they discover a problem.

             

            If you want help you have to SHOW us what you are doing and what result you are getting.

             

            'Best practice' is: don't try to automate what you can't do manually. For dynamic sql that means create a query manually and verify that it actually works and gives the correct results. Then you can try to write code that constructs the query dynamically.

             

            If you do that you will find several problems with the code you posted.

             

            Comment out the execution and PRINT the method parameters being passed and the query that is being constructed.

             

            Does that query look correct? NO.

             

            Will that query execute properly? I seriously doubt it. As TPD suggested execute the query manually and see if it gives you the correct results. I don't see how it can.

             

            You are using 'sColumn3' in two different places: 1) as a column name to check for not null and 2) as a string literal to look for a match the SUB_CATEGORY_FLOW value. That doesn't even make sense.

                     String s_getValue = "SELECT "+sReturn+" FROM "+sTable+" WHERE "+sColumn1+" = ? and "+sColumn3+" IS NOT NULL AND  SUB_CATEGORY_FLOW = '"+sColumn3+"' and

            Your method also includes a parameter 'String sSchema' that isn't being used. Why have a parameter if you aren't going to use it?

            • 3. Re: calling oracle sql  with a  not null where cluee in java
              Tshifhiwa

              Hi i did change my query and test the sql in my sqlplus i test by the values which are giving me error the query return no value which is correct my problem is why my dynamic sql in java is reading those value which got null value in SUB_CATEGORY_DATE_INACTIVE

               

              latest code

              • 4. Re: calling oracle sql  with a  not null where cluee in java
                TPD-Opitz

                > i try this

                > if (s_InactiveDate.equals(null)) {

                What did you say s_InactiveDate meight point to?

                 

                bye

                TPD

                • 5. Re: calling oracle sql  with a  not null where cluee in java
                  Tshifhiwa

                  what do you mean when you say What did i say s_InactiveDate meight point to? am just selecting  value from database and do comparing with ActiveDate,sorry i don't undestand your question,i just what to check inactive date only if there is value i what to ignore all null values

                  • 6. Re: calling oracle sql  with a  not null where cluee in java
                    rp0428
                    what do you mean when you say What did i say s_InactiveDate meight point to? am just selecting  value from database and do comparing with ActiveDate,sorry i don't undestand your question,i just what to check inactive date only if there is value i what to ignore all null values

                    You say you want help but then you don't want to give us the information we need to help you. Try again:

                    If you want help you have to SHOW us what you are doing and what result you are getting.

                    . . .

                    'Best practice' is: don't try to automate what you can't do manually. For dynamic sql that means create a query manually and verify that it actually works and gives the correct results. Then you can try to write code that constructs the query dynamically.

                    . . .

                    Comment out the execution and PRINT the method parameters being passed and the query that is being constructed.

                    You haven't posted ANY execution of ANY code or any results from that execution..

                     

                    You haven't post the query that is actually being executed.

                     

                    You haven't printed out the method parameters being passed.

                     

                    You say you want help with your query but you won't post the query so we can see what might be wrong with it.

                     

                    Quit TELLING us what you are doing and SHOW us!

                    • 7. Re: calling oracle sql  with a  not null where cluee in java
                      TPD-Opitz

                      adf009 wrote:

                       

                      what do you mean when you say What did i say s_InactiveDate meight point to? am just selecting  value from database and do comparing with ActiveDate,sorry i don't undestand your question,i just what to check inactive date only if there is value i what to ignore all null values

                      I mean: what happens in this line when s_InactiveDate happens to hold a null?


                      bye

                      TPD

                      • 8. Re: calling oracle sql  with a  not null where cluee in java
                        Tshifhiwa

                        execution of ANY code or any results from that execution..

                         

                        • 9. Re: calling oracle sql  with a  not null where cluee in java
                          rp0428

                          Well all I can do is repeat what I said earlier - maybe you will read it and do it this time:

                          'Best practice' is: don't try to automate what you can't do manually. For dynamic sql that means create a query manually and verify that it actually works and gives the correct results. Then you can try to write code that constructs the query dynamically.

                          . . .

                          Comment out the execution and PRINT the method parameters being passed and the query that is being constructed.

                          You have to create the query yourself MANUALLY and then test it BEFORE you try to construct that same query in code.

                          this is the two query

                          select sub_category_date_active 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'

                          AND SUB_CATEGORY_DATE_INACTIVE IS NOT NULL 

                          Ok - does that query give you the results you want?

                          select 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'

                          AND SUB_CATEGORY_DATE_INACTIVE IS not  NULL

                          Ok - same query but different column in the SELECT. Does that query give you the results you want? Both of those queries could just be combined into one.

                          this is my dynamic java sql am querying the database

                               String s_getValue = "SELECT "+sReturn+" FROM "+sTable+" WHERE "+sColumn1+" = ? and "+sColumn3+" IS  NULL AND  SUB_CATEGORY_FLOW = '"+sColumn4+"' and SUB_CATEGORY_BOP_VERSION = '"+sColumn5+"'" ;

                          Why do you have 'IS NULL' in that code when you used 'IS NOT NULL' in the two queries? Does that seem right to you?

                          if is posible i what to select max(SUB_CATEGORY_DATE_INACTIVE) from sub_category

                          and also

                          select max(sub_category_date_active) from sub_category

                          OK - so just put MAX in your query like you just did above

                          something like

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

                          Again - why do you have 'IS NULL' in that code when you used 'IS NOT NULL' in the two queries? Does that seem right to you?

                           

                          FORGET THE JAVA CODE - write a query manually and test it until you get the correct results.

                          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'

                          AND SUB_CATEGORY_DATE_INACTIVE IS NOT NULL

                          • 10. Re: calling oracle sql  with a  not null where cluee in java
                            Tshifhiwa

                            hi sorry the query should be

                             

                             

                            the reason i have  is null i was trying to get all value but trying to ignore null value when i send inactive date for formating