WCS Member DB tables

— Get total count of registered and Guest user count

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') as "Report as of",(select count(*)
from users where registertype='G') as "Guest User Count",(select count(*) from users
where registertype='R') as "Registered User Count" from dual;

— Order or cart count site wider based on user registration type

select u.registertype, o.status, count(*) as "Order/Cart Count" from orders o,
users u where o.member_id = u.users_id group by u.registertype, o.status
 order by registertype, status;

— Get address details by login id

select * from ADDRESS where member_id in (select users_id from userreg where logonid
 like '%LOGIN_ID>%') and status='P';

— Copying password in case of a locked out account
— This is a simple strategy to regenerate password from a working account

UPDATE userreg SET passwordexpired=0,status=1,passwordcreation=SYSDATE, 
logonpassword=(SELECT logonpassword FROM userreg WHERE logonid='<WORKING_ACCOUNT>'), 
salt=(SELECT salt FROM userreg WHERE logonid='<WORKING_ACCOUNT>') 
WHERE logonid='<LOCKED_OUT_USERID>';

ref : IBM infocenter

Leave a Reply