Enrichment with Table with non existent data

[dipendu_g]

Hello All,

I am getting a list of Recipients from the query in the workflow activity.

What I need to do is take the ISO value of the Recipient Location and Query another external table to fetch some data basis the Location.

i.e if Recipient Location ISOValue = External Table ISOValue, then fetch another column from external table

Here the issue is Recipient Location might have few values, which need not be present in the external table. In those case I need the column value to be blank or null ( Like an external Join )

When I use enrichment, -> Additional Data and Simple Join, it doesn't list those records not present in External Table, ( behaves like a inner join )

What should be the best method to fetch both existent and non existent data ( blank/null) from the external table

Regards,

DG

[Denis_Bozonnet]

Hi @dipendu_g 

In the Enrichment, when you select the type of link, you have the choice to set the cardinality of the link you define, in your case you have to select 0-1 cardinality

Hope this helps,

Thanks

Denis

[dipendu_g]

@Denis_Bozonnet , I had tried that, still its not working. It works like a inner join only

Regards,

DG

[Denis_Bozonnet]

Hi @dipendu_g 

Could you share the screen shot of your Enrichment activity, all screens, something is wrong because it does work all the time. Are you sure the data type of the fields used by the join is the same?

Thanks

Denis

[dipendu_g]

Hi @Denis_Bozonnet ,

What I am facing is now, if I try to add an output column from external table, it works like inner join and if no column is added all the records are shown ( like Outer Join )

I am at total loss, where is the issue

Regards,

DG

[Sukrity_Wadhwa]

Hi @Denis_Bozonnet, 

Could you please help @dipendu_g further here?

Thanks!

[dipendu_g]

@Sukrity_Wadhwa , @Denis_Bozonnet, Can somebody help me here ?

Regards,

DG

[Denis_Bozonnet]

Hi @dipendu_g 

When you execute it, what is the result? Could you set the SQL Logs property at workflow level that will lel you collect the SQL executed for the activity.

I think the issue might be you need to select the field from the enriched link the enriched link should be visible in yellow / orange color, you seem to select the field from another link.

Thanks

Denis

[dipendu_g]

Hi @Denis_Bozonnet ,

When I execute, I get 0 count, if the fields from the enriched link are selected. If no fields are selects are I get all the records ( as expected ) i.e it acts just like a left outer join.

Regarding the color of the link in the enrichment->edit additional data section, its a grey color link, and I am adding the fields from the enriched link itself.

I am using a Simple Join with 0 and 1 Card Link

Regards,

DG

[Denis_Bozonnet]

Hi @dipendu_g 

Please do execute the workflow enabling SQL logs temporary, you'll be able to capture the SQL generated. I suspect, such enrichment is very common and I never saw it not working, maybe require a bit a fiddling but it does work well

What do you see via the SQL logs enabled?

Thanks
Denis