1 2 Previous Next 22 Replies Latest reply: Mar 3, 2014 10:11 AM by rukbat RSS

    ignore null values

    Tshifhiwa

      hi how can i ignore null values,my use case is i have value which i beign pass by user from java application some value the user pass return null in database how can i ignore those null value but still return value which got value even if use is sending value which got null

      my query is

      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

       

      the value in my query return null but i don't what to return value if there is null,i don't have control of the value what the use is sending

      you can also follow this discussion

      calling oracle sql  with a  not null where cluee in java

        • 1. Re: ignore null values
          Partha Sarathy S

          Why don't you use NVL function.

          select NVL(max(sub_category_date_active),default_vale_instead_of_NULL), NVL(max( SUB_CATEGORY_DATE_INACTIVE),default_vale_instead_of_NULL)  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

          • 2. Re: ignore null values
            Solomon Yakobson

            adf009 wrote:

             

            hi how can i ignore null values,my use case is i have value which i beign pass by user from java application some value the user pass return null in database how can i ignore those null value but still return value which got value even if use is sending value which got null

            my query is

            the value in my query return null but i don't what to return value if there is null,i don't have control of the value what the use is sending

             

            MAX already ignores NULLs. Your query has condition AND SUB_CATEGORY_DATE_INACTIVE IS NOT NULL. Therefore, if query returns NULL max(SUB_CATEGORY_DATE_INACTIVE), then either all rows in table sub_category where SUB_CATEGORY_CAT_CODE = '104' and  SUB_CATEGORY_CODE = '10' and SUB_CATEGORY_FLOW = 'OUT' and SUB_CATEGORY_BOP_VERSION = '3' have NULL value in SUB_CATEGORY_DATE_INACTIVE column or there are no rows with SUB_CATEGORY_CAT_CODE = '104' and  SUB_CATEGORY_CODE = '10' and SUB_CATEGORY_FLOW = 'OUT' and SUB_CATEGORY_BOP_VERSION = '3'. Issue:

             

            select count(*) , 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

             

            and post results.

             

            SY.

            • 3. Re: ignore null values
              Biju Das

              Solomon Yakobson wrote:

               

              MAX already ignores NULLs. Your query has condition AND SUB_CATEGORY_DATE_INACTIVE IS NOT NULL. Therefore, if query returns NULL max(SUB_CATEGORY_DATE_INACTIVE), then either all rows in table sub_category where SUB_CATEGORY_CAT_CODE = '104' and  SUB_CATEGORY_CODE = '10' and SUB_CATEGORY_FLOW = 'OUT' and SUB_CATEGORY_BOP_VERSION = '3' have NULL value in SUB_CATEGORY_DATE_INACTIVE column or there are no rows with SUB_CATEGORY_CAT_CODE = '104' and  SUB_CATEGORY_CODE = '10' and SUB_CATEGORY_FLOW = 'OUT' and SUB_CATEGORY_BOP_VERSION = '3'. Issue:

               

              select count(*) , 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

               

               

              If the predicate SUB_CATEGORY_DATE_INACTIVE IS NOT NULL is true then can't fetch the NULL in SUB_CATEGORY_DATE_INACTIVE (the same column is used in SELECT and WHERE.So, SUB_CATEGORY_DATE_INACTIVE can not have null if  "SUB_CATEGORY_DATE_INACTIVE IS NOT NULL" is in where predicates  ).

               

              Regards

              Biju

              • 4. Re: ignore null values
                Solomon Yakobson

                Biju Das wrote:

                 

                If the predicate SUB_CATEGORY_DATE_INACTIVE IS NOT NULL is true then can't have the NULL in SUB_CATEGORY_DATE_INACTIVE (the same column). Please correct me if I am wrong here.

                 

                 

                Correct, and is exactly what first part of my reply said. However, there is second part - OP is doing implicit aggregation by using MAX. And implicit aggreation query always returns one row even when fetch yields no rows. For example:

                 

                SQL> with t as (select null n from emp)
                  2  select n from t where n is not null;

                no rows selected

                SQL> with t as (select null n from emp)
                  2  select max(n) from t where n is not null;

                M
                -


                SQL>

                 

                As you can see, OP's query can return NULL even though there is condition SUB_CATEGORY_DATE_INACTIVE IS NOT NULL.

                 

                SY

                • 5. Re: ignore null values
                  Biju Das

                  Yes, that's correct.My apologies!! I mis read your post.

                   

                  Regards

                  Biju

                  • 6. Re: ignore null values
                    Tshifhiwa

                    the results of

                    select count(*) , 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

                     

                    is

                    COUNT(*)         MAX(SUB_CATEGORY_DATE_INACTIVE)

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

                    0                                        

                     

                    1 rows selected

                     

                    in this query am geting error

                    select NVL(max(sub_category_date_active),default_value_instead_of_NULL), NVL(max( SUB_CATEGORY_DATE_INACTIVE),default_value_instead_of_NULL)  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

                     

                    ORA-00904:dEFAULT_VALUE_instead_of_null

                    • 7. Re: ignore null values
                      Partha Sarathy S

                      I told you to use default value like below.

                      select NVL(max(sub_category_date_active),sysdate), NVL(max( SUB_CATEGORY_DATE_INACTIVE),sysdate)  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;

                      Not the default_value_instead_of_null clause straight away.

                      • 8. Re: ignore null values
                        Biju Das

                        adf009 wrote:

                         

                        the results of

                        select count(*) , 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

                         

                        is

                        COUNT(*)         MAX(SUB_CATEGORY_DATE_INACTIVE)

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

                        0                                        

                         

                        1 rows selected

                         

                        in this query am geting error

                        select NVL(max(sub_category_date_active),default_value_instead_of_NULL), NVL(max( SUB_CATEGORY_DATE_INACTIVE),default_value_instead_of_NULL)  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

                         

                        ORA-00904:dEFAULT_VALUE_instead_of_null

                        dEFAULT_VALUE_instead_of_null should be a date that you are asked to set.

                        • 9. Re: ignore null values
                          Tshifhiwa

                          i did that i do get value

                          NVL(MAX(SUB_CATEGORY_DATE_ACTIVE),SYSDATE) NVL(MAX(SUB_CATEGORY_DATE_INACTIVE),SYSDATE)

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

                          03/MAR/14                 03/MAR/14               

                           

                          1 rows selected

                          • 10. Re: ignore null values
                            Partha Sarathy S

                            So your problem is solved? Or what else you need. You are not getting null now right?

                            • 11. Re: ignore null values
                              Tshifhiwa

                              not realy

                              am sitl geting null check this thread

                              Re: calling oracle sql  with a  not null where cluee in java

                              • 12. Re: ignore null values
                                Partha Sarathy S

                                I am not clear. You have used NVL function. You said you are getting rows. But now you are saying still you are getting nulls instead of values. Are you using this query in your front end code?

                                • 13. Re: ignore null values
                                  Tshifhiwa

                                  am using it in my java check the other thread

                                  • 14. Re: ignore null values
                                    Partha Sarathy S

                                    If you are calling this same query from Java, then try using the query. If not, then whatever query you are using in Java, add NVL function to that and check it as I did for the above query. If you are using the same query then it should work the same way as when executed from database. If not, check your Java code

                                    1 2 Previous Next