Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
Delete with Join |
Fri, Nov 16 2007 11:15 AM | Permanent Link |
Gordon Turner | 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 |
Fri, Nov 16 2007 5:39 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.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 |
Sat, Nov 17 2007 9:48 AM | Permanent Link |
Gordon Turner | 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 |
Mon, Nov 19 2007 3:16 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.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 |
Mon, Nov 26 2007 11:45 AM | Permanent Link |
Gordon Turner | 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 |
Mon, Nov 26 2007 12:04 PM | Permanent Link |
Gordon Turner | I forgot to note that this is in 1.06 Build 1 Unicode.
-- Gordon Turner Mycroft Computing http://www.mycroftcomputing.com |
Mon, Nov 26 2007 5:38 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.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 |
This web page was last updated on Thursday, May 23, 2024 at 03:39 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |