6 Replies Latest reply on Oct 3, 2013 1:18 PM by Dharani123



      I HAVE TO TABLES financialyr_mst and invoice_hdr. i want no sales date for particular financial year.


      invoice table----------

      NAME                            Null?     Type

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

      INV_CNTRL_NO                    NOT NULL  NUMBER(10,0)

      FINYR_CODE                                VARCHAR2(6)

      PERIOD_CODE                               NUMBER(3,0)

      INV_TYPE                                  VARCHAR2(10)

      INV_NO                                    NUMBER(10,0)

      INV_DT                                    DATE

      CUST_CODE                                 VARCHAR2(10)

      ORD_CNTRL_NO                              NUMBER(10,0)

      INV_VALUE                                 NUMBER(12,2)


      financial yr table---------------


      NAME                            Null?     Type

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

      FINYR_CODE                      NOT NULL  VARCHAR2(6)

      START_DATE                                DATE

      END_DATE                                  DATE




      i am trying these query


      select (start_date+level-1) fin_dt from finyr_mst

      where finyr_code =13

      connect by level<=(end_date-start_date)+1


      select inv_dt from  inv_hdr.



      but that query returninig thousand of records.i want only 365 records???????

        • 1. Re: NO SALES DATES

          Looking for this?


          with finyr_mast_13



          select (start_date+level-1) fin_dt

             from finyr_mst

            where finyr_code = 13

          connect by level <= (end_date-start_date)+1


          select fin_dt

            from finyr_mast_13 a


            join inv_hdr b

              on a.fin_dt = b.fnv_dt

          where b.fnv_dt is null;


          I am not very clear on what you want. May be you need to explain it better.

          1 person found this helpful
          • 2. Re: NO SALES DATES

            i want no sales date.....

            • 3. Re: NO SALES DATES

              The above query does not work?

              • 4. Re: NO SALES DATES
                Frank Kulash



                I think you want something like this:


                WITH days_wanted AS


                    SELECT  finyr_code

                    ,       start_date + 1 - LEVEL AS a_date

                    FROM    finyr_mst

                --  START WITH   finyr_code   IN (13)   -- if wanted

                    CONNECT BY   finyr_code         = PRIOR finyr_code

                            AND  LEVEL             <= end_date + 1 - start_date

                            AND  PRIOR SYS_GUID ()  IS NOT NULL


                SELECT    d.*

                FROM              days_wanted  d

                LEFT OUTER JOIN   inv_hdr      i  PARTITION BY (i.finyr_code)

                                                  ON  i.inv_dt  = d.a_date

                WHERE     i.inv_dt   IS NULL

                ORDER BY  d.finyr_code

                ,         d.a_date


                I hope this answers your question.
                If not, post  a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
                Explain, using specific examples, how you get those results from that data.   For example, do any of the columns in inv_hdr (other than inv_dt) play any role in this problem, particularly finyr_code?  If not, don't post them.

                Will you ever want to do this query for 2 (or more) values of finyr_mst.finyr_code at the same time?  Make sure your sample data and results show what you want.
                Always say which version of Oracle you're using (e.g.,

                See the forum FAQ: https://forums.oracle.com/message/9362002

                • 5. Re: NO SALES DATES


                  its coming too many records?


                  select a.fin_dt


                  (select (start_date+level-1) fin_dt

                     from finyr_mst

                    where finyr_code = 13

                  connect by level <= (end_date-start_date)+1

                  ) a, inv_hdr b

                  where  (a.fin_dt-b.inv_dt)>0

                  • 6. Re: NO SALES DATES

                    i got right answer.thanks for support..romba  nandri





                    select a.start_date+level-1 no_sale_date from



                    (select start_date start_date from finyr_mst where finyr_code=13) a



                    connect by level<=(select end_date-start_date from finyr_mst where finyr_code=13)+1


                    select inv_dt from inv_hdr  where finyr_code=13