how to increment the value in the table based on the query

INSERT INTO gecl.CCL_JE_LE_SEQUENCE (Ledger_id,
                                           period_name,
                                           DEFAULT_EFFECTIVE_DATE,
                                           JE_HEADER_ID,
                                           legal_Entity,
                                           CCL_JE_SEQUENCE)
SELECT l.ledger_id, h.period_name, default_effective_date, h.je_header_id, m.segment1, 1
  FROM apps.gl_code_combinations m, apps.gl_je_headers h, apps.gl_je_lines l --,
 --gl_je_categories n
 WHERE     h.je_header_id = l.je_header_id
       AND m.code_combination_id = l.code_combination_id
       AND l.ledger_id = 2947
       --AND h.je_category = n.je_category_name
       AND h.period_name = 'JAN-13'
       order by ledger_id, m.segment1, h.default_effective_date, h.je_header_id
       --AND h.je_category = 1

    /* Formatted on 9/11/2013 5:00:09 PM (QP5 v5.252.13127.32847) */
  SELECT ledger_id,
         period_name,
         default_effective_date,
         je_header_id,
         legal_entity,
         COUNT (*)
    FROM gecl.CCL_JE_LE_SEQUENCE
GROUP BY ledger_id,
         period_name,
         default_effective_date,
         je_header_id,
         legal_entity


in the below query i have done the group by
for each record in have to update the  CCL_JE_SEQUENCE column and it has to increment
like a sequence or a rownum
pardeshirahulAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
the easiest you can do is to use Oracle ROWNUM pseudocolumn. Just replace 1 in your statement with ROWNUM.

INSERT INTO gecl.CCL_JE_LE_SEQUENCE (Ledger_id,
                                           period_name,
                                           DEFAULT_EFFECTIVE_DATE,
                                           JE_HEADER_ID,
                                           legal_Entity,
                                           CCL_JE_SEQUENCE)
SELECT l.ledger_id, h.period_name, default_effective_date, h.je_header_id, m.segment1, ROWNUM
  FROM apps.gl_code_combinations m, apps.gl_je_headers h, apps.gl_je_lines l --,
 --gl_je_categories n
 WHERE     h.je_header_id = l.je_header_id
       AND m.code_combination_id = l.code_combination_id
       AND l.ledger_id = 2947
       --AND h.je_category = n.je_category_name
       AND h.period_name = 'JAN-13'
       order by ledger_id, m.segment1, h.default_effective_date, h.je_header_id
       --AND h.je_category = 1

Open in new window


However, please note that the order of this statement will be undetermined. If you need a controlled row number, you need to use ANSI ROW_NUMBER() OVER() functions. Let me know and I can help you with that
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pardeshirahulAuthor Commented:
the number i have to assign is against this select statement

 SELECT ledger_id,
         period_name,
         default_effective_date,
         je_header_id,
         legal_entity,
         COUNT (*)
    FROM gecl.CCL_JE_LE_SEQUENCE
GROUP BY ledger_id,
         period_name,
         default_effective_date,
         je_header_id,
         legal_entity


it groups the records and for every group i havee to assign the number
0
pardeshirahulAuthor Commented:
can sdome one give me the update statement which will update the sequence number column based on the group by query
0
chaauCommented:
It is not clear what field you want to update in your update query, and to what value. Can you clarify
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.