Creating a check "At least"

3970
11
Jump to solution
07-07-2015 04:47 AM
gretacarrete
New Contributor II

Hello,

I am trying to switch to Data Reviewer from lots of model builder tools. One of the checks I have to do on my GDB is that the table x if it has at least one row it has to have the attribute RN (a subtype) and if it has several rows AT LEAST one has to have the RN attibute.

Any help will be very welcomed.

Thank you very much,

Greta

Tags (1)
0 Kudos
1 Solution

Accepted Solutions
MichelleJohnson
Esri Contributor

What type of database is your geodatabase?  File, SQL Server, or Oracle...?  I think you can do this check in Execute SQL.  Depending on the geodatabase type, the syntax may be different.

Here's an example of the statement for a file geodatabase.

Not Exists (Select * from <table> where <field name> = 'RN')

If you don't have at least one RN, it will return all records in the table/feature class.

Cheers,

michellej.

View solution in original post

11 Replies
JenniferMcCall4
Occasional Contributor III

Hi Greta,

I believe the only way to do this is through a custom check.

0 Kudos
gretacarrete
New Contributor II

Hi Jennifer McCall, thanks for the answer. Although it is a little bit disapointing I was wondering if there are good tutorials on learning how to create custom checks. I am afraid I don't know what a dll is and it looks like it is the basis of a custom check.

Any help would be appreciated.

Thanks,

G

0 Kudos
JenniferMcCall4
Occasional Contributor III

There are some good custom check code samples in vb.net and c# that you can modify to suit your needs.  You can find the code and instructions here http://www.arcgis.com/home/item.html?id=3a73e1090dae409ea85dff868380cb6f.

Demo 3 in this video also walks through the steps once the code is written Data Quality Control Using ArcGIS Data Reviewer | Esri Canada.  A dll is a file that is created when you compile the code that you write.  This video will show you how to get and use it.

Data Reviewer tends to look at each record at once, as opposed to all records to see if there is "at least" one value in any of the records.  This is a limitation when trying to do what you are attempting.  A custom check would allow If Else statements so you can run the code on the table if the condition is met.  I guess the way to do this would be to see if the RN value exists, if it doesn't the code would run to return the rest of the records as errors.

Cheers,

Jenn

gretacarrete
New Contributor II

Hi Jenn, thanks a lot for all your suggestions. I am a little bit surprised there isn't more extended documentation to develop custom checks, I guess the ArcObject ressources are helpful (so far I had only used them for Add-Ins).

It is good to know that it is posible to include conditional checks with dll, it encourages me to take this challenge.

Thanks again.

G

0 Kudos
MichelleJohnson
Esri Contributor

What type of database is your geodatabase?  File, SQL Server, or Oracle...?  I think you can do this check in Execute SQL.  Depending on the geodatabase type, the syntax may be different.

Here's an example of the statement for a file geodatabase.

Not Exists (Select * from <table> where <field name> = 'RN')

If you don't have at least one RN, it will return all records in the table/feature class.

Cheers,

michellej.

gretacarrete
New Contributor II

Hi Michelle,

Thanks for your suggestion, that SQL statement should be written within a custom check I guess...

The batch of checks I am creating should be able to work with both an SQL GDB and a File GDB, but it is not big deal if they have to be duplicated and the user chooses the batch depending on the GDB that will be used.

Cheers,

Greta

0 Kudos
MichelleJohnson
Esri Contributor

The SQL statement can go in the Execute SQL check and would not require a custom check.

I had a project where i had to support data validation for a file and SQL server database. What i ended up doing was having one batch job for the checks that work in both. And two separate batch jobs, one for file and one for SQL server. That way when i had to update or add checks that would work in both, i only had to do it once.

Happy Connecting. Sent from my Sprint Samsung Galaxy S® 5

0 Kudos
gretacarrete
New Contributor II

THANK YOU SO MUCH! It works perfectly! That is a powerful tool!!

gretacarrete
New Contributor II

Hi Michelle Johnson​ I was hoping you could help me out with another situation. Another check is that in the Table_A for the field "amount" I need to check that if the value is more than 0 then the value is not repeated in all the rows.

I have come up with this:

SELECT * FROM Table_A

WHERE amount > 0

GROUP BY amount

HAVING COUNT (amount DISTINCT) > 1

which... of course... is an invalid expression...

I would very much appreciate any help in this matter.

Thank you very much,

Greta

0 Kudos