Where Clause

[Anonymous]

Hi,

I have a question regarding to a where clause.

Here is my table

a/a/a

a/a/b

a/e/r

a/q/q

a/u/t

How must look my where clause that i get only back rows which an element is max 1 time in a row.

In the example above the correct result must be a/e/r and a/u/t.

Thank you

[sunny_talwar]

May be this

Where SubField(Field, '/', 1) <> SubField(Field, '/', 2) and SubField(Field, '/', 1) <> SubField(Field, '/', 3) and SubField(Field, '/', 3) <> SubField(Field, '/', 2);

[sunny_talwar]

Here is a sample script using an inline load

Table:

LOAD * INLINE [

    Field

    a/a/a

    a/a/b

    a/e/r

    a/q/q

    a/u/t

]

Where SubField(Field, '/', 1) <> SubField(Field, '/', 2) and SubField(Field, '/', 1) <> SubField(Field, '/', 3) and SubField(Field, '/', 3) <> SubField(Field, '/', 2);

[Anonymous]

Thank you. But it there a solution with and / or?

[sunny_talwar]

Not sure what you mean? I have used and

Where SubField(Field, '/', 1) <> SubField(Field, '/', 2) and SubField(Field, '/', 1) <> SubField(Field, '/', 3) and SubField(Field, '/', 3) <> SubField(Field, '/', 2);

[maxgro]

another approach for variable number of elements

Table:

LOAD Field, SubField(Field, '/') as Word INLINE [

    Field

    a/a/a/a

    a/a/b

    a/e/r

    a/q/q

    a/u/t/t

    a/u/z

];

TableF:

LOAD

     *

WHERE WordCount = WordCountDistinct;

LOAD

     Field,

     Count(Word) as WordCount,

     Count(DISTINCT Word) as WordCountDistinct

Resident Table

Group By Field;