This is my SQL server syntax, when I put it the EDB, there is an error of
'ElevateDB Error #700 An error was found in the statement at line 31 and column 28 (Expected NULL, Char, GUID, or VarChar expression but instead found'


SET ZBeginday= (SELECT A.xDefaultValue FROM xST_Code A Inner Join xST_Building C ON xDOPBegins =              A.xGUID  WHERE C.xGUID=ZBuildingGuid AND xCodeType = 'DOW' );

RichardWu wrote:

> This is my SQL server syntax, when I put it the EDB, there is an
> error of 'ElevateDB Error #700 An error was found in the statement at
> line 31 and column 28 (Expected NULL, Char, GUID, or VarChar
> expression but instead found'
>
>
> SET ZBeginday= (SELECT A.xDefaultValue FROM xST_Code A Inner Join
> xST_Building C ON xDOPBegins =              A.xGUID  WHERE
> C.xGUID=ZBuildingGuid AND xCodeType = 'DOW' );

Richard,

What do you get when you run just the select statement?

--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

When I run in SQL server,
It's just a single value  selected from a column.



"Michael Riley" wrote:

RichardWu wrote:

> This is my SQL server syntax, when I put it the EDB, there is an
> error of 'ElevateDB Error #700 An error was found in the statement at
> line 31 and column 28 (Expected NULL, Char, GUID, or VarChar
> expression but instead found'
>
>
> SET ZBeginday= (SELECT A.xDefaultValue FROM xST_Code A Inner Join
> xST_Building C ON xDOPBegins =              A.xGUID  WHERE
> C.xGUID=ZBuildingGuid AND xCodeType = 'DOW' );

Richard,

What do you get when you run just the select statement?

--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

I just want to know the right syntax in EDB for
such kind of expression:
set  parameter = (select column from table where *）




"Michael Riley" wrote:

RichardWu wrote:

> This is my SQL server syntax, when I put it the EDB, there is an
> error of 'ElevateDB Error #700 An error was found in the statement at
> line 31 and column 28 (Expected NULL, Char, GUID, or VarChar
> expression but instead found'
>
>
> SET ZBeginday= (SELECT A.xDefaultValue FROM xST_Code A Inner Join
> xST_Building C ON xDOPBegins =              A.xGUID  WHERE
> C.xGUID=ZBuildingGuid AND xCodeType = 'DOW' );

Richard,

What do you get when you run just the select statement?

--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

RichardWu


My first guess would have been the same as Michael's (ie you're getting more than one value back from the subselect), my second would be that the datatype of A.xDefaultValue isn't compatible with ZBeginday, my third is that you have posted one line from a script and it may be that there is a problem elsewhere in the script. Can you post the full script?

Roy Lambert [Team Elevate]

The full script:
And this is the inner part of a cursor, i will pass the ZbuildingGuid values from the outer cursor.

   DECLARE ZBuildingGuid VARCHAR(40);
   DECLARE ZProjectGuid VARCHAR(40);
   DECLARE ZBeginday VARCHAR(10);
   DECLARE ZEndday VARCHAR(10);
   DECLARE ZxHOpBegins VARCHAR(10);
   DECLARE ZxHOpEnds VARCHAR(10);
   DECLARE ZxTempHB VARCHAR(10);
   DECLARE ZxTempCB VARCHAR(10);
   DECLARE ZxOCTSetpoint VARCHAR(10);
   DECLARE ZxOHTSetPoint VARCHAR(10);
   DECLARE ZxUCITSetPoint VARCHAR(10);
   DECLARE ZxUHITSetPoint VARCHAR(10);
   DECLARE ZOIEnthalpy VARCHAR(10);
   DECLARE ZUIEnthalpy VARCHAR(10);
   DECLARE ZAECoefficient VARCHAR(10);
   DECLARE ZASHeatCoefficient VARCHAR(10);
   DECLARE ZBtuton VARCHAR(10);
   DECLARE ZBtu VARCHAR(10);
   DECLARE ZxDescription  VARCHAR(40);
   DECLARE ZSqft FLOAT (8);
   DECLARE ZSqftC FLOAT(8);
   DECLARE ZASCValue VARCHAR(10);
   DECLARE ZAWCValue VARCHAR(10);
   DECLARE ZxWIND VARCHAR(10);

   SET ZBuildingGuid='13020607-3739-1890-1101-00059A3C7800';
   SET ZOIEnthalpy =(SELECT xValue from xST_ASHConstants where xDescription = 'Occupied Indoor Enthalpy at ° F and 60% RH');
   SET ZUIEnthalpy =(SELECT xValue from xST_ASHConstants where xDescription = 'Unoccupied Indoor Enthalpy at ° F and 60% RH');
   SET ZAECoefficient =(SELECT xValue from xST_ASHConstants where xDescription = 'ASHRAE Enthalpy Coefficient');
   SET ZASHeatCoefficient =(SELECT xValue from xST_ASHConstants where xDescription = 'ASHRAE Sensible Heat Coefficient');

   INSERT INTO xST_WeatherCalcs
   (xBuildingGUID, xOccupiedDay, xOccupiedHour, xCoolingHR, xHeatingHR,xOccupiedCoolingTemp, xUnoccupiedCoolingTemp, xOccupiedHeatingTemp,
    xUnoccupiedHeatingTemp,xWEATHERCALC, xWEATHERCALC2,xOCCCoolBTU, xOCCHeatBTU, xUOCCCoolBTU, xUOCCHeatBTU, xCDD, xHDD,xMonth,xDay,xHour)
  VALUES(SELECT ZBuildingGuid,
       CASE WHEN xDayOfWeek BETWEEN ZBeginday AND ZEndday
                          THEN 1 ELSE 0 END AS OccupiedDay,
       CASE WHEN xHour BETWEEN ZxHOpBegins and ZxHOpEnds
                          THEN 1 ELSE 0 END AS OccupiedHour,
       CASE WHEN xDBTemp >= ZxTempCB
                          THEN 1 ELSE 0 END AS CoolingHR,
       CASE WHEN xDBTemp <= ZxTempHB
                          THEN 1 ELSE 0 END AS HEATINGHR,
       CASE WHEN
           (CASE WHEN xDayOfWeek BETWEEN ZBeginday AND ZEndday
                          THEN 1 ELSE 0 END +
            CASE WHEN xHour BETWEEN ZxHOpBegins and ZxHOpEnds
                          THEN 1 ELSE 0 END +
            CASE WHEN xDBTemp >= ZxTempCB
                          THEN 1 ELSE 0 END
                 ) = 3
        THEN ZxOCTSetpoint ELSE 0 END as OccupiedCoolingTemp,
        
   CASE WHEN
           (CASE WHEN xDayOfWeek BETWEEN ZBeginday AND ZEndday
                          THEN 1 ELSE 0 END +
            CASE WHEN xHour BETWEEN ZxHOpBegins and ZxHOpEnds
                          THEN 1 ELSE 0 END ) < 2
        THEN
            CASE WHEN xDBTemp >= ZxTempCB THEN ZxUCITSetPoint ELSE 0 END
        ELSE 0 END as UnoccupiedCoolingTemp,

   CASE WHEN
           (CASE WHEN xDayOfWeek BETWEEN ZBeginday AND ZEndday
                          THEN 1 ELSE 0 END +
            CASE WHEN xHour BETWEEN ZxHOpBegins and ZxHOpEnds
                          THEN 1 ELSE 0 END +
            CASE WHEN xDBTemp >= ZxTempCB
                          THEN 1 ELSE 0 END) = 3
        THEN ZxOHTSetPoint ELSE 0 END as OccupiedHeatingTemp,

   CASE WHEN (CASE WHEN xDayOfWeek BETWEEN ZBeginday AND ZEndday
                          THEN 1 ELSE 0 END +
              CASE WHEN xHour BETWEEN ZxHOpBegins and ZxHOpEnds
                          THEN 1 ELSE 0 END) < 2
        THEN CASE WHEN xDBTemp <= ZxTempHB
                  THEN ZxUHITSetPoint ELSE 0 END
        ELSE 0 END as UnoccupiedHeatingTemp,

   ABS(xDBTemp-(CASE WHEN (CASE WHEN xDayOfWeek BETWEEN ZBeginday AND ZEndday
                                THEN 1 ELSE 0 END +
                           CASE WHEN xHour BETWEEN ZxHOpBegins and ZxHOpEnds
                                THEN 1 ELSE 0 END +
                           CASE WHEN xDBTemp >= ZxTempCB
                                THEN 1 ELSE 0 END
                            ) = 3
                     THEN ZxOCTSetpoint ELSE 0 END +
                  CASE WHEN (CASE WHEN xDayOfWeek BETWEEN ZBeginday AND ZEndday
                                  THEN 1 ELSE 0 END +
                             CASE WHEN xHour BETWEEN ZxHOpBegins and ZxHOpEnds
                                  THEN 1 ELSE 0 END ) < 2
                       THEN
                           CASE WHEN xDBTemp >= ZxTempCB THEN ZxUCITSetPoint ELSE 0 END
                      ELSE 0 END +

                  CASE WHEN (CASE WHEN xDayOfWeek BETWEEN ZBeginday AND ZEndday
                                  THEN 1 ELSE 0 END +
                             CASE WHEN xHour BETWEEN ZxHOpBegins and ZxHOpEnds
                                  THEN 1 ELSE 0 END +
                             CASE WHEN xDBTemp >= ZxTempCB
                                  THEN 1 ELSE 0 END) = 3
                       THEN ZxOHTSetPoint ELSE 0 END +

                 CASE WHEN (CASE WHEN xDayOfWeek BETWEEN ZBeginday AND ZEndday
                                 THEN 1 ELSE 0 END +
                            CASE WHEN xHour BETWEEN ZxHOpBegins and ZxHOpEnds
                                 THEN 1 ELSE 0 END) < 2
                     THEN CASE WHEN xDBTemp <= ZxTempHB
                               THEN ZxUHITSetPoint ELSE 0 END
                     ELSE 0 END)
        ) as WEATHERCALC,
   POWER(xWS,2) as WEATHERCALC2,

   CASE WHEN xDBTemp >= ZxTempCB
        THEN CASE WHEN xEnthalpy > ZOIEnthalpy
                  THEN ZAECoefficient *(xEnthalpy -ZOIEnthalpy)
                  ELSE 0 END
        ELSE 0 END AS OCCCoolBTU,

   CASE WHEN xDBTemp <= ZxTempHB
        THEN ZASHeatCoefficient * (ZxOHTSetPoint-xDBTemp)  
        ELSE 0 END AS OCCHEATBTU,
        
   CASE WHEN xDBTemp >= ZxTempCB
        THEN CASE WHEN xEnthalpy > ZUIEnthalpy
                  THEN ZAECoefficient *(xEnthalpy - ZUIEnthalpy)
                  ELSE 0 END
        ELSE 0 END AS UOCCCoolBTU,
        
   CASE WHEN xDBTemp <= ZxTempHB
        THEN ZAECoefficient * (ZxUHITSetpoint-xDBTemp)
        ELSE 0 END AS UOCCHEATBTU,

   CASE WHEN xDBTemp >= 65
        THEN xDBTemp - 65 ELSE 0 END as CDD,

   CASE WHEN xDBTemp < 65 THEN 65 - xDBTemp
        ELSE 0 END as HDD,

   xMonth,xDay,xHour
   FROM xST_Weather WHERE xProjectGUID=ZProjectGuid);








Roy Lambert wrote:

RichardWu


My first guess would have been the same as Michael's (ie you're getting more than one value back from the subselect), my second would be that the datatype of A.xDefaultValue isn't compatible with ZBeginday, my third is that you have posted one line from a script and it may be that there is a problem elsewhere in the script. Can you post the full script?

Roy Lambert [Team Elevate]

Sorry,I missed some SET part:


The full script should be like this:
And this is the inner part of a cursor, i will pass the ZbuildingGuid values from the outer cursor.

  DECLARE ZBuildingGuid VARCHAR(40);
   DECLARE ZProjectGuid VARCHAR(40);
   DECLARE ZBeginday VARCHAR(10);
   DECLARE ZEndday VARCHAR(10);
   DECLARE ZxHOpBegins VARCHAR(10);
   DECLARE ZxHOpEnds VARCHAR(10);
   DECLARE ZxTempHB VARCHAR(10);
   DECLARE ZxTempCB VARCHAR(10);
   DECLARE ZxOCTSetpoint VARCHAR(10);
   DECLARE ZxOHTSetPoint VARCHAR(10);
   DECLARE ZxUCITSetPoint VARCHAR(10);
   DECLARE ZxUHITSetPoint VARCHAR(10);
   DECLARE ZOIEnthalpy VARCHAR(10);
   DECLARE ZUIEnthalpy VARCHAR(10);
   DECLARE ZAECoefficient VARCHAR(10);
   DECLARE ZASHeatCoefficient VARCHAR(10);
   DECLARE ZBtuton VARCHAR(10);
   DECLARE ZBtu VARCHAR(10);
   DECLARE ZxDescription  VARCHAR(40);
   DECLARE ZSqft FLOAT (8);
   DECLARE ZSqftC FLOAT(8);
   DECLARE ZASCValue VARCHAR(10);
   DECLARE ZAWCValue VARCHAR(10);
   DECLARE ZxWIND VARCHAR(10);

   SET ZBuildingGuid='13020607-3739-1890-1101-00059A3C7800'
   SET ZProjectGuid=(SELECT xProjectGUID  FROM xST_Building WHERE xGUID=ZBuildingGuid);
   SET ZBeginday= (SELECT A.xDefaultValue FROM xST_Code A Inner Join xST_Building C ON xDOPBegins = A.xGUID  WHERE C.xGUID=ZBuildingGuid AND xCodeType = 'DOW' );
   SET ZEndday=  (SELECT A.xDefaultValue FROM xST_Code A Inner Join xST_Building C ON xDOPEnds = A.xGUID  WHERE C.xGUID=ZBuildingGuid AND xCodeType = 'DOW'  );

   SET ZxHOpBegins=(SELECT xHOpBegins FROM xST_Building WHERE xGUID=ZBuildingGuid);
   SET ZxHOpEnds=(SELECT xHOpEnds FROM xST_Building WHERE xGUID=ZBuildingGuid);
   SET ZxTempCB=(SELECT xTempCB FROM xST_Building WHERE xGUID=ZBuildingGuid);
   SET ZxTempHB=(SELECT xTempHB FROM xST_Building WHERE xGUID=ZBuildingGuid);
   SET ZxOCTSetpoint=(SELECT xOCTSetpoint FROM xST_Building WHERE xGUID=ZBuildingGuid);
   SET ZxOHTSetPoint=(SELECT xOHTSetpoint FROM xST_Building WHERE xGUID=ZBuildingGuid);
   SET ZxUCITSetPoint=(SELECT xUCITSetPoint FROM xST_Building WHERE xGUID=ZBuildingGuid);
   SET ZxUHITSetPoint=(SELECT xUHITSetPoint FROM xST_Building WHERE xGUID=ZBuildingGuid);
   SET ZOIEnthalpy =(SELECT xValue from xST_ASHConstants where xDescription = 'Occupied Indoor Enthalpy at ° F and 60% RH');
   SET ZUIEnthalpy =(SELECT xValue from xST_ASHConstants where xDescription = 'Unoccupied Indoor Enthalpy at ° F and 60% RH');
   SET ZAECoefficient =(SELECT xValue from xST_ASHConstants where xDescription = 'ASHRAE Enthalpy Coefficient');
   SET ZASHeatCoefficient =(SELECT xValue from xST_ASHConstants where xDescription = 'ASHRAE Sensible Heat Coefficient');

   INSERT INTO xST_WeatherCalcs
   (xBuildingGUID, xOccupiedDay, xOccupiedHour, xCoolingHR, xHeatingHR,xOccupiedCoolingTemp, xUnoccupiedCoolingTemp, xOccupiedHeatingTemp,
    xUnoccupiedHeatingTemp,xWEATHERCALC, xWEATHERCALC2,xOCCCoolBTU, xOCCHeatBTU, xUOCCCoolBTU, xUOCCHeatBTU, xCDD, xHDD,xMonth,xDay,xHour)

   SELECT ZBuildingGuid,
       CASE WHEN xDayOfWeek BETWEEN ZBeginday AND ZEndday
                          THEN 1 ELSE 0 END AS OccupiedDay,
       CASE WHEN xHour BETWEEN ZxHOpBegins and ZxHOpEnds
                          THEN 1 ELSE 0 END AS OccupiedHour,
       CASE WHEN xDBTemp >= ZxTempCB
                          THEN 1 ELSE 0 END AS CoolingHR,
       CASE WHEN xDBTemp <= ZxTempHB
                          THEN 1 ELSE 0 END AS HEATINGHR,
       CASE WHEN
           (CASE WHEN xDayOfWeek BETWEEN ZBeginday AND ZEndday
                          THEN 1 ELSE 0 END +
            CASE WHEN xHour BETWEEN ZxHOpBegins and ZxHOpEnds
                          THEN 1 ELSE 0 END +
            CASE WHEN xDBTemp >= ZxTempCB
                          THEN 1 ELSE 0 END
                 ) = 3
        THEN ZxOCTSetpoint ELSE 0 END as OccupiedCoolingTemp,
        
   CASE WHEN
           (CASE WHEN xDayOfWeek BETWEEN ZBeginday AND ZEndday
                          THEN 1 ELSE 0 END +
            CASE WHEN xHour BETWEEN ZxHOpBegins and ZxHOpEnds
                          THEN 1 ELSE 0 END ) < 2
        THEN
            CASE WHEN xDBTemp >= ZxTempCB THEN ZxUCITSetPoint ELSE 0 END
        ELSE 0 END as UnoccupiedCoolingTemp,

   CASE WHEN
           (CASE WHEN xDayOfWeek BETWEEN ZBeginday AND ZEndday
                          THEN 1 ELSE 0 END +
            CASE WHEN xHour BETWEEN ZxHOpBegins and ZxHOpEnds
                          THEN 1 ELSE 0 END +
            CASE WHEN xDBTemp >= ZxTempCB
                          THEN 1 ELSE 0 END) = 3
        THEN ZxOHTSetPoint ELSE 0 END as OccupiedHeatingTemp,

   CASE WHEN (CASE WHEN xDayOfWeek BETWEEN ZBeginday AND ZEndday
                          THEN 1 ELSE 0 END +
              CASE WHEN xHour BETWEEN ZxHOpBegins and ZxHOpEnds
                          THEN 1 ELSE 0 END) < 2
        THEN CASE WHEN xDBTemp <= ZxTempHB
                  THEN ZxUHITSetPoint ELSE 0 END
        ELSE 0 END as UnoccupiedHeatingTemp,

   ABS(xDBTemp-(CASE WHEN (CASE WHEN xDayOfWeek BETWEEN ZBeginday AND ZEndday
                                THEN 1 ELSE 0 END +
                           CASE WHEN xHour BETWEEN ZxHOpBegins and ZxHOpEnds
                                THEN 1 ELSE 0 END +
                           CASE WHEN xDBTemp >= ZxTempCB
                                THEN 1 ELSE 0 END
                            ) = 3
                     THEN ZxOCTSetpoint ELSE 0 END +
                  CASE WHEN (CASE WHEN xDayOfWeek BETWEEN ZBeginday AND ZEndday
                                  THEN 1 ELSE 0 END +
                             CASE WHEN xHour BETWEEN ZxHOpBegins and ZxHOpEnds
                                  THEN 1 ELSE 0 END ) < 2
                       THEN
                           CASE WHEN xDBTemp >= ZxTempCB THEN ZxUCITSetPoint ELSE 0 END
                      ELSE 0 END +

                  CASE WHEN (CASE WHEN xDayOfWeek BETWEEN ZBeginday AND ZEndday
                                  THEN 1 ELSE 0 END +
                             CASE WHEN xHour BETWEEN ZxHOpBegins and ZxHOpEnds
                                  THEN 1 ELSE 0 END +
                             CASE WHEN xDBTemp >= ZxTempCB
                                  THEN 1 ELSE 0 END) = 3
                       THEN ZxOHTSetPoint ELSE 0 END +

                 CASE WHEN (CASE WHEN xDayOfWeek BETWEEN ZBeginday AND ZEndday
                                 THEN 1 ELSE 0 END +
                            CASE WHEN xHour BETWEEN ZxHOpBegins and ZxHOpEnds
                                 THEN 1 ELSE 0 END) < 2
                     THEN CASE WHEN xDBTemp <= ZxTempHB
                               THEN ZxUHITSetPoint ELSE 0 END
                     ELSE 0 END)
        ) as WEATHERCALC,
   POWER(xWS,2) as WEATHERCALC2,

   CASE WHEN xDBTemp >= ZxTempCB
        THEN CASE WHEN xEnthalpy > ZOIEnthalpy
                  THEN ZAECoefficient *(xEnthalpy -ZOIEnthalpy)
                  ELSE 0 END
        ELSE 0 END AS OCCCoolBTU,

   CASE WHEN xDBTemp <= ZxTempHB
        THEN ZASHeatCoefficient * (ZxOHTSetPoint-xDBTemp)  
        ELSE 0 END AS OCCHEATBTU,
        
   CASE WHEN xDBTemp >= ZxTempCB
        THEN CASE WHEN xEnthalpy > ZUIEnthalpy
                  THEN ZAECoefficient *(xEnthalpy - ZUIEnthalpy)
                  ELSE 0 END
        ELSE 0 END AS UOCCCoolBTU,
        
   CASE WHEN xDBTemp <= ZxTempHB
        THEN ZAECoefficient * (ZxUHITSetpoint-xDBTemp)
        ELSE 0 END AS UOCCHEATBTU,
        
   CASE WHEN xDBTemp >= 65
        THEN xDBTemp - 65 ELSE 0 END as CDD,

   CASE WHEN xDBTemp < 65 THEN 65 - xDBTemp
        ELSE 0 END as HDD,
        
   xMonth,xDay,xHour                                                                                         
   FROM xST_Weather WHERE xProjectGUID=ZProjectGuid;

I just wonder if this kind of structure will works in EDB
Because if too many joins and subquery in the EDB, it runs very slowly.

RichardWu

My eyeballs can't see anything wrong


However, with that sort of structure, my suggestion would be to use the debugger in EDBManager, set breakpoints at all of the lines where you use SET to make sure you have the right line. Firstly I can never remember to start counting at 1 or 0, then I can't remember wether to include the BEGIN line etc. Its also possible that some things throw the parser off by a line or so.

At least if you set breakpoints you will be able to find out exactly which line is causing the problem.


Another option might be to try the following syntax


EXECUTE IMMEDIATE 'SELECT A.xDefaultValue INTO ? FROM xST_Code A Inner Join xST_Building C ON xDOPBegins =  A.xGUID  WHERE C.xGUID=ZBuildingGuid AND xCodeType = 'DOW'
USING ZBeginday;

No idea if it will help though.

Roy Lambert [Team Elevate]

RichardWu


Another question - what is the definition of A.xDefaultValue - the error message is basically saying you have the wrong data type


Roy Lambert [Team Elevate]