OrangeScape Developer Community

A community for Application Developers on OrangeScape's Platform-as-a-Service

Syntax to create sequence number and update the same table based on userid order by date desc

Problem:

when uniqueid in  dimension table is a randomly generated alpha numeric value. objectindex() in dimension2.4 depending on uniqueid of table will not work properly.

If incase you had written logic  to generate sequence number for already existing model and it works fine for new records .

But for already existing records if sequence number is not as expected and if data is huge to manually update.

If in case you need a query to generate and update sequence number for already existing records based on a field and if you had  to re-order the records by another field . You can use the below query.

 

Solution:

So I wrote the following  query with help of syntax from internet

 

 WITH temptable(uniqueid,userid, createdate, objindex)
AS
(

select  uniqueid,userid,createdate,case when count(userid) over (partition by userid) > 1
then row_number()over (partition by userid order by createdate desc) else null end as objindex
 from usertable
)
UPDATE usertable
SET objindex = d.objindex
FROM usertable AS e
JOIN temptable AS d ON e.userid = d.userid and d.createdate=e.createdate and d.uniqueid=e.uniqueid

query to check whether the objindex is properly set:

select  objindex,uniqueid,userid,createdate,case when count(userid) over (partition by userid) > 1
then row_number()over (partition by userid order by createdate desc) else null end as objindex
 from usertable

Views: 306

Comment

You need to be a member of OrangeScape Developer Community to add comments!

Join OrangeScape Developer Community

© 2017   Created by OrangeScape Technologies.   Powered by

Badges  |  Report an Issue  |  Terms of Service