CODEWITHSUNDEEP
sqloracle-database
Pravellika
Pravellika

Reputation: 182

how to generate constant date within a group using dbms_random in oracle?

This is my data set.

P_Id I_id I_Name Qty
1   1   Jeans   1
1   5   Salwar  1
2   3   Socks   1

I want to update the created date for this table as follows:

P_Id I_id   I_Name  Qty  C_dt
    1   1   Jeans   1  01-01-2011
    1   5   Salwar  1  01-01-2011
    2   3   Socks   1  02-05-2013

I want the c_dt to remain constant for a particular P_id. I tried using dbms_random but it keeps changing. Query I had used:

update test_ins set c_dt=TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_CHAR(DATE '2000-01-01','J'),TO_CHAR(sysdate,'J'))),'J') where p_id=i;

*i refers to the iteration for every purchase made.

Any help is appreciated. Thanks, Pravellika J

Upvotes: 1

Views: 70

Answers (2)

Pravellika
Pravellika

Reputation: 182

Thank you Gordon, I figured an other way:

    update test_ins a set c_dt=
(
select TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_CHAR(DATE '2000-01-01','J'),TO_CHAR(sysdate,'J'))),'J')
from dual group by a.p_id 
)
where purchase_id=i;

I just added a group by clause to the p_id column in the sub query and it worked like charm :)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270191

How about using a temporary table? The logic would be something like this:

create table temp_dte as 
    select distinct p_id, 
           TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_CHAR(DATE '2000-0101', 'J'), 
                                           TO_CHAR(sysdate, 'J')
                                          )), 'J') as val
    from test_ins;

Then:

update test_ins
    set c_dt = (select val from temp_dte td where test_ins.p_id = td.p_id);

Upvotes: 0

Related Questions