Its not the problem of datatype.
I just cant declare a parameter and then set a value.

Always error 700. I am totally desperate.
Any good examples or manuals of syntax? I mean not only the  ElevateDB Version 2 SQL Manual .




Roy Lambert wrote:

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]

RichardWu wrote:

> 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,

It appears you are trying to use Microsoft SQL syntax. I use Microsoft
by day and ElevateDB by night. If you are a Microsoft guy, you have to
adjust your thinking when you use ElevateDB.

The SET statement in ElevateDB is different than Microsoft.

You may want to get into the habit of using the "New --> Script" option
when inside ElevateDB. This lets you turn on/off breakpoints within the
script.


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

Try this...

Start with New > Script


SCRIPT
BEGIN

DECLARE X int; -- used to set breakpoint
DECLARE ZBeginday varchar;

EXECUTE IMMEDIATE '
   SELECT A.xDefaultValue into ?
     FROM xST_Code A
Inner Join xST_Building C
       ON xDOPBegins = A.xGUID  
    WHERE C.xGUID = ''13020607-3739-1890-1101-00059A3C7800''
      AND xCodeType = ''DOW''' using ZBeginday;

SET X = 1; -- turn on breakpoint by clicking in left gutter

END

-- When the breackpoint turns green mouse over ZBeginday
-- the value will be displaye in the tool tip

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

RichardWu

>Always error 700. I am totally desperate.
>Any good examples or manuals of syntax? I mean not only the ElevateDB Version 2 SQL Manual .

What's wrong with the examples in the manual and the OLH?

Roy Lambert [Team Elevate]

Richard,

<< 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' >>

You can't assign SQL statement results directly to variables/parameters in
ElevateDB SQL/PSM.

ElevateDB uses a *dynamic* version of SQL/PSM, meaning that SQL statements
are not specified in-line with other flow-control and declarative
statements.  This is done to allow the developer the ability to mix and
match DML with DDL without any weird or unpleasant side-effects.

To do what you want in ElevateDB's SQL/PSM, you would need to do something
like this:

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 = '+QUOTEDSTR('DOW')+' )' USING
ZBeginDay;

The end result of this will be that the single value will be stored in the
ZBeginDay variable.

I would recommend checking out the examples in the manual for these topics:

http://www.elevatesoft.com/manual?action=topics&id=edb2sql&section=sql_psm_statements

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com