index constraint

[times29]

Hi,
I want to filter any records which has ___Req% or ___Joi%
i am putting constraint in transformer its not working

Index((to_transf._ID), "___Req",1)
or Index((to_transf._ID), "___Joi",1) = 0

Thanks

[pandeesh]

It seems you need to concatenate the search string like this:

Code: Select all

"_" : "Joi"

"_" : "Reqi"

[chulett]

For any kind of "starts with" check, all you need to do is substring.

Code: Select all

to_transf._ID[1,6] = "___Req"

[pandeesh]

oh!i got it wrong.. i thought that he is searching for the string which contains '_' as a character in that string..

[ray.wurlod]

You missed the value comparison for the first Index() function. And you probably want "And" rather than "Or" if you want to exclude these records (it's not clear what you mean by "filter").

Code: Select all

Index((to_transf._ID), "___Req",1) = 0 And Index((to_transf._ID), "___Joi",1) = 0

If you want to include only these records, change the expression appropriately.

Code: Select all

Index((to_transf._ID), "___Req",1) <> 0 Or Index((to_transf._ID), "___Joi",1) <> 0

[times29]

I want to do sumthing like this in sql so that records for _Req and _Joi not pass.i tried and suggested by ray its not working

SELECT *
FROM aa
where "ID" NOT like '___Req%' AND
"ID" NOT like '___Joi%'

[nbalegar]

You want to do it in sql or in datastage. If in datastage means you can use the filter stage which accepts the same format as you are using in the example above.

[suse_dk]

Put this in a stage variable and use the result to define the constraint.

If Index('___Joi|___Roi', to_transf._ID[1,6], 1) Then 1 Else 0