Defining Constraints in Transformer Stage
Moderators: chulett, rschirm, roy
Defining Constraints in Transformer Stage
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
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
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 56
- Joined: Thu Feb 13, 2003 6:08 pm
- Location: USA
Re: Defining Constraints in Transformer Stage
Can you post the text from your constraint link and hopefully you dont have the rejects checked for any of the links above.
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
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
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Defining Constraints
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
Thank you all for your suggestions. I got the solution for the problem. Actually, there were some leading spaces in the 'Name' column.
-Yamini
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.
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.
Re: Defining Constraints in Transformer Stage
Did you try UpCase(trim(UR Input)="SUN"
Becase Datastage case sensitive and space validater
Ranga
Becase Datastage case sensitive and space validater
Ranga
RRCHINTALA
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
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"
Happy DataStaging
Defining Constraints in Transformer Stage
Hello all,
Thank you very much for your suggestions.
-Yamini
Thank you very much for your suggestions.
-Yamini