13 Replies Latest reply: Apr 1, 2013 5:13 AM by S10390 RSS

    Regarding query

    sunitha2010
      Dear All,

      I am trying to fetch the both null values and not null values based on the date condition.It is not giving the correct data count.can you please help me on this. I have to do this operation through SQL query only.

      I have below query..
      SELECT * FROM view1 where  end_date>=add_months(sysdate, -6);
      Here, in the above code end_date having the null values and not null values.

      I need to retrieve the data past 6 moths both null and not null data.

      I have tried below query...
      SELECT * FROM view1 where end_date
      
      =add_months(sysdate, -6) and end_date is not null or end_date is null;
      Based on the above query i am not getting the past 6 months data.Please help me on this query.

      Many Thanks for your help.

      Oracle Version : 11g

      Please suggest.
        • 1. Re: Regarding query
          jeneesh
          It should be
          SELECT * 
          FROM view1
          where  ( end_date >=add_months(sysdate, -6) 
                      or end_date is null)
          --Add extra condition if you have
          ;
          • 2. Re: Regarding query
            S10390
            SELECT * FROM view1 where end_date>=add_months(trunc(sysdate), -6);
            Check this...
            • 3. Re: Regarding query
              Nimish Garg
              try this
              SELECT * FROM view1
              where nvl(end_date,sysdate) >= add_months(sysdate, -6) 
              • 4. Re: Regarding query
                sunitha2010
                Hi All,

                Many thanks for your Quick Repsonse..

                If i use the below query i am not getting correct data, it is giving whole data from the view.
                SELECT *
                FROM view1
                where    ( end_date >=add_months(sysdate, -6)
                            or end_date is null)
                ;
                I need the data if end_date is having null values also for past 6 months. please help me.

                Many Thanks..
                • 5. Re: Regarding query
                  Ramin Hashimzadeh
                  sunitha2010 wrote:
                  Hi All,

                  Many thanks for your Quick Repsonse..

                  If i use the below query i am not getting correct data, it is giving whole data from the view.
                  SELECT *
                  FROM view1
                  where    ( end_date >=add_months(sysdate, -6)
                  or end_date is null)
                  ;
                  I need the data if end_date is having null values also for past 6 months. please help me.

                  Many Thanks..
                  this query also give you all records which end_date past 6 month and end_date is having null values
                  SELECT *
                  FROM view1
                  where    ( end_date >=add_months(sysdate, -6)
                  or end_date is null)
                  ;
                  if not, then please show sample data and result which you want to get
                  • 6. Re: Regarding query
                    sunitha2010
                    Hi,

                    Many Thanks for reply...

                    I need the data for last 6 months.Let me explain in detail..

                    I have view veiw1,need to display last 6 months data.While i am using below query then i am getting whole data from view.

                    here,in below query i am validating the query with date column end_date. in the veiw for this end_date column contains null values for last 6 months.Even it contains nulls also i need to retrieve the data in the result set.Is it possible with below query?
                    SELECT start_date,end_date
                    FROM view1
                    where    ( end_date >=add_months(sysdate, -6)
                                or end_date is null)
                    ;
                    If i ran the same above query it is giving below data..

                    Query data :
                    start_date                        end_date
                    --------------                      ------------
                    6/3/2012 12:49:46 PM     
                    6/3/2012 12:52:14 PM     
                    6/21/2012 4:49:22 PM     
                    6/21/2012 4:49:23 PM     
                    8/13/2012 1:43:56 PM     
                    8/17/2012 12:01:02 PM     
                    8/17/2012 12:02:39 PM     
                    8/17/2012 3:27:47 PM     
                    8/17/2012 3:27:48 PM     
                    8/21/2012 12:26:30 PM     
                    8/21/2012 4:16:53 PM     
                    9/24/2012 12:01:39 PM     
                    9/24/2012 4:33:25 PM     
                    9/24/2012 4:33:26 PM     
                    9/24/2012 4:35:05 PM     
                    10/24/2012 3:55:43 PM     
                    11/28/2012 6:25:17 PM     
                    11/28/2012 6:27:57 PM     
                    11/28/2012 6:32:17 PM     
                    11/28/2012 6:37:45 PM     
                    11/28/2012 6:43:14 PM     
                    11/28/2012 6:47:32 PM     
                    11/28/2012 6:54:06 PM     
                    11/28/2012 6:58:21 PM     
                    11/28/2012 9:11:09 PM     
                    11/28/2012 9:29:45 PM     
                    1/10/2013 3:45:43 PM     
                    1/10/2013 3:50:53 PM     
                    1/10/2013 3:55:53 PM     
                    1/10/2013 4:00:10 PM     
                    1/10/2013 4:51:12 PM     
                    1/15/2013 4:35:30 PM     
                    1/15/2013 4:47:58 PM     
                    1/15/2013 5:04:32 PM     
                    1/15/2013 5:17:45 PM     
                    1/17/2013 12:47:11 PM     
                    1/18/2013 3:44:11 PM     
                    1/18/2013 3:49:26 PM     
                    2/4/2013 9:44:17 AM     
                    2/27/2013 12:26:23 PM     
                    2/27/2013 12:28:05 PM     
                    3/10/2013 9:52:30 PM     
                    3/10/2013 9:53:47 PM     
                    3/10/2013 9:55:03 PM     
                    3/18/2013 3:39:27 PM     
                    3/19/2013 4:45:55 PM     
                    Many thanks for your help.
                    • 7. Re: Regarding query
                      Vivek L
                      Sorry, but your requirements are still not clear.
                      Define "past 6 months". Is it based on end_date or start_date?
                      If a row has start_date as 01-Apr-2012 and end_date as NULL, should this be included in your desired resultset?
                      • 8. Re: Regarding query
                        jeneesh
                        sunitha2010 wrote:
                        Hi,

                        Many Thanks for reply...

                        I need the data for last 6 months.Let me explain in detail..
                        Last 6 months - Based on which field? Start_date or end_date?

                        >
                        I have view veiw1,need to display last 6 months data.While i am using below query then i am getting whole data from view.
                        Is your view contain only 6 months data?

                        >
                        here,in below query i am validating the query with date column end_date. in the veiw for this end_date column contains null values for last 6 months.Even it contains nulls also i need to retrieve the data in the result set.Is it possible with below query?
                        So you need NULLs, and you are getting NULLS, what is your problem then?
                        • 9. Re: Regarding query
                          Ramin Hashimzadeh
                          i think this must help :
                           SELECT start_date,end_date
                            FROM view1
                            where    ( start_date >=add_months(sysdate, -6) or end_date is null);
                          • 10. Re: Regarding query
                            sunitha2010
                            Hi jeenesh,


                            Last 6 months - Based on end_date..

                            My view contains past 2 years data...I am getting the results..There is no problem at all..

                            Problem is --

                            Last 6 months data means-- I need to get the data upto Nov,2012. right?
                            am getting data for july,2012 also from this query.
                            • 11. Re: Regarding query
                              Vivek L
                              may be
                              select      * 
                              from view1 
                              where  nvl(end_date,start_date) >= add_months(sysdate, -6)
                              • 12. Re: Regarding query
                                jeneesh
                                sunitha2010 wrote:
                                Hi jeenesh,


                                Last 6 months - Based on end_date..

                                My view contains past 2 years data...I am getting the results..There is no problem at all..

                                Problem is --

                                Last 6 months data means-- I need to get the data upto Nov,2012. right?
                                In your sample output you gave, there is no data with ENd date in July..
                                Can you show us the output with end_date in july?
                                am getting data for july,2012 also from this query.
                                • 13. Re: Regarding query
                                  S10390
                                  Whenever you are comparing date values with sysdate, use Trunc(), because sysdate will have the time portion.
                                  SELECT start_date,end_date FROM view1 where ( trunc(end_date )>=add_months(trunc(sysdate), -6) or end_date is null);