And/Or with () doesn't work in Transformer Constraints

[DsInteg]

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

[ArndW]

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(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")) 

[DsInteg]

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!

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]

[ArndW]

Don't use the Substrings() function. What datatype is "CATEGORIES"? Use either Substr() or the Link.Column[x,y] functionality. You don't need to convert back and forth fro integer.

[aoriano]

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]

[ArndW]

I would recommend making a VarChar stage variable "svCAT" derived by "Link_Inv_Retraite_VCN.CATEGORIES[1,2]:','"

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)

This uses the INDEX function instead of a string of ORs

[aoriano]

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.

[aoriano]

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" )

[aoriano]

I would recommend making a VarChar stage variable "svCAT" derived by "Link_Inv_Retraite_VCN.CATEGORIES[1,2]:','"

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)

This uses the INDEX function instead of a string of ORs

This solution is better as mine in fact.
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):','

[DsInteg]

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.

I would recommend making a VarChar stage variable "svCAT" derived by "Link_Inv_Retraite_VCN.CATEGORIES[1,2]:','"

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)

This uses the INDEX function instead of a string of ORs

This solution is better as mine in fact.
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):','