Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
How to use BETWEEN column_value AND column_value? |
Wed, Oct 9 2013 6:09 PM | Permanent Link |
RichardWu | 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 |
Thu, Oct 10 2013 5:46 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Thu, Oct 10 2013 3:34 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Oct 10 2013 5:48 PM | Permanent Link |
RichardWu | 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 |
Tue, Oct 15 2013 11:15 AM | Permanent Link |
RichardWu | Any Solution to this?
|
Tue, Oct 15 2013 11:32 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Wed, Oct 16 2013 2:55 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |