And/Or with () doesn't work in Transformer Constraints
Moderators: chulett, rschirm, roy
And/Or with () doesn't work in Transformer Constraints
My Friends this is my problem:
I'm trying to use this constraint in a transformer:
Not(IsNull(Link_Inv_Retraite_VCN.ACT_INITIAL)) And Not(IsNull(Link_Inv_Retraite_VCN.LISS_FINAL)) And( (Substrings(Link_Inv_Retraite_VCN.CATEGORIES,0,2) Matches "07") Or (Substrings(Link_Inv_Retraite_VCN.CATEGORIES,0,2) Matches "15") Or (Substrings(Link_Inv_Retraite_VCN.CATEGORIES,0,2) Matches "16") Or (Substrings(Link_Inv_Retraite_VCN.CATEGORIES,0,2) Matches "19"))
In the execution, when i have a Categories staring with "07" the ligne goes, but when it starts with "15", "16" or"19" the ligne is rejected.
Its like the 'or' condition doesn't work in a global AND ?!!
If you have my dears any idea, thanks for helping.
I'm using DataStage Server V7.5
I'm trying to use this constraint in a transformer:
Not(IsNull(Link_Inv_Retraite_VCN.ACT_INITIAL)) And Not(IsNull(Link_Inv_Retraite_VCN.LISS_FINAL)) And( (Substrings(Link_Inv_Retraite_VCN.CATEGORIES,0,2) Matches "07") Or (Substrings(Link_Inv_Retraite_VCN.CATEGORIES,0,2) Matches "15") Or (Substrings(Link_Inv_Retraite_VCN.CATEGORIES,0,2) Matches "16") Or (Substrings(Link_Inv_Retraite_VCN.CATEGORIES,0,2) Matches "19"))
In the execution, when i have a Categories staring with "07" the ligne goes, but when it starts with "15", "16" or"19" the ligne is rejected.
Its like the 'or' condition doesn't work in a global AND ?!!
If you have my dears any idea, thanks for helping.
I'm using DataStage Server V7.5
One day you requested a help!
You were happy, when you've got a response. Reply, Make others happy![Smile :)](./images/smilies/icon_smile.gif)
You were happy, when you've got a response. Reply, Make others happy
![Smile :)](./images/smilies/icon_smile.gif)
The "substrings" function is not the correct one to use, and in DataStage strings start at position 1. the "Matches" function is also not correct or required here.
Try:
Try:
Code: Select all
Not(IsNull(Link_Inv_Retraite_VCN.ACT_INITIAL)) And Not(IsNull(Link_Inv_Retraite_VCN.LISS_FINAL)) And( (Link_Inv_Retraite_VCN.CATEGORIES[1,2]) = "07") Or (Link_Inv_Retraite_VCN.CATEGORIES[1,2]) = "15") Or (Link_Inv_Retraite_VCN.CATEGORIES[1,2]) = "16") Or (Link_Inv_Retraite_VCN.CATEGORIES[1,2]) = "19"))
This what i Found, its bizard but its real:
When a String start with 0 the ligne goes but when it start whit 1 it don't.
I tried then to convert to an int and the compare but the same conclusion if the Categorie start with 0 it goes with 1 it dont.
this the condition:
Not(IsNull(Link_Inv_Retraite_VCN.ACT_INITIAL)) And Not(IsNull(Link_Inv_Retraite_VCN.LISS_FINAL)) And( (int(Substrings(Link_Inv_Retraite_VCN.CATEGORIES,1,2))=7) Or (int(Substrings(Link_Inv_Retraite_VCN.CATEGORIES,1,2)) =15))
Please help if any Idea!
When a String start with 0 the ligne goes but when it start whit 1 it don't.
I tried then to convert to an int and the compare but the same conclusion if the Categorie start with 0 it goes with 1 it dont.
this the condition:
Not(IsNull(Link_Inv_Retraite_VCN.ACT_INITIAL)) And Not(IsNull(Link_Inv_Retraite_VCN.LISS_FINAL)) And( (int(Substrings(Link_Inv_Retraite_VCN.CATEGORIES,1,2))=7) Or (int(Substrings(Link_Inv_Retraite_VCN.CATEGORIES,1,2)) =15))
Please help if any Idea!
ArndW wrote:The "substrings" function is not the correct one to use, and in DataStage strings start at position 1. the "Matches" function is also not correct or required here.
Try:
Code: Select all
Not(IsNull( ...[/quote]
One day you requested a help!
You were happy, when you've got a response. Reply, Make others happy![Smile :)](./images/smilies/icon_smile.gif)
You were happy, when you've got a response. Reply, Make others happy
![Smile :)](./images/smilies/icon_smile.gif)
Not(IsNull(Link_Inv_Retraite_VCN.ACT_INITIAL)) And Not(IsNull(Link_Inv_Retraite_VCN.LISS_FINAL)) And (Link_Inv_Retraite_VCN.CATEGORIES[0,1]='7' Or Link_Inv_Retraite_VCN.CATEGORIES[0,2]='15')
this should work if the character you are comparing is the first one in the string, otherwise, change start from 0 to 1 in the datastage substr function [start,len]
this should work if the character you are comparing is the first one in the string, otherwise, change start from 0 to 1 in the datastage substr function [start,len]
I would recommend making a VarChar stage variable "svCAT" derived by "Link_Inv_Retraite_VCN.CATEGORIES[1,2]:','"
then the constraint:
This uses the INDEX function instead of a string of ORs
then the constraint:
Code: Select all
NOT(IsNull(Link_Inv_Retraite_VCN.ACT_INITIAL) OR IsNull(Link_Inv_Retraite_VCN.LISS_FINAL)) AND INDEX('07,15,01,16,02,',svCAT,1)
If you specify 0 or a negative value as start argument, the value is assumed to be 1.
Try changing you constraint for this one :
Not(IsNull(Link_Inv_Retraite_VCN.ACT_INITIAL)) And Not(IsNull(Link_Inv_Retraite_VCN.LISS_FINAL)) And ( Link_Inv_Retraite_VCN.CATEGORIES[0,2] = "07" Or Link_Inv_Retraite_VCN.CATEGORIES[0,2] = "15" Or Link_Inv_Retraite_VCN.CATEGORIES[0,2] = "01" Or Link_Inv_Retraite_VCN.CATEGORIES[0,2] = "16" Or Link_Inv_Retraite_VCN.CATEGORIES[0,2] = "02" )
You should give me the fields that are sent to the reject file as I can see the seconf field has no value.
Try changing you constraint for this one :
Not(IsNull(Link_Inv_Retraite_VCN.ACT_INITIAL)) And Not(IsNull(Link_Inv_Retraite_VCN.LISS_FINAL)) And ( Link_Inv_Retraite_VCN.CATEGORIES[0,2] = "07" Or Link_Inv_Retraite_VCN.CATEGORIES[0,2] = "15" Or Link_Inv_Retraite_VCN.CATEGORIES[0,2] = "01" Or Link_Inv_Retraite_VCN.CATEGORIES[0,2] = "16" Or Link_Inv_Retraite_VCN.CATEGORIES[0,2] = "02" )
You should give me the fields that are sent to the reject file as I can see the seconf field has no value.
You can also use the field function which split the string as all of you CATEGORIES starts with a number then a delimiter and a value.
Not(IsNull(Link_Inv_Retraite_VCN.ACT_INITIAL)) And Not(IsNull(Link_Inv_Retraite_VCN.LISS_FINAL)) And (
Field(Link_Inv_Retraite_VCN.CATEGORIES,"-",0) ="07" Or Field(Link_Inv_Retraite_VCN.CATEGORIES,"-",0) = "15" Or Field(Link_Inv_Retraite_VCN.CATEGORIES,"-",0) = "01" Or Field(Link_Inv_Retraite_VCN.CATEGORIES,"-",0) = "16" Or Field(Link_Inv_Retraite_VCN.CATEGORIES,"-",0) = "02" )
Not(IsNull(Link_Inv_Retraite_VCN.ACT_INITIAL)) And Not(IsNull(Link_Inv_Retraite_VCN.LISS_FINAL)) And (
Field(Link_Inv_Retraite_VCN.CATEGORIES,"-",0) ="07" Or Field(Link_Inv_Retraite_VCN.CATEGORIES,"-",0) = "15" Or Field(Link_Inv_Retraite_VCN.CATEGORIES,"-",0) = "01" Or Field(Link_Inv_Retraite_VCN.CATEGORIES,"-",0) = "16" Or Field(Link_Inv_Retraite_VCN.CATEGORIES,"-",0) = "02" )
This solution is better as mine in fact.ArndW wrote:I would recommend making a VarChar stage variable "svCAT" derived by "Link_Inv_Retraite_VCN.CATEGORIES[1,2]:','"
then the constraint:
This uses the INDEX function instead of a string of ORsCode: Select all
NOT(IsNull(Link_Inv_Retraite_VCN.ACT_INITIAL) OR IsNull(Link_Inv_Retraite_VCN.LISS_FINAL)) AND INDEX('07,15,01,16,02,',svCAT,1)
First, your constraint would be easier to read and then you would be able to avoid the or operator.
You can make a stage variable by derivating
Link_Inv_Retraite_VCN.CATEGORIES[1,2]:','
or
field(Link_Inv_Retraite_VCN.CATEGORIES, "-",0):','
Thanks resolved
thanks ArndW and aoriano.
Now it works, the error simply was that all the categories that start with 0 didn' t have this condition returning true : IsNull(Link_Inv_Retraite_VCN.LISS_FINAL)), so this is why it stops, and not because of INDEX('07,15,01,16,02,',svCAT,1).
At least I changed my Constraint for better syntax as you advised me.
Thanks for you help again.
Now it works, the error simply was that all the categories that start with 0 didn' t have this condition returning true : IsNull(Link_Inv_Retraite_VCN.LISS_FINAL)), so this is why it stops, and not because of INDEX('07,15,01,16,02,',svCAT,1).
At least I changed my Constraint for better syntax as you advised me.
Thanks for you help again.
aoriano wrote:This solution is better as mine in fact.ArndW wrote:I would recommend making a VarChar stage variable "svCAT" derived by "Link_Inv_Retraite_VCN.CATEGORIES[1,2]:','"
then the constraint:
This uses the INDEX function instead of a string of ORsCode: Select all
NOT(IsNull(Link_Inv_Retraite_VCN.ACT_INITIAL) OR IsNull(Link_Inv_Retraite_VCN.LISS_FINAL)) AND INDEX('07,15,01,16,02,',svCAT,1)
First, your constraint would be easier to read and then you would be able to avoid the or operator.
You can make a stage variable by derivating
Link_Inv_Retraite_VCN.CATEGORIES[1,2]:','
or
field(Link_Inv_Retraite_VCN.CATEGORIES, "-",0):','
One day you requested a help!
You were happy, when you've got a response. Reply, Make others happy![Smile :)](./images/smilies/icon_smile.gif)
You were happy, when you've got a response. Reply, Make others happy
![Smile :)](./images/smilies/icon_smile.gif)