Defining Constraints in Transformer Stage

[yaminids]

Hello there,

I am trying to distribute data in the source table into three target tables based on the values in the source columns. I am trying to accomplish this by defining a constraint in the 'Transformer' Stage. When the source column is of numeric type, I have no problem in defining the constraint but when the source column is of Character type I am not able to define the Constraint. Can anyone explain to me how to define a Constraint on a 'Character' column.

Thanx in advance.
-Yamini

[chulett]

There's nothing different about the way a constraint is defined for character versus numeric data. You code an expression that evaluates to True or False and the row either is passed out through the constrained link or not, respectively.

You may be having an issue with the nature of the data you are trying to contrain on. Why don't you post some examples, samples of 'good' data versus 'bad' data (data that should evaluate as TRUE or FALSE) and what you've tried in the way of a constraint expression so far.

[datastagedummy]

Can you post the text from your constraint link and hopefully you dont have the rejects checked for any of the links above.

[kcbland]

Make sure you use quotes around literal values being compared to character columns. If you use literal values that are numeric and compare them to character or numeric columns, then you're okay.

x is integer
x=10 is okay
x=A is bad
x='A' is okay

x is char
x=10 is okay
x=A is bad
x='A' is okay

[yaminids]

Hi,

These are the conditions I was using to define the Constraints.

DSLink3.Name = 'SUN' (It does'nt work)
DSLink3.Age = 26 (It works fine)

Can you explain me what is the problem.

Thanx in advance.
-Yamini

[kcbland]

Your link.column value either has an case mismatch or leading/trailing spaces, from what I can guess. I suggest you find out the exact contents, and either apply a TRIM and/or UPCASE function where appropriate.

[ketfos]

Hi,
You can use Num function to check
what DSLink3.Name returns?

This will determine if the string is NUMERIC

Ketfos

[ririr]

Try wrapping the TrimB(TrimF(LINK.COLUMNNAME))="SUN"..

[yaminids]

Hello there,

Thank you all for your suggestions. I got the solution for the problem. Actually, there were some leading spaces in the 'Name' column.

-Yamini

[T42]

This is yet another perfect example of why it is important to remember:

No matter what we use, it must be PRECISE. If you want a flexible rule-based comparison, check out QualityStage. DataStage is superior when it is dealing with precise data comparison. Leading/trailing spaces, extra whitespaces, invisible characters, and so on forth, can screw up DataStage's results unless you compenstate for it.

This reminds me of a recent client's issue with transferring data from a mainframe computer that includes NULL as a valid character. It had to be converted ahead of time before it can be read in. Data integrity is as important to deal with these days.

[ranga1970]

Did you try UpCase(trim(UR Input)="SUN"
Becase Datastage case sensitive and space validater

Ranga

[dsxdev]

Hi,

If you are really dealing with a char type column then you cannot trim a char type column,even if you trim a char type column value remains same, padding will still be there. For such column convert it to varchar and then use

Code: Select all

TrimLeadingTrailing(inputcolumn)="Value"

[rasi]

Make it a practice to always trim the strings before doing any validataion.

Cheers

[yaminids]

Hello all,

Thank you very much for your suggestions.
-Yamini