5 Replies Latest reply: Jan 14, 2018 2:12 PM by Massimo Grossi

Where Clause

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

• Re: Where Clause

May be this

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

• Re: Where Clause

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

• Re: Where Clause

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

• Re: Where Clause

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

• Re: Where Clause

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;