1 2 Previous Next 26 Replies Latest reply on Nov 27, 2013 7:28 AM by Moazam Shareef

    From Date to Date

    Moazam Shareef

      Hello Guys,

      I want to insert some data into details table field using the two dates from the master table as shown in below example.

      have the below two filed in master table.

      From_Date: 01-01-2013

      To_Date: 31-01-2013

      I want to fill the detail table field Process_date using the above dates, it should be like this.

      01-01-2013

      02-01-2013

      03-01-2013

      04-01-2013

      .

      .

      .

      .

      31-01-2013

      How can i achive this task? using forms 6i database 9i

        • 1. Re: From Date to Date
          Priyasagi

          Cursors will help us to complete the task.

           

          key-next-item trigger of the header_block <to_prdate>

           

          declare

          declare cursor c1 is select * from <detail table> where process_date between <header_table.fr_prdate> and <header_table.to_prdate>;

          begin

          go_block(<detail_block_name>);

          first_record;

          for i in c1 loop

               if c1%notfound then

                    exit;

               else

                    detail_block.field1:=i.field1;

                    detail_block.field2:=i.field2;

                    ....

                    ....

               next_record;

               end if;

          end loop;

          end;

          • 2. Re: From Date to Date
            Andreas Weiden

            1. If the records with the dates are already existing in your detail-table, simply put a WHERE-condition on your block.

             

            2. If the record are not existing and you want to insert them, do something like

             

            INSERT INTO DETAIL_TABLE (
              DATE_COLUMN,
              XX
            ) SELECT :BLOCK.FROM_DATE+LEVEL-1,
                 'X',
                 FROM DUAL
                 CONNECT BY LEVEL<=:BLOCK.TO_DATE-:BLOCK.FROM_DATE;
            

             

            Afterwards, requery the block as in 1.

            • 3. Re: From Date to Date
              Sanjeev G

              Use this

               

              INSERT INTO table1 (date_column)

              SELECT v_from_date + ROWNUM - 1

                FROM dual

              CONNECT BY (v_from_date + ROWNUM - 1) <= v_to_date;

              • 4. Re: From Date to Date
                Moazam Shareef

                Thanks for your reply Andreas, but its not working in forms. i used the below code its working in SQL but not in forms can you tell where i'm doing wrong?

                [code]

                SELECT ROWNUM - 1 + TO_DATE ('01-mar-2006', 'dd-mon-yyyy')

                FROM  ALL_TAB_COLUMNS WHERE ROWNUM <=to_date('10-mar-2006', 'dd-mon-yyyy')-to_date('01-mar-2006', 'dd-mon-yyyy')+1

                ROWNUM-1+T

                ----------

                01-03-2006

                02-03-2006

                03-03-2006

                04-03-2006

                05-03-2006

                06-03-2006

                07-03-2006

                08-03-2006

                09-03-2006

                10-03-2006

                 

                 

                the same code i'm using in forms KEY-NEXT-ITEM trigger but its showing the below error.

                ORA-01858.

                INSERT INTO MN_DET_TS(PROCESS_DATES)

                (SELECT ROWNUM - 1 + TO_DATE ('MN_MAS_TS.FROM_DT', 'DD-MON-YYYY')

                FROM  ALL_TAB_COLUMNS WHERE ROWNUM <=to_date('MN_MAS_TS.DATE_TO', 'DD-MON-YYYY')-to_date('MN_MAS_TS.FROM_DT', 'DD-MON-YYYY')+1);

                • 5. Re: From Date to Date
                  Priyasagi

                  See my previous post, without using cursor how can you fetch multiple rows in the form?

                  • 6. Re: From Date to Date
                    Moazam Shareef

                    appreicate your post priya but here i'm not fetching record both master and details table are empty here im inserting dates from_date to_date which is on master form and inserting records into details table column process_dates.

                    • 7. Re: From Date to Date
                      Priyasagi

                      I think your 'MN_MAS_TS.FROM_DT'  and 'MN_MAS_TS.DATE_TO' are already date data type.

                      Do not convert it again as date.

                      • 8. Re: From Date to Date
                        Moazam Shareef

                        You are RITE priya, both are date datatype and process_dates which is in detail table also date datatype just i wanna to generate the dates between FROM_DT and DATE_TO into Details table column PROCESS_DATE as below.

                        MN_MAS_TS.FROM_DT:='01-01-2013'

                        MN_MAS_TS.DATE_TO:='25-01-2013'


                        PROCESS_DATES

                        01-01-2013

                        02-01-2013

                        03-01-2013

                        ....

                        ....

                        25-01-2013

                        • 9. Re: From Date to Date
                          Priyasagi

                          Try this,

                           

                          Key-next-item of MN_MAS_TS.DATE_TO

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

                           

                          INSERT INTO MN_DET_TS(PROCESS_DATES)

                          (
                               SELECT ROWNUM - 1 + :MN_MAS_TS.FROM_DT

                               FROM 

                               ALL_TAB_COLUMNS WHERE ROWNUM <=:MN_MAS_TS.DATE_TO-:MN_MAS_TS.FROM_DT+1

                          );

                           

                          COMMIT_FORM;
                          NEXT_ITEM;

                          • 10. Re: From Date to Date
                            Moazam Shareef

                            Already i tried this as you said, it through error ORA-01400 which is multiple errors. in help 

                            • 11. Re: From Date to Date
                              Priyasagi

                              Its 'Can not insert null'.

                               

                              Check your MN_DET_TS table, if any of the column having not null field then you can not insert single column.

                               

                              For example, if you had a table called suppliers defined as follows:

                              CREATE TABLE suppliers ( supplier_id number not null, supplier_name varchar2(50) not null );

                              And you tried to execute the following INSERT statement:

                              INSERT INTO suppliers ( supplier_id ) VALUES ( 10023 );

                              You would receive the ORA-01400  error message:

                               

                              You have defined the supplier_name column as a NOT NULL field. Yet, you have attempted to insert a NULL value into this field.

                              You could correct this error with the following INSERT statement:

                              INSERT INTO suppliers ( supplier_id, supplier_name ) VALUES ( 10023, 'IBM' );

                              Now, you are inserting a NOT NULL value into the supplier_name column.

                              • 12. Re: From Date to Date
                                Moazam Shareef

                                Yes i re-create table and remove all not null values, records cannot insert into in PROCESS_DATES with the below code, neither showing any error.

                                INSERT INTO MN_DET_TS(PROCESS_DATES)

                                (

                                     SELECT ROWNUM - 1 + :MN_MAS_TS.FROM_DT

                                     FROM

                                     ALL_TAB_COLUMNS WHERE ROWNUM <=:MN_MAS_TS.DATE_TO-:MN_MAS_TS.FROM_DT+1

                                );

                                COMMIT_FORM;

                                next_item;

                                • 13. Re: From Date to Date
                                  Priyasagi

                                  Please close already running forms. Compile again.  Check for your fmx is it updated correctly or not.

                                  And post your table structure.

                                  • 14. Re: From Date to Date
                                    Moazam Shareef

                                    yes fmx already updated after compiling. below are the structure.

                                    SQL> DESC MN_MAS_TS;

                                    Name                            Null?    Type

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

                                    FROM_DT                                  DATE

                                    DATE_TO                                  DATE

                                    CUR_MONTH                                VARCHAR2(12)

                                    EMPID                                    NUMBER(6)

                                    EMP_NAME                                 VARCHAR2(60)

                                    SUB_DATE                                 DATE

                                     

                                     

                                    SQL> DESC MN_DET_TS

                                    Name                            Null?    Type

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

                                    EMPID                                    NUMBER(6)

                                    PROCESS_DATES                            DATE

                                    LOC_AREA                                 VARCHAR2(30)

                                    MOR_IN_TIME                              VARCHAR2(6)

                                    LUNCH_TIME_OUT                           VARCHAR2(6)

                                    LUNCH_TIME_IN                            VARCHAR2(6)

                                    EVE_OUT_TIME                             VARCHAR2(6)

                                    DEPT                                     VARCHAR2(30)

                                    REMARKS                                  VARCHAR2(15)

                                    UPD_DATE                                 DATE

                                    1 2 Previous Next