invalid relational operator

[#1]

Hi,

I try to load data with some specific formula, my code is as below, but I receive an error message "invalid relational operator":

LOAD

A,

B,

C,

D,

VALID_FROM,

VALID_TO,

E;

SQL SELECT

A, 

B,

C,

D,

VALID_FROM,

VALID_TO,

E

FROM table

where A='A'

and B='B'

and not isnull("VALID_FROM")

and "VALID_TO">"VALID_FROM" or isnull("VALID_TO")

and isnull(E);

Is there naything wrong with the formula?

Peter

[Sokkorn]

Hi Peter

Let try this way

LOAD
    A,
    B,
    C,
    D,
    VALID_FROM,
    VALID_TO,
    E Where A='A' and B='B' and Not IsNull([VALID_FROM]) and ([VALID_TO]>[VALID_FROM] or IsNull([VALID_TO])) and IsNull(E);
SQL SELECT
    A, 
    B,
    C,
    D,
    VALID_FROM,
    VALID_TO,
    E
FROM DAREO."ITEM_SU_T";

[#3]

try this

LOAD

A,

B,

C,

D,

VALID_FROM,

VALID_TO,

E

where A='A'

and B='B'

and not isnull("VALID_FROM")

and "VALID_TO">"VALID_FROM" or isnull("VALID_TO")

and isnull(E);

SQL SELECT

A, 

B,

C,

D,

VALID_FROM,

VALID_TO,

E

FROM table;

[#4]

try this

LOAD

A,

B,

C,

D,

VALID_FROM,

VALID_TO,

E

where A='A' and B='B' and VALID_FROM <>' ' and num(VALID_TO) > num(VALID_FROM) or VALID_TO=' ' and E=' ';

SQL SELECT

A, 

B,

C,

D,

VALID_FROM,

VALID_TO,

E

FROM table;

[er_mohit]

Try this way

LOAD

A,

B,

C,

D,

VALID_FROM,

VALID_TO,

E

where A='A'

and B='B'

and [VALID_FROM]<>' '

and [VALID_TO]>[VALID_FROM] or isnull([VALID_TO])

and isnull(E);

SQL SELECT

A,

B,

C,

D,

VALID_FROM,

VALID_TO,

E

FROM table;

or this

LOAD

A,

B,

C,

D,

VALID_FROM,

VALID_TO,

E

where A='A'

and B='B'

and [VALID_FROM]<>' '

and [VALID_TO]>[VALID_FROM] or [VALID_TO]=' '

and E=' ';

SQL SELECT

A,

B,

C,

D,

VALID_FROM,

VALID_TO,

E

FROM table;

[#6]

Hi er.mohit,

Thanks but I'm afraid that I may have not made my formula clear, so the 'or' function doesn't work as I want, it loads into a lot of things that I don't need.

What I need to do is to apply the 'or' function only to [VALID_TO], meaning to load only if [VALID_TO] is null, if it's not null then load those [VALID_TO]>[VALID_FROM].

Is it possible to make that happen?

BR/Peter

[kaushiknsolanki · Accepted solution]

Hi,

     Try below Script.

SQL SELECT

A, 

B,

C,

D,

VALID_FROM,

VALID_TO,

E

FROM table

where A='A'

and B='B'

and not isnull(VALID_FROM)

and (VALID_TO>VALID_FROM or isnull(VALID_TO))

and isnull(E);

Regards,

Kaushik Solanki

[#8]

Hi try this..

SET SQL =  “SELECT

  A,

  B,

C,

D,

VALID_FROM,

VALID_TO,

E

From Source table”;

LOAD
*; $(SQL) where
A='A' and B='B'

and not isnull(VALID_FROM)

and VALID_TO > VALID_FROM or isnull(VALID_TO)

and isnull(E);