0

I have gone through a similar post in Stack overflow... but my query is :

If my table generates a flag in run time execution,then how can I increase Grp_number(generate run time) every time my flag changes.

my Oracle query:

Select emp_id,
Case when MOD(rownum/3)=1 and rownum>1 then 'Y' else 'N' as flag
from Transaction_table

Desired o/p Data format:

emp_id    Flag    GRP_number
  1        N          1
  2        N          1
  3        N          1
  4        Y          2
  5        N          2
  6        N          2
  7        Y          3
Hugo Yates
  • 2,081
  • 2
  • 26
  • 24
sudhir
  • 1
  • See [this](http://stackoverflow.com/questions/16563581/increment-row-number-when-value-of-field-changes-in-oracle) @sudhir – Skizo-ozᴉʞS ツ Feb 18 '15 at 10:33
  • Hi Skizo, I have gone through that before. But since i am generating my flag run time , i am unable to use that flag column for further calculation of grp_number in the query. – sudhir Feb 18 '15 at 10:37
  • Hi Skizo, I have gone through that before. But since i am generating my flag run time , i am unable to use that flag column for further calculation of grp_number in the query. Below is the query that i have tried for but getting error for INVALID IDENTIFIER 'flag': select emp_id, flag, sum(gap) over (partition by emp_id order by emp_id) GRP_number from ( select emp_id , Case when MOD(rownum,30)=1 and rownum>1 then 'N' else 'Y' end as flag, case when flag= lag(flag) over (partition by emp_id order by emp_id) then 0 else 1 end gap from TRANSACTION_table ) Correct me if i am wrong. – sudhir Feb 18 '15 at 10:47
  • You cannot reference a column in another column in the same select list. You need to use `sub query` to avoid `INVALID IDENTIFIER` error. See my answer. – Lalit Kumar B Feb 18 '15 at 11:03

1 Answers1

0

You cannot reference a column in another column in the same select list. You need to use sub query to avoid INVALID IDENTIFIER error.

Do it like -

WITH DATA AS(
SELECT emp_id,
    CASE
      WHEN MOD(rownum/3)=1
      AND rownum        >1
      THEN 'Y'
      ELSE 'N' AS flag
      FROM Transaction_table
  )
  SELECT emp_id, flag, SUM(gap) over (PARTITION BY person
                                            ORDER BY DAY) grp
     FROM(
          SELECT emp_id, flag,
                  CASE WHEN flag = lag(flag) over (PARTITION BY person
                                                   ORDER BY DAY)
                       THEN 0
                       ELSE 1
                  END gap
             FROM DATA)
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • Thanks Lalit... But since i am assigning flag on rownum the out put is getting varied since all the time the output doesn't come in same order. Is there any way i can assign the flag on any other basis other than rownum for 30 records each. I am not getting the desired output as expected. – sudhir Feb 18 '15 at 11:38
  • You could first ORDER BY in a subquery and then use rownum outside. Just like top-n rows. Something like, `select * from(select emp_id, rownum rn from table order by emp_id)` This query will give you ordered rows. – Lalit Kumar B Feb 18 '15 at 12:24