7 Replies Latest reply: Jul 15, 2013 3:04 AM by Bikash S RSS

    invalid relational operator

      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

        • Re: invalid relational operator
          Sokkorn Cheav

          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";
          
          • Re: invalid relational operator
            Vishwaranjan Kumar

            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;

            • Re: invalid relational operator
              Vishwaranjan Kumar

              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;

              • Re: invalid relational operator
                Mohit Sharma

                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;


                  • Re: invalid relational operator

                    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

                  • Re: invalid relational operator

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