reject rows thru column derivation

[DSbox61]

Hi folks,

i have a job where in transformer stage i'm checking for a few conditions thru stage variables and If then Else statements. At one point after checking many conditions for one particular column...i have to reject rows tht do not meet the conditions. how can i reject rows thru stage variables or column derivations in Transformer stage. i'm not using any constraints.

DB2==>Tx==>DB2

I saw an option in column derivations, under Link Variables which says REJECTED....but how to make use of it? i can add a sequential file stage to send all rejected rows out of transformer stage.

please help me out guyz...
thnx..

[kumar_s]

You can assign the condition check in the derivation of the stage variable. And the same variable can be used for constraints. The stage variable is not mandate, just for better understandability.
Once you created a constraints, those rows that doesn't match to the given constraints will be rejected using REJECT constraints. You can capture those records using sequential file.

[ray.wurlod]

A Transformer stage with a single output link can not process rejected rows. You need at the very least two outputs. Otherwise all you can do is to drop the invalid rows.

The last-executed output link can be an Otherwise/Log link, in which case it handles any row that has not met the output constraint criteria of any of the previously-executed output links. Or it can be a Reject link (right click on the link and choose "Convert to Reject" - it changes the line to long dashes) which handles any input row that might have caused an exception.

[sudeepmantri]

Hi, is this a server Job? or a parallel job. If it is a parallel job, then I am afraid u got to use a filter or anything else. Cause I dont think parallel transformer has got capacity to reject the rows(Though they can drop the rows which do not meet their constraints). Server jobs has got special property known as "Reject Row" in the constraint dialogue box. Say Link1 is ur reject n link 2 is output link. Mention the constraint in Link 2 and set the "Reject row" check box in link1. Note that the reject link(s) must be last in the execution order. Any data on rows not written to any other output link in the stage is then written to the reject link(s), using the column mappings you have specified.

A reject link can be defined in the Transformer Stage Constraints dialog box. Choose Yes in the Reject Row field and setting the Constraint field as follows:

To catch rows which are rejected from a specific output link, set the Constraint field to linkname.REJECTED. This will be set whenever a row is rejected on the linkname link, whether because the row fails to match a constraint on that output link, or because a write opera tion on the target fails for that row. Note that such a reject link should occur after the output link from which it is defined to catch rejects.

To catch rows which caused a write failures on an output link, set the Constraint field to linkname.REJECTEDCODE. The value of linkname.REJECTEDCODE will be non-zero if the row was rejected due to a write failure or 0 (DSE.NOERROR) if the row was rejected due to the link constraint not being met. When editing the Constraint field, you can set return values for linkname.REJECTEDCODE by selecting from the Expression Editor Link Variables > Constants... menu options. These give a range of errors, but note that most write errors return DSE.WRITERROR. In order to set a reject constraint which differentiates between a write failure and a constraint not being met, a combination of the linkname.REJECTEDCODE and linkname.REJECTED flags can be used. For example:

To catch rows which have failed to be written to an output link, set the Constraint field to linkname.REJECTEDCODE

To catch rows which do not meet a constraint on an output link, set the Constraint field to linkname.REJECTEDCODE = DSE.NOERROR AND linkname.REJECTED

To catch rows which have been rejected due a a constraint or write error, set the Constraint field to linkname.REJECTED

As a "catch all", the Constraint field can be left blank. This indicates that this reject link will catch all rows which have not been successfully written to any of the output links processed up to this point. Therefore, the reject link should be the last link in the defined processing order.

Any other Constraint can be defined. This will result in the number of rows written to that link (i.e. rows which satisfy the constraint) to be recorded in the job log as "rejected rows".

Note: Due to the nature of the "catch all" case above, you should only use one reject link whose Constraint field is blank. To use multiple reject links, you should define them to use the linkname.REJECTED flag detailed in the first case above.

[DSguru2B]

I saw an option in column derivations, under Link Variables which says REJECTED....

That statement assures that it is, infact, a server job type and not px. He/she just posted in the wrong forum.

[DSbox61]

hi ppl,

thnx for your answers....but i need more specific than using a REJECTED option in constraint coz the condition i'm using in stage variable is like this:

If DSLink3.SRV_ARE_RST_MDL_ID=46162 Then DSLink3.VEND_PACK_ID Else (If currentUPC <> lastUPC Then DSLink3.VEND_PACK_ID Else ___)

The rows coming under second ELSE condition are the ones i need to reject.

thnx...

[DSguru2B]

Specify a stage variable say 'cond'. Its derivation will be

Code: Select all

If (DSLink3.SRV_ARE_RST_MDL_ID=46162) OR (currentUPC <> lastUPC) then 'X' else 'Y'

In the constraint of the reject link, specify

Code: Select all

cond = 'Y'

For the regular link provide constraint

Code: Select all

cond = 'X'

[ray.wurlod]

Parallel Transformer jobs DO support reject links - see my earlier post. But the OP probably requires an Otherwise/Log output link. But it is true that the parallel Transformer stage does not have link variables available in its expressions.