I have faced a problem of using CASE WHEN and BETWEEN AND.
I need the logic judgement of table b 's column value and then combined with table a's column value.
But it seems BETWEEN AND do not support column name.
Is there any good idea?




SELECT a.xBuildingGUID, CASE WHEN a.xDayOfWeek BETWEEN b.xBeginday AND b.xEndday
                    THEN 1 ELSE 0 END AS OccupiedDay,
     CASE WHEN a.xHour BETWEEN 3 and 5
                    THEN 1 ELSE 0 END AS OccupiedHour
      FROM xST_Weather a LEFT JOIN xST_WeatherCalcsTemp b ON a.xProjectGUID=b.xProjectGUID

RichardWu

As well as simple > or < and nested IFs the other idea that just occurred to me is that there are circumstances where you need to just use the column name and not the table name so try that.

Roy Lambert [Team Elevate]

Richard,

<< I have faced a problem of using CASE WHEN and BETWEEN AND.  I need the
logic judgement of table b 's column value and then combined with table a's
column value.  But it seems BETWEEN AND do not support column name. Is there
any good idea? >>

Are you seeing an error message ?  There shouldn't be any issue using
columns in that manner.

Tim Young
Elevate Software
www.elevatesoft.com

My code is below:

   INSERT INTO xST_WeatherCalcs
   (xGUID,xBuildingGUID, xOccupiedDay)

   SELECT Current_GUID,b.xBuildingGuid,
       CASE WHEN xDayOfWeek BETWEEN xBeginday AND xEndday
                            THEN 1 ELSE 0 END AS OccupiedDay
   FROM xST_Weather a LEFT JOIN xST_WeatherCalcsTEMP b ON a.xProjectGUID=b.xProjectGUID

Error message:
ElevateDB Error #700 An error was found in the statement at line 5 and column 38 (Expected Boolean, SmallInt, Integer, BigInt, Float, Decimal, Interval Year, Interval Day, Interval Hour, Interval Minute, Interval Second, or Interval MSecond expression but instead found "b"."xBeginday")


If I use numbers instead of column name in the BETWEEN... AND,then it will be OK.


 
"Tim Young [Elevate Software]" wrote:

Richard,

<< I have faced a problem of using CASE WHEN and BETWEEN AND.  I need the
logic judgement of table b 's column value and then combined with table a's
column value.  But it seems BETWEEN AND do not support column name. Is there
any good idea? >>

Are you seeing an error message ?  There shouldn't be any issue using
columns in that manner.

Tim Young
Elevate Software
www.elevatesoft.com

Any Solution to this?

RichardWu


From Tim's post <<There shouldn't be any issue using columns in that manner.>>

The error you posted in response to Tim indicates that there is a datatype mismatch but that's as far as anyone can go with the information we have to work with.

If the datatypes are the same you'll have to open a support session with ElevateSoft (remember these are user supported forums) and provide them with a test case - code and data that demonstrates the problem.

Roy Lambert [Team Elevate]

Richard,

<< My code is below: >>

That error is, as Roy indicates, a data type mismatch.  What does the
structure of the tables involved in the query look like ?  You can get the
structure of the tables in the EDB Manager via the Properties page/SQL tab
when the relevant table is selected in the Explorer tree view control on the
left.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com