Natalie,
You can retrieve all of the codes for a code set with the following SQL:
select * from B_CODE_SET cs
join B_CODE_SET_DETAIL csd on csd.CODE_SET_ID = cs.CODE_SET_ID
where cs.NAME = '<codeSetName>'
order by csd.LAST_UPDATE_DATETIME desc
The above query lists the codes in the order they were added/updated (with the latest showing first). If you want to take action on new codes, you would need to compare the LAST_UPDATE_DATETIME against some timestamp (e.g., of when you last checked). However, this won't distinguish between a new code and a modified code.
An alternative approach would be to maintain the code set as a repository, which would allow you to "know" when a specific code was added or just modified. To use a repository as a code set requires defining the attribute to use the Custom Dynamic Drop-down List control and a Bulk Callout validation rule to ensure the value is one that is found in the repository. Another advantage of using a repository for the code set is that you can use Change Notification on that code set repository to actually take action the moment a change or addition is made.
-Brian
------------------------------
Brian Zupke | Senior Technical Support Engineer
Winshuttle North America | 9099009179
------------------------------
Original Message:
Sent: 06-07-2021 13:47
From: Natalie Scott
Subject: SQL View export of specific Cod
Brian:
I am looking to populate a new repository ongoing as new code sets get added. For end users, the code set makes it easier for the user to select a value, however we want to collect additional date for each code set option. I was able to run SQL to get the code set name, etc, but I couldn't figure out how to run it to get all the values in that specific code set. My plan was to create a view export job and have it run based on added or changed codes.
Thanks!
------------------------------
Natalie Scott | Data Governance & Business Process Manager
Gourmet Foods International | 40421 04367
Original Message:
Sent: 06-07-2021 00:31
From: Brian Zupke
Subject: SQL View export of specific Cod
Natalie,
Is the specific information you are looking for properties that have been defined for the code set? You can find examples of SQL to extract man of the data model elements in the Compare Extract scripts that you can find in the Services Framework deployment folder, which should be somewhere under the <drive>:\EWSoftware folder on your application servers.
Are you looking to move a code set to the repository and use it moving forward instead of the code set? If so, then you may be able to just export the code set (which should include the properties) and import it into the new repository.
-Brian
------------------------------
Brian Zupke | Senior Technical Support Engineer
Winshuttle North America | 9099009179
Original Message:
Sent: 06-03-2021 14:52
From: Natalie Scott
Subject: SQL View export of specific Cod
I was planning to populate a repository with some code set data along with some additional information. I can find the code set tables in SQL, but I can't figure out how to get the details from a specific code set, I am only able to retrieve the individual code set details. This probably has more to do in my intro SQL skills and I thought someone else may be doing something similar with code sets and could share their script.
Thanks!
------------------------------
Natalie Scott | Data Governance & Business Process Manager
Gourmet Foods International | 40421 04367
------------------------------