Integrity check using combination of attributes

Hi ​@JTH, 

You could build the lkp in ONE from some Catalog Item or SQL Catalog Item.

In this case we would need to combine those 2 fields so we can create an sqlCI concatenating those 2 fields from the Address Table:

select street|| '_' || house_number as key

 from "orch"."address"

 where street in ('MDM','ORCH')

Then create and build a lkp from that certain sqlCI with concatenation of those 2 fields.

I used sample data in my example. Below is the lkp and sample values:

 

Then you can create a rule in ONE with 2 attributes street and house_number. Also create a variable concatenating the 2 attributes: street+'_'+house_number

You could use a simple logic whether var value is from the lookup: test_kostas_lkp
 

Test the DQ rule in ONE:

Please let us know if that could work for you.

Thank you 

 Konstantinos

Hi ​@konstantinos.patikas,

Haha I was really hoping to avoid SQL CIs, but I'll give it a try! - thank you!

Btw, can't I just do the same what you did, but instead of looking at a SQL CI lookup I will look at the Catalog Item that's already there?

Hi ​@JTH 

Absolutely, I understand wanting to avoid SQL CIs when possible!

Yes, you can reference the existing Catalog Item directly instead of creating an SQL CI. However, in this case since the key of the look up is a concatenation of 2 fields it wouldn’t be possible to do it while creating the look up with the CI.
You would have to prepare your data set (sqlCI-concatenate the 2 columns) and then create and build the lookup based on the concatenated key.