How to translate ID numbers in SMS to their real meaning [SQL]


40
views
0
3 months ago by
If I look at a case in SMS, I may see that the case status is "SURGERY COMPLETE."  Within the database, the status is listed as a number, such as "4."

Where do I find the translations that convert these numeric IDs to their meaning within SMS?
Community: Zimmer Biomet

2 Answers


2
3 months ago by
You can use the sms.enum table to cross reference SMS IDs with the description of what they mean.

I have included an example below. Just make sure to add the specific entity name in your join or you will end up duplicating your data!

SELECT
  b.status, 
  e.description

FROM 
  sms.bill b
  left join sms.enum e on e.enum_id = b.status and entity_name = 'bill_status'​
1
3 months ago by
You also use the sms.enum table to point you to the correct table in SMS.  For example, you leverage the location_type and location_id to point to the correct record.  See the below example:
SELECT
  s.location_type,
  e.description,
  s.location_id,
  coalesce(a.name, sa.last_name, st.name, si.name) as Location_name
FROM 
  sms.stock s
    LEFT JOIN sms.enum e on e.enum_id = s.location_type and e.entity_name = 'location_type'
    LEFT JOIN sms.account a on a.id = s.location_id and s.location_type = 9
    LEFT JOIN sms.sales_associate sa on sa.id = s.location_Id and s.location_type = 8
    LEFT JOIN sms.sales_team st on st.id = s.location_id and s.location_type = 10
    left join sms.site si on si.id = s.location_id and s.location_type = 1
​
Please login to add an answer/comment or follow this question.

Similar posts:
Search »