Skip to main content
Solved

Case statement in computed content

Albert de Ruiter
Star Blazer L2
Forum|alt.badge.img+4

Hi community,

We have a usecase to set a property's value automatically: based on several conditions that property gets a value. First we considered creating a desktop plan for this. Then we figured it would be preferrable to have this value set automatically, something that can possibly be accomplished by applying computed content.

We have used computed content before, mainly for concatenating values (like described in Computed Content: Show attribute values based on existing attributes (ataccama.com)). But in this case we would need to define a case statement to define the conditions and determine the value.

So the question is, does someone know if sql-like case stamenent can be implemented as computed content? And if so, can an example be provided?

Thanks for your attention and kind regards,

Albert

Best answer by abir

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.

 

 

Catalog Item when description is null, custom property shows “PENDING”.

 

 

Catalog Item when description is not null, custom property shows “READY”.

 

Configuration of Catalog Item entity in Metadata Model

Configuration of the computed content entity:

Here is the SQL query:

select
        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 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     ,
						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!

View original
Did this topic help you find an answer to your question?

abir
Ataccamer
Forum|alt.badge.img+2
  • abirAtaccamer
  • Ataccamer
  • June 24, 2024

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.

 

 

Catalog Item when description is null, custom property shows “PENDING”.

 

 

Catalog Item when description is not null, custom property shows “READY”.

 

Configuration of Catalog Item entity in Metadata Model

Configuration of the computed content entity:

Here is the SQL query:

select
        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 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     ,
						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!

S
Albert de Ruiter
Cansu
3 people like this
  • S
    SamWrigley
  • Albert de Ruiter
    Albert de Ruiter
  • Cansu
    Cansu
Albert de Ruiter
Star Blazer L2
Forum|alt.badge.img+4

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

abir
1 person likes this
  • abir
    abir
abir
Ataccamer
Forum|alt.badge.img+2
  • abirAtaccamer
  • Ataccamer
  • June 25, 2024

Hi @Albert de Ruiter 

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

select 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_gid
from 
    $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

 

 

Albert de Ruiter
1 person likes this
  • Albert de Ruiter
    Albert de Ruiter
Albert de Ruiter
Star Blazer L2
Forum|alt.badge.img+4

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

Cansu
1 person likes this
  • Cansu
    Cansu
Albert de Ruiter
Star Blazer L2
Forum|alt.badge.img+4

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

S
Cansu
2 people like this
  • S
    SamWrigley
  • Cansu
    Cansu
S
Data Pioneer
Forum|alt.badge.img
  • sumishaData Pioneer
  • Data Pioneer
  • March 28, 2025

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?

 

Albert de Ruiter
Star Blazer L2
Forum|alt.badge.img+4

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

Cansu
S
2 people like this
  • Cansu
    Cansu
  • S
    sumisha
S
Data Pioneer
Forum|alt.badge.img
  • sumishaData Pioneer
  • Data Pioneer
  • March 31, 2025

Thanks Albert for your reply.

Albert de Ruiter
Cansu
2 people like this
  • Albert de Ruiter
    Albert de Ruiter
  • Cansu
    Cansu
Albert de Ruiter
Star Blazer L2
Forum|alt.badge.img+4

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

Cansu
1 person likes this
  • Cansu
    Cansu
Cansu
Community Manager
Forum|alt.badge.img+3
  • CansuCommunity Manager
  • Community Manager
  • March 31, 2025

Thank you for the update ​@Albert de Ruiter 😊

Check out our Quick Start Guide to get started on the community 🙋‍♀️

Reply


Most Liked this week

Community Terms of Use & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings