AnsweredAssumed Answered

Where find achievements in database.

Question asked by jm28915 on Jun 11, 2019
Latest reply on Jun 19, 2019 by jkelley_blackboard

We're trying to pull a list of users who have earned badges (achievements) in our SaaS environment.  However, we are receiving an error that the ach_notification table does not exist.  I'm not certain if other tables/columns are not the same.  We've reviewed the schema for MH which we were told is almost the same as SaaS - - guess this is one of those cases where it isn't. The script we used in Managed Hosting is below.  Can anyone provide assistance with this?

select  u.lastname, .firstname, u.user_ID, max(an.announced_datetime) AS [Date Received],  cc.TITLE, cm.COURSE_NAME,  cc.main_data, cm.COURSE_ID
from ach_notification AS an inner JOIN --only users who got badges
      USERS AS u on an.users_pk1 = u.PK1 inner JOIN --subset of previous line to course contents
      COURSE_CONTENTS AS cc ON cc.PK1 = an.content_pk1 LEFT OUTER JOIN --subset of previous line to course main
      COURSE_MAIN AS cm ON cm.PK1 = cc.CRSMAIN_PK1
where CNTHNDLR_HANDLE = 'resource/x-bb-achievement'
and an.announced_datetime > '2017-07-31'
--and USER_ID = 'awestern'
group by u.lastname, u.firstname, u.user_ID,  cc.TITLE, cm.COURSE_NAME, cc.main_data, cm.COURSE_ID, cm.COURSE_NAME
order by u.lastname, u.firstname, u.user_ID, TITLE; --u.LASTNAME, u.firstname, u.user_ID, ab.badge_name,TITLE;