Select the record with 0x0 inside the field by using Filter

benny.lbs · Post by **benny.lbs** » Thu Mar 15, 2007 4:14 am

I have a simple testing job as below
Row Generator --> Transformer --> Filter --> Peek

APT_STRING_PADCHAR = 0x0

Row Generator
A: String[1]
B: String[10]

Transformer
C: String[10] = A
D: String[10] = B

Filter
Peek1 <-- C like '%0x0%'
Peek2 <-- D like '%0x0%'

From above design, field C should contain '0x0' inside, however, the filter can not select them out.

Anyone know how to select the record with '0x0' inside the field by using Filter ?

kumar_s · Post by **kumar_s** » Thu Mar 15, 2007 5:18 am

What is the result that you get out of Filter? No output?
Could you pass the input to a Peek stage and post the data here?
Does it one with any other condition other that '0x0'?

jhmckeever · Post by **jhmckeever** » Thu Mar 15, 2007 6:48 am

Despite the help file documenting that you can use LIKE with regular expressions in Filter stages we've never managed to get it to work on 7.1r2

Wouldn't it be easier to do this in the Transformer?
Try using :

Code: Select all

Index( InStream.InColumn, '0X0',1)

HTH,
J.

DSguru2B · Post by **DSguru2B** » Thu Mar 15, 2007 6:50 am

A slight correction jhmckeever, if I may:

Code: Select all

Index( InStream.InColumn, '0X0',1) > 0

in the constraint to filter records that contain the literal '0X0'

jhmckeever · Post by **jhmckeever** » Thu Mar 15, 2007 6:50 am

Oh ... and if you want to use proper regular expressions in your transformer there's the C source code for a Parallel Routine to achieve this <a href="viewtopic.php?t=107882">here</a>.

HTH,
J.

jhmckeever · Post by **jhmckeever** » Thu Mar 15, 2007 6:57 am

Thanks DSguru2B,

Whilst we're on that point, wouldn't a constraint without the >0 work OK?

I thought Booleans were expressed as zero for FALSE and non-zero for TRUE? So a positive Index match would be interpreted as TRUE and a Index non-match would equate to FALSE. Is that wrong?

(I'm hoping this doesn't qualify as a thread hijack!)

Thanks,
J.

DSguru2B · Post by **DSguru2B** » Thu Mar 15, 2007 7:10 am

I am not sure if a any positive number other than 1 would be considered as TRUE as only 1 translates to TRUE. Test it out, let us know

jhmckeever · Post by **jhmckeever** » Thu Mar 15, 2007 8:13 am

DSguru2B, ,

We've got a number of jobs which work this way.

I streamed a VarChar column containing Integers 0 to 100 into a transformed and had two output links with constraints:

Code: Select all

Link1 Constraint: Index (InputCol, '1', 1)   &
Link2 Constraint: Not( Index (InputCol, '1', 1) )

And got the correct results. So, Boolean expressions can be zero (FALSE) or non-zero (TRUE).

I'm ASSUMING this also works for Server - I've only tested on PX.

John.

DSguru2B · Post by **DSguru2B** » Thu Mar 15, 2007 9:17 am

Hmm, something else I learnt today. Thanks for confirming that. I will have to test it out some time.

benny.lbs · Post by **benny.lbs** » Thu Mar 15, 2007 6:45 pm

kumar_s wrote:What is the result that you get out of Filter? No output?
Could you pass the input to a Peek stage and post the data here?
Does it one with any other condition other that '0x0'? ...

The input is using the default generated value, that is
Record 1 : A = a ; B = aaaaaaaaaa
Record 2 : A = b ; B = bbbbbbbbbb
.
.
.

jhmckeever, DSguru2B,

Thanks for your input. I know it is easy to be done in Transformer Constraint, however, what I want is to use "Filter" to achieve the requirement.

kumar_s · Post by **kumar_s** » Fri Mar 16, 2007 2:19 am

Will this work like '%0x%'?
What is the output when you use like '%0x0%'? as expression?

benny.lbs · Post by **benny.lbs** » Fri Mar 16, 2007 3:15 am

kumar_s wrote:Will this work like '%0x%'?
What is the output when you use like '%0x0%'? as expression? ...

It doesn't work. No output for this criteria.

kumar_s · Post by **kumar_s** » Fri Mar 16, 2007 3:17 am

And by the way are you sure that you input data has some content with '0x0'. It seems that your data are generated by row generator.

benny.lbs · Post by **benny.lbs** » Fri Mar 16, 2007 3:26 am

kumar_s wrote:And by the way are you sure that you input data has some content with '0x0'. It seems that your data are generated by row generator. ...

The output definition is Char(10), but the input is Char(1) and PADString = '0x0', so the output should have '0x0' inside.

Am i wrong ?

kumar_s · Post by **kumar_s** » Fri Mar 16, 2007 3:51 am

There you go. The pad char that been specified denoted Null char. You cannot elimitate the padchar in the CHAR field. Else you need to convert it into Varchar. Else use PARCHAR as ' '.