I've used the following delete query in DBISAM with no problems...

delete from MemoryData.MemTimeOff M inner join TimeKeeper.Timeoff T
  on M.Reasonid = T.ReasonID
  and M.WeekDate = T.WeekDate
where M.EmpID = 0
  and T.EmpID = -:EmpID

Now I'm getting "ElevateDB Error #700 An error was found in the
statement at line 1 and column 37 (Expected end of expression but
instead found inner)" in version 1.06.  So how should I word this query?

--
Gordon Turner
Mycroft Computing
http://www.mycroftcomputing.com

Gordon,

<< Now I'm getting "ElevateDB Error #700 An error was found in the statement
at line 1 and column 37 (Expected end of expression but instead found
inner)" in version 1.06.  So how should I word this query? >>

See here:

http://www.elevatesoft.com/scripts/newsgrp.dll?action=openmsg&group=17&msg=75&page=1#msg75

and here:

http://www.elevatesoft.com/scripts/newsgrp.dll?action=openmsg&group=19&msg=1020&page=1#msg1020

--
Tim Young
Elevate Software
www.elevatesoft.com

A correlated sub-query will not work as it takes two columns to match
records, so I'm guessing the other alternative is to use a procedure.
Last time I tried a procedure there was a problem using the parameter
more than once in the procedure.  Has that problem been resolved?

--
Gordon Turner
Mycroft Computing
http://www.mycroftcomputing.com

Gordon,

<< A correlated sub-query will not work as it takes two columns to match
records, so I'm guessing the other alternative is to use a procedure. >>

Use this:

delete from MemoryData.MemTimeOff M
WHERE M.EmpID = 0 AND
EXISTS (SELECT * FROM TimeKeeper.Timeoff T
WHERE T.ReasonID = M.Reasonid  and T.WeekDate = M.WeekDate and T.EmpID
= -:EmpID)

<< Last time I tried a procedure there was a problem using the parameter
more than once in the procedure.  Has that problem been resolved? >>

Refresh my memory - did you post something here about it ?  If so, if you
could give me a link or the thread title I would appreciate it.

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
>
> << Last time I tried a procedure there was a problem using the parameter
> more than once in the procedure.  Has that problem been resolved? >>
>
> Refresh my memory - did you post something here about it ?  If so, if you
> could give me a link or the thread title I would appreciate it.
>

Parameters in Procedures was the thread from June 7, 2007.

Also, I'm having a problem getting the procedure to actually use the
passed parameter.  So in my procedure I defined a parameter EmpIDNum as
an Integer.  Then the procedure consists of the following:

DECLARE stmt STATEMENT;

EXECUTE IMMEDIATE
  'delete from MemoryData.MemTimeOff';

PREPARE stmt FROM
  'insert into MemoryData.MemTimeOff
   select ReasonID, WeekDate, EmpID, Hours
     from TimeKeeper.TimeOff
    where EmpID = ?';
EXECUTE stmt USING EmpIDNum;

When I execute the procedure (from within EDBManager) and set the value
of EmpIDNum at the prompt, the procedure does not load any rows into the
table.  However, when I execute the statement as a query it returns over
200 rows.

So what am I doing wrong?
--
Gordon Turner
Mycroft Computing
http://www.mycroftcomputing.com

I forgot to note that this is in 1.06 Build 1 Unicode.

--
Gordon Turner
Mycroft Computing
http://www.mycroftcomputing.com

Gordon,

<< Parameters in Procedures was the thread from June 7, 2007. >>

Did you ever send me the data that you were using for the procedure ?  If
not, then it probably was not resolved (at the very least it was not
resolved by using your example).

<< Also, I'm having a problem getting the procedure to actually use the
passed parameter.  So in my procedure I defined a parameter EmpIDNum as an
Integer.  Then the procedure consists of the following: >>

See here:

http://www.elevatesoft.com/scripts/newsgrp.dll?action=openmsg&group=17&msg=696&page=1#msg696

It's an issue with parameterized INSERT statements that use the parameters
in a sub-query.   It will be fixed shortly in 1.06 B2 or 1.07 (we may have
to do a new version due to some changes for the .NET data provider).

--
Tim Young
Elevate Software
www.elevatesoft.com