Data Validation - DI - Count Records

[DavidCaliman]

I can use the COUNT function in Data Validation within DI Studio?

What´s the syntax?

I need to abort the job if the table has more than one record.

[Patrick · Accepted solution]

From the top of my head:

The Data Validation Node creates SAS Data Step code and the rules are applied on row level. If so then you could use the automatic variable _N_ in an expression where you abort the job if: _N_>1

[DaveR_SAS]

To follow up on Patrick's suggestion, perhaps this example for a Data Validation transformation will give you an idea of how this transformation can be used:

SAS(R) Data Integration Studio 4.7: User's Guide

[DavidCaliman]

Patrick,

I need abort the job too when the table is empty.

When the table has more than one record is working!

So, the table should have only one record for the job does not fail. (_N_ <> 1 did not work!)

Tks.

[LinusH]

For an empty table, you could use Status Handling instead - where you can specify an action based on if the target table was updated (or not).

[DavidCaliman]

Thanks for the reply, but I want in the data validation transformation.

[LinusH]

Why?

[Patrick]

So you need to abort the job when the table exists but has 0 records (and not "more than 1 record"). That's something else.

Can't test it but doesn't the Data Validation node include a Status Handling tab with one of the default conditions being "Table truncated". You could use this one and if truncated then select action "abort all processes".

SAS(R) Data Integration Studio 4.21: User's Guide

P.S: You should always post the DIS version so we can know what's available to you "out-of-the-box".

[Quentin]

Or just add the check as a user-written code module?

Is this seen as "bad form" in DI Studio land?  I haven't done much with DI studio besides user-written code, so I'm curious if people often mix DI transformation nodes with user-written nodes.

[Patrick]

Best practice is to use user written code only if you can't do it with an existing transformation.

[Quentin]

THanks Patrick.  Rather than hijack this thread, I'll start another one relating to pros and cons of mixing the canned transformations with user written code.