How to write a business rule in Information Steward to perform this function

[former_member592680]

table.txtHi everyone,

I have a table attached in which I wanted to write a business rule in "Information Steward" to check against this table. The table is rather simple and features two fields only, field #1 and field #2. The rule needs to sort the field #1 and then field #2 and check to see if there is ONLY one constant character X populated over field #2 by field #1. Otherwise, all rows with respective to field #1 are treated invalid and need to be outputted into IS failed report.

I think that a programming logic in this nature can be well written in ABAP programming with syntax function like Loop. But in search of all syntax provided in Information Steward, I can barely find something that can be used to perform the desired check function. Any If IS doesn't work, can BODS do the same? any idea? Thank you.

Yaofei

[virendrakumar_soni · Accepted solution]

you can do a lookup or call a SQL in advance editor mode, keep in mind only one column can be returned. Probably you can write two SQL statements, store the result in temp parameter and then compare.

[former_member592680]

Virendra,

Thank you for your prompt response. I'm new to the information steward advanced rule editor that I would like you to provide further assistance. On the note about calling a SQL, below is what I have written. But it has failed from syntax validation. Can you please take a look? In the following SQL statement, 2CES_SAP_SDA_220 is source system connection name; ZFINT_ABU_CCODE = table name; ABU_PYRECPY=FIELD B Name ; BUKRS=FIELD A Name. CNT= a new field name created to store x number of counts of X over field B. My goal of this SQL statement is to get a table with two fields BUKRS and CNT created. When this step is completed, I can easily write a rule to check if CNT<>1 and error out records when CNT<>1 count. Thank you for your help.

DECLARE
# define variables
$CNT
$CNT= sql ('2CES_SAP_SDA_220', 'SELECT COUNT (ZFINT_ABU_CCODE."ABU_PYRECPY") from ZFINT_ABU_CCODE GROUP BY "BUKRS"');
RETURN TRUE;

[former_member592680]

On the same question, is it possible if we could use SQL function provided within Information Steward? How can we write a SQL statement, i.e. Select table, Select field #1, field #2, Count field #2 by field #1 in information steward rule editor? Thanks.