Case statement in computed content

Hello @Albert de Ruiter,

Yes, sql-like case stamenent can be implemented as computed content. See below a simple example of a computed content property for Catalog Item entity, serving a purpose of defining a status whether it is reviewed by Data Steward or not based on the description property.

Configuration of the computed content entity:

Here is the SQL query:

1select        a.id_i       ,        a.parent_id_i,        a.from_h     ,        a.path_i     ,        a.type_i     ,        case            when catalogitem_description is null then 'PENDING'            else 'READY'         end as status,        concat( 'https://mdm-server-one-fyg1u.worker-01-euc1.prod.ataccama.link/runWorkflow?id=WF1:export_all_instance.ewf&inputId=', a.id_i) as urlfrom        (                select                        ci.$ci_id$           as id_i       ,                        ci.$ci_id$           as parent_id_i,                        ci.$ci_from$         as from_h     ,                        $path(ci.$ci_path$)$ as path_i     ,                        $type()$             as type_i     ,						ci.$ci_name$         as catalogitem_name,						ci.$ci_def$          as catalogitem_description,                        ci.$ci_id$           as catalogitem_gid                from                        $ci$ ci        ) as a

Hope this helps! If you have any questions, let me know in the comments below!

Hi @abir ,

Thanks for your example. I'll give it a try to make it fit our usecase. Likely I will have a follow-up question about joining tables, which works different as compared to regular SQL, because in our usecase the case statement will use logic involving several entities. But I will give it a shot myself first.

Kind regards,

Albert

Hi @Albert de Ruiter 

You’re welcome! And joining multiple entities is also doable. Here is another example where entities are joined together.

1select id_i, parent_id_i, from_h, path_i, type_i, concat('https://ataccama.atlassian.net/jira/secure/CreateIssueDetails!init.jspa?pid=10000&issuetype=10005&labels=AccessRequest&summary=Access%20Request%20for%20',catalogitem_name,'%20from%20',connection_name,'&customfield_10033=',schema_name,'&customfield_10034=',catalogitem_name,'&customfield_10035=',owner,'&customfield_10037=https%3A%2F%2Fone.local.ataccama.pro%2Fcatalog%2Fdata%2FcatalogItem%2F',catalogitem_gid,'%2F&customfield_10038=',connection_name,'&description=Hi%2C%0A%0ACould%20I%20please%20get%20access%20to%20',catalogitem_name,'%20from%20',connection_name,'%3F') as url from (select     ci.$ci_id$ as id_i,    ci.$ci_id$ as parent_id_i,    ci.$ci_from$ as from_h,    $path(ci.$ci_path$)$ as path_i,    $type()$ as type_i,    l.$l_name$ as schema_name,    c.$c_name$ as connection_name,    p.$p_username$ as owner,    ci.$ci_name$ as catalogitem_name,    ci.$ci_id$ as catalogitem_gidfrom     $ci$ ci     left join $l$ l on l.$l_id$ = ci.$ci_pid$     left join $c$ c on c.$c_id$ = ci.connection_ri    left join $p$ p on p.$p_id$ = ci.owner_ri) as a

Hi @abir ,

I wanted to apply your input, but I ran into the following. One of the entities that I need in the computed content is an extension of term. When trying to add this it cannot be selected (not visible in the dropdownbox). I can see term itself, but none of the extensions of term that I created. Is that expected behaviour (version 13.9)? Otherwise I'll contact support.

Kind regards,

Albert

I have contacted Support about the last question. I will update this post when I have more clarity.

HI ​@abir I am trying to implement similar solution. Below are my configurations:  When I create referenced object in catalog item and reference computedContent, it gives error that it cannot be referenced. Do I create arbitrary referend object?

Hi ​@sumisha ,

Indeed you cannot reference to the computed content directly. It should work by following these two steps:

1. In the create an ‘embedded object’ property, where the object is your computed content entity.
2. Create a ‘delegated scalar property’, with ‘Via property’ being the name of the computed content entity (in your case ‘computedContent’) and the ‘Delegate property’ being the name of the property in ‘computedContent’ that you want to apply (so in your case ‘layer').

If you are interested you can refer to an article that I wrote about this topic: Business glossary – computed content (part 4).

Kind regards,

Albert

Thanks Albert for your reply.

Hi Community,

I forgot to update you regarding the follow-up from Ataccama Support, that investigated why the computed content didn't work for my example.

In short, the issue is that one of the entities that I need in the computed content is an extension of term. When trying to add this entity in the computed content screen it cannot be selected (it is not visible in the dropdownbox).

The feedback I received was that it is considered a bug, being fixed in version 15.4.

Kind regards,

Albert

Thank you for the update ​@Albert de Ruiter 😊

Hello, I need further support. I need to join attribute and termInstance but getting error when I bring in attribute of display name from termInstance.

select

        a.id_i       ,

        a.parent_id_i,

        a.from_h     ,

        a.path_i     ,

        a.type_i     ,

        case

            when a.lop like 'DateOfBirth%'   then 'PII'

            else ''

        end as sensitivity

from

        (

                select

                        ci.$ci_id$           as id_i       ,

                        ci.$ci_id$           as parent_id_i,

                        ci.$ci_from$         as from_h     ,

                        $path(ti.$ci_path$)$ as path_i     ,

                        $type()$             as type_i     ,

                        ti.$ti_dn$           as lop

                from

                        $ci$ ci

                        inner join $ti$ ti

                        on ci.$ci_id$ = ti.$ti_pid$

        ) as a

Also trying different approach but giving different error , support here would be appreciated: