Link to home
Start Free TrialLog in
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
ASKER CERTIFIED SOLUTION
Avatar of chaau
chaau
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pardeshirahul
pardeshirahul

ASKER

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
can sdome one give me the update statement which will update the sequence number column based on the group by query
It is not clear what field you want to update in your update query, and to what value. Can you clarify