1 Reply Latest reply: Sep 3, 2013 3:52 PM by Michael Solomovich RSS

    SET function used in where clause

    Steve Zagzebski

      I am trying to set a variable: 

      SET JANRETENSION = B201;

      (I tried it will as SET JANRETENSION = 'B201' as well and it didn't work either)

       

      But when I use it in my where clause it is not recognized.

       

      Here is the script followed by the error message:

       

      ProductionByDeptByCust:

      Load
      [Dept] as [Temp Dept],
      sum([Commission]) as [Temp Commission Dept]
      Resident Production
      Where
      ProdGLMonthOrig <= $(JANRETENSION)

      and

      ProdGLMonthOrig >= $(JANRETENSION)

      and

      Dept<>'4'

      Group By [Dept]
      Order by Dept ASC;

       

       

       

      Error MESSAGE I am getting:

       

      Field not found - <B201>

      ProductionByDeptByCust:

      Load

      [Dept] as [Temp Dept],

      sum([Commission]) as [Temp Commission Dept]

      Resident Production

      Where

      ProdGLMonthOrig <= B201

      and

      ProdGLMonthOrig >= B201

      and

      Dept<>'4'

      Group By [Dept]

      Order by Dept ASC