Avatar of pardeshirahul
pardeshirahul asked on

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
Oracle Database

Avatar of undefined
Last Comment
chaau

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
chaau

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
pardeshirahul

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
ASKER
pardeshirahul

can sdome one give me the update statement which will update the sequence number column based on the group by query
chaau

It is not clear what field you want to update in your update query, and to what value. Can you clarify
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23