1 2 Previous Next 18 Replies Latest reply: Nov 14, 2012 12:30 PM by choti Go to original post RSS
      • 15. Re: month table
        sb92075
        choti wrote:
        How can we insert a unique record inside the table. with the current insert script it is inserting duplicate records. please help me to resolve this
        how to decide or determine which of the "duplicate" rows is to be INSERTed?

        if you do NOT SELECT any duplicate values, then they won't be INSERTed.
        • 16. Re: month table
          choti
          when i run below query it is giving error. remove sequence number from select query due distinct clause. I can insert with sequence.nextval. But requirement is to have distinct records with the sequence number
          WITH   extrema     AS
          (
               SELECT     TO_DATE('01.01.2006','DD.MM.YYYY')     AS first_date
               ,     TO_DATE('31.12.2007','DD.MM.YYYY')     AS last_date
               FROM     dual
          )
          ,     all_days     AS
          (
               SELECT     first_date + LEVEL - 1     AS date_key
               FROM     extrema
               CONNECT BY     LEVEL <= last_date + 1 - first_date
          )
          SELECT   Distinct temp_mont_vj_s.nextval          AS month_key
          ,       to_char (EXTRACT(MONTH FROM date_key),'09')
                 ||  TO_CHAR (date_key, 'YYYY')     AS mn_in_year
          ,       to_char (trunc(to_char(date_key,'mm')/to_char(date_key,'Q')),'09')
                 ||  TRIM(to_char(TRUNC(TO_CHAR(date_key, 'Q')),'09'))
                 || TO_CHAR(date_key, 'YYYY')          AS mn_in_quarter_and_year
           ,       to_char(EXTRACT(MONTH FROM date_key),'09')
                                        AS month_number
          ,       TO_CHAR(date_key , 'MONTH')          AS month_name
          ,       TO_CHAR ( 1 + MONTHS_BETWEEN ( TRUNC (date_key, 'MONTH')
                                             , TRUNC (date_key, 'Q')
                                     )
                      , 'FM00'
                      )
                ||  TO_CHAR ( date_key
                              , '"0"Q'
                      )                    AS mn_in_quarter
          FROM     all_days ;
          Edited by: choti on Nov 14, 2012 10:14 AM

          Edited by: choti on Nov 14, 2012 10:14 AM
          • 17. Re: month table
            Frank Kulash
            Hi,
            choti wrote:
            How can we insert a unique record inside the table. with the current insert script it is inserting duplicate records. please help me to resolve this
            The query I posted earlier always generates unique rows when I run it. Post a complete test script that shows the problem.

            That query produces one row per day. If you only want one row per month, then use:
            WITH   extrema     AS
            (
                 SELECT     TO_DATE('01.01.2006','DD.MM.YYYY')     AS first_date
                 ,     TO_DATE('31.12.2007','DD.MM.YYYY')     AS last_date
                 FROM     dual
            )
            ,     all_days     AS
            (
                 SELECT     ADD_MONTHS (first_date, LEVEL - 1)     AS date_key
                 FROM     extrema
                 CONNECT BY     LEVEL <= 1 + MONTHS_BETWEEN ( TRUNC (last_date,  'MONTH')
                                                         , TRUNC (first_date, 'MONTH')
                                              )
            )
            SELECT    temp_mont_vj_s.nextval          AS month_key
            ,       TO_CHAR (date_key, 'MMYYYY')          AS mn_in_year
            ,       to_char (trunc(to_char(date_key,'mm')/to_char(date_key,'Q')),'09')
                   ||  TRIM(to_char(TRUNC(TO_CHAR(date_key, 'Q')),'09'))
                   || TO_CHAR(date_key, 'YYYY')          AS mn_in_quarter_and_year
             ,       TO_CHAR (date_key, 'MM')          AS month_number
            ,       TO_CHAR(date_key , 'MONTH')          AS month_name
            ,       TO_CHAR ( 1 + MONTHS_BETWEEN ( TRUNC (date_key, 'MONTH')
                                               , TRUNC (date_key, 'Q')
                                       )
                        , 'FM00'
                        )
                  ||  TO_CHAR ( date_key
                                , '"0"Q'
                        )                    AS mn_in_quarter
            FROM     all_days
            ;
            If you're using the query I posted to INSERT new rows into an existing table, but you're not sure if a row for that month already is in the table or not, then add a WHERE clause like this to the main query:
            WHERE     NOT EXISTS  (
                                  SELECT  1
                           FROM     temp_month_vj
                           WHERE     mn_in_year     = TO_CHAR (all_days.date_key, 'MMYYYY')
                              )
            This will leave any existing rows as they were, and only add rows for months not already in the table. If you want to change existing rows, then use MERGE instead of INSERT.
            • 18. Re: month table
              choti
              Thank you sir.
              1 2 Previous Next