Highlighted
Micro Focus Expert
Micro Focus Expert
152 views

SQL Query - Report on Custom Enumerated Fields and their Values

----
-- For best results (when running in SQL Query window) open 'Tools | Options' and
-- expand 'Query Results | SQL Server' and select 'Results to text' for the default destination
-- (you will need to open a new query window for this to take effect)
----

SET NOCOUNT ON

DECLARE @cf_fid varchar(10)
DECLARE @cf_name varchar(30)
DECLARE @cf_objstr varchar(1000)
DECLARE @disp varchar(100)

DECLARE @enum varchar(100)
DECLARE @stcode varchar(10)

DECLARE cf CURSOR FOR
  SELECT stfieldid, stname, objstid FROM cfields
  WHERE
  STTABLEID = (select ID from CTABLES WHERE STNAME = 'Change') AND -- CRs ONLY; change ALL references to this item if required
  STNAME LIKE 'Usr_%' AND
  STFLDTYPE=2 -- enumerated types ONLY
  ORDER BY STNAME

OPEN cf
FETCH NEXT FROM cf INTO @cf_fid, @cf_name, @cf_objstr
WHILE (@@fetch_status <> -1)
BEGIN
  DECLARE c_str CURSOR FOR
  SELECT ststring FROM ctstrc WHERE strid = @cf_objstr
  OPEN c_str
  FETCH NEXT FROM c_str INTO @disp

  SELECT 'ST ID ', 'ST Name ', 'Display Name'

  WHILE (@@fetch_status <> -1)
  BEGIN
    SELECT @cf_fid 'ST ID', @cf_name 'ST Name', @disp 'Display Name'
    DECLARE c_vals CURSOR FOR
    SELECT ce.stcode, cs.ststring FROM ctstrc cs, cenums ce WHERE cs.strid like ('IDS_ENUM_Change_'+@cf_fid+'%') AND cs.strid = ce.enumstid
    OPEN c_vals
    FETCH NEXT FROM c_vals INTO @stcode, @enum
    SELECT ' ','Enum Code ', 'Enum Value'
    WHILE (@@fetch_status <> -1)
    BEGIN
      SELECT ' ', @stcode 'Enum Code', @enum 'Enum Value'
      FETCH NEXT FROM c_vals INTO @stcode, @enum
    END
    DEALLOCATE c_vals
    FETCH NEXT FROM c_str INTO @disp
  END
  SELECT '----------------------------------------------------------'
  DEALLOCATE c_str
  FETCH NEXT FROM cf INTO @cf_fid, @cf_name, @cf_objstr
END
DEALLOCATE cf

0 Likes
0 Replies
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.