This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Nov 26, 2013 11:28 PM by Moazam Shareef RSS

From Date to Date

Moazam Shareef Explorer
Currently Being Moderated

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 Pro
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

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

  • 6. Re: From Date to Date
    Moazam Shareef Explorer
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points