6 Replies Latest reply on Sep 29, 2014 1:44 PM by TylerNelson

    Undesired result while concatonating in my SQL query

    TylerNelson

      Title

      Undesired result while concatonating in my SQL query

      Post

      Hello! So I have an SQL query that's working perfectly. But I'm trying to modify it slightly and I'm running into some trouble. I'm querying a table called Classes, but the TO is called CLS__Classes. Here's the query that's working:

      ExecuteSQL("
      select ClassID+' '+EffectiveMonth+' '+EffectiveYr
      from CLS__Classes
      where zcClassStatusActive1  =  '1'
      ORDER BY ClassID ASC
      "
      ; ""; "" )

      One line of the result looks something like this:

      C-2193 DEC 2014

      The above is the ClassID the Month abbreviated and the year of a Class. I would like the query result to include the ClassID, Class Code, Month and Year. But the concatenation of the ClassCode in between the ClassID and the Month/Year isn't giving me the desired result. I'm not getting a "?" error. I'm just getting some the Class Code with no other values. Here's the query that's giving me the undesired result:

      ExecuteSQL("
      select ClassID+' '+ClassCode+' '+EffectiveMonth+' '+EffectiveYr
      from CLS__Classes
      where zcClassStatusActive1  =  '1'
      ORDER BY ClassID ASC
      "
      ; ""; "" )

      The result I'm getting is the 3 or 4 digit "ClassCode" sometimes prefixed with a dash. For example:

      -193 or 823

      Is there some reserved word like "Class" or "Code" that I need to wrap in quotations marks to avoid this?

      Stephen