Fetch the Enum labels in SQL Statements

 When dealing with writing SQL Statements the Enum values and their corresponding names force us to write switch case statements which is time consuming. A shortcut for this method is as follows. 

There is already a table in the D365 Database called SRSANALYSISENUMS this table is used to store the D365 labels for use in the Datawarehouse cubes. By default all the tables that are part of any perspectives are populated in this table. However, for some of our enum we can explicitly populate this table and use the same in SQL. 

Create the following job to populate any given enum into the SRSANALYSISENUMS table. 

    public static void main(Args _args)
    {
        SRSAnalysisEnums analysisEnums;
        DictEnum currentEnum;
        str currentEnumName;
        int valueCount;
        str enumItemName;
        int enumItemValue;
        RecordInsertList records;
        Dialog dialog;
        DialogField  dfEnumName;

        dialog = new Dialog("Please enter the enum name");
        dfEnumName = dialog.addField(extendedTypeStr("Name"));
        dfEnumName.label("Enum name");
        if (dialog.run())
        {
            records = new RecordInsertList(tablenum(SRSAnalysisEnums));
         
            currentEnumName = dfEnumName.value();
            currentEnum = new DictEnum(enumName2Id(currentEnumName));
            if(currentEnum == null)
            {
                throw error(strfmt("Enum with name %1 does not exists.",currentEnumName));
            }
            valueCount = currentEnum.values();
            ttsbegin;
            delete_from analysisEnums where analysisEnums.EnumName == currentEnum.name();
            for(int j = 0; j < valueCount; j++)
            {
                enumItemName = currentEnum.index2Symbol(j);
                enumItemValue = currentEnum.index2Value(j);
             
                select firstfast forupdate EnumName, EnumItemName, EnumItemValue
                from analysisEnums where analysisEnums.EnumName == currentEnum.name() && analysisEnums.EnumItemValue == enumItemValue;

                if (analysisEnums)
                {
                    if (analysisEnums.EnumItemName != enumItemName)
                    {
                        analysisEnums.EnumItemName  = enumItemName;
                        analysisEnums.update();
                    }
                }
                else
                {
                    analysisEnums.EnumName      = currentEnum.name();
                    analysisEnums.EnumItemName  = enumItemName;
                    analysisEnums.EnumItemValue = enumItemValue;
                 
                    records.add(analysisEnums);
                }
            }
            records.insertDatabase();
            ttscommit;

            Info("Completed");
        }
    }

 

Once this job is created and compiled we can run this class for each Enum that we want to be populated. To run the class from the url use the following syntax: 

https://ds-dev-7658e683048e57b40devaos.cloudax.dynamics.com/?cmp=ds01&mi=SysClassRunner&Cls=SRSAnalysisEnumsGenerator

In the above URL the servername and cmp parameter should be replaced to match your environment.

Split CSV values as columns

To split a CSV value in a given column into different individual columns use the following trick. 

Firstly, let us look at the different functions that are involved in this process. 

1. Table Value Constructor: The Value clause can be used to create a table out of values. This clause is used in the insert statement however its interesting to note that it can also be used in joins to create a derived table with fixed values. 

E.g.: In the examples below a derived table Source has been created using literal values. 

select Source.NewReasonType, Source.NewName
from (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion')) 
     AS Source (NewName, NewReasonType)

2. Apply operator: Just like the join operator we have another operator apply which has been introduced in SQL and this operator indicates to the SQL Engine that the clause has to be evaluated for each row

3. JSON_VALUE: This function is used to extract a scalar value from a Json string. The function accepts 2 paramters 1. Json expression and 2. subscript of the node to refer.

E.g.

       

DECLARE @data NVARCHAR(MAX);
SET @data = N'{  
     "info":{    
       "ID":1,  
     "Name":"Akshat",
       "address":{    
         "City":"Gurgaon",  
         "Country":"India"  
       },  
       "Favorite_Subject":["English", "Science"]  
    },  
    "type":"student"  
 }';  
SELECT JSON_VALUE(@data, '$.info.Name') AS 'Name', 
       JSON_VALUE(@data, '$.info.Favorite_Subject[1]') AS 'Favorite Subject';

 

E.g: If Display value contains the dimension values separated by a pipe (|) the below statement will break it into individual dimension columns

 select
JSON_VALUE(JS,'$[0]') Dim1, JSON_VALUE(JS,'$[1]') Dim2, JSON_VALUE(JS,'$[2]') Dim3, JSON_VALUE(JS,'$[3]') Dim4
from DIMENSIONSETENTITY T17
Cross Apply (values ('["' + replace( T17.DISPLAYVALUE ,'|','","')+'"]') ) B(JS)