DSXchange

Hi,

I have a dataset with 100 columns. I have about 20 rules to check whether the column is null or blank. Each rule will be using different columns from a source dataset.

for each rule, I need to create 2 files one with empty values and other with non empty values.

I am thinking to create one job and pass required columns (the column name for which I need to check whether it is null or not) to the job, the job will check the null ability (function is same for all columns) and create 2 files needed.

any thought would be appreciated. Let me know for further details. Thanks.

I would use data rules. That way you only need to define the data rule definition once, and bind it onto the various columns, perhaps encapsulating them all in a rule set.

Of course, this requires an Information Analyzer licence.

Thanks Ray.

I am planning to do in DataStage. is there an option to pass if else statement as a parameter to transformer.

In transformer, i have stage variable - svCheck

svCheck - if isnull(link1.columnA) then 'Y' else 'N'

when i tried to do by passing parameter, its not taking.

In Job property - i have defined a parameter Param1 with default value as below.

Param1 - if isnull(link1.columnA) then 'Y' else 'N'

In stage variable i am using the parameter.

svCheck - Param1


Is there an option to execute the Param1 command (i.e if else statement)

Let me know for any other option. Thanks again.

kannan_ayyanar wrote:is there an option to pass if else statement as a parameter to transformer.

All I've got time for right now is the short answer and unfortunately the short answer is "no".

I also don't think there the inbuilt stages have options to accept the entire IF-THEN-CLAUSE as dynamic parameter. As an alternate approach, you can use MODIFY/FILTER STAGE which allows the dynamic columns based processing. Atleast you check the nullability of the parameter column there and set some flag so that the flag is used to create 2 files.

Other alternatives for this scenario:

- You may check the rarely known "Custom stage" option (OR)
- as the worst case, you can create a process to load the dataset to your DB and take the entire dynamic processing on the DB side depending on the DB you use (like Oracle, Teradata all support DYNAMIC SQL concept for these scenarios)

how complex are these rules, and can you change your extract?
seems like a coalesce or similar statement for each rule would work if the rules are binary (eg column rule 1 = if any of these are null, null else 1) and then trip off the rule columns would be easier if you have this much leeway...

I'd use the Data Rules stage in DataStage. It can support rules whose definitions are of the If..Then..Else form (but which must end up generating a true/false (or 0/1) value).