![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
![]() |
Tue, Aug 26 2008 10:26 AM | Permanent Link |
Marcin | Hello,
Trying to execute a query that will insert records from one table to another in stored procedure . PROCEDURE "usp_A2DBUpdateItemCount" (IN "AClientId" INTEGER, IN "AnAuditId" INTEGER, IN "AModuleName" VARCHAR(64) COLLATE ANSI) BEGIN DECLARE Cur CURSOR FOR Stmt; DECLARE TmpCount INTEGER; PREPARE Stmt FROM ' INSERT INTO ' + AModuleName + ' (ClientID, FoundAudit, FKTableName, FKTableID, Count) VALUES ( SELECT RSLT.ClientID, RSLT.FoundAudit, RSLT.FKTableName, RSLT.FKTableID, RSLT.Count FROM ( SELECT ' + CAST(AClientId AS VARCHAR) + ' AS ClientID, ' + CAST(AnAuditId AS VARCHAR) + ' AS FoundAudit, TC.FKTableName AS FKTableName, TC.FKTableID AS FKTableID, TC.Count AS "Count" FROM TmpCount AS TC WHERE TC.FKTableName + CAST( TC.FKTableID AS VARCHAR(50) ) NOT IN ( SELECT HW.FKTableName + CAST( HW.FKTableID AS VARCHAR(50) ) FROM ' + AModuleName + ' AS HW WHERE ( HW.ClientID = ' + CAST(AClientId AS VARCHAR) + ' ) AND ( HW.RemoveAudit IS NULL ) ) ) AS RSLT )'; EXECUTE Stmt; END Above results in "scalar query can only return a single value" error. I can execute select query in ElevateDB manager and it works fine. Where is the error? Thanks Marcin. |
Tue, Aug 26 2008 2:53 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Marcin,
<< Above results in "scalar query can only return a single value" error. I can execute select query in ElevateDB manager and it works fine. Where is the error? >> Which version of EDB are you using ? There were some issues with derived tables and this error message prior to B4. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Aug 27 2008 3:27 AM | Permanent Link |
Marcin | "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:
Which version of EDB are you using ? There were some issues with derived tables and this error message prior to B4. I am using ElevateDB 2.01 Build 4. |
Wed, Aug 27 2008 1:12 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Marcin,
<< I am using ElevateDB 2.01 Build 4. >> You said that the SELECT works fine in the EDB Manager - can you execute the INSERT with the SELECT okay in the EDB Manager, or is that when you see the error ? -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Aug 28 2008 5:15 AM | Permanent Link |
Marcin | >> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:
>> You said that the SELECT works fine in the EDB Manager - can you execute the >> INSERT with the SELECT okay in the EDB Manager, or is that when you see the >> error ? Tim, The INSERT is not working in EDB Manager and in my program with the same error. I Fixed the code by using cursor for the select and insert in loop with that cursor. I think it is less efficient but it works. Marcin. |
Thu, Aug 28 2008 4:36 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Marcin,
<< The INSERT is not working in EDB Manager and in my program with the same error. I Fixed the code by using cursor for the select and insert in loop with that cursor. I think it is less efficient but it works. >> Okay, I'll check this out and see what I can find. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Sep 11 2008 8:37 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Marcin,
<< The INSERT is not working in EDB Manager and in my program with the same error. I Fixed the code by using cursor for the select and insert in loop with that cursor. I think it is less efficient but it works. >> Okay, I finally got a chance to look at this, and the problem is in your SQL. I completely missed that you included a VALUES clause, which is the mistake. Your INSERT statement should look like this instead: PROCEDURE "usp_A2DBUpdateItemCount" (IN "AClientId" INTEGER, IN "AnAuditId" INTEGER, IN "AModuleName" VARCHAR(64) COLLATE ANSI) BEGIN DECLARE Cur CURSOR FOR Stmt; DECLARE TmpCount INTEGER; PREPARE Stmt FROM ' INSERT INTO ' + AModuleName + ' (ClientID, FoundAudit, FKTableName, FKTableID, Count) SELECT RSLT.ClientID, RSLT.FoundAudit, RSLT.FKTableName, RSLT.FKTableID, RSLT.Count FROM ( SELECT ' + CAST(AClientId AS VARCHAR) + ' AS ClientID, ' + CAST(AnAuditId AS VARCHAR) + ' AS FoundAudit, TC.FKTableName AS FKTableName, TC.FKTableID AS FKTableID, TC.Count AS "Count" FROM TmpCount AS TC WHERE TC.FKTableName + CAST( TC.FKTableID AS VARCHAR(50) ) NOT IN ( SELECT HW.FKTableName + CAST( HW.FKTableID AS VARCHAR(50) ) FROM ' + AModuleName + ' AS HW WHERE ( HW.ClientID = ' + CAST(AClientId AS VARCHAR) + ' ) AND ( HW.RemoveAudit IS NULL ) ) ) AS RSLT'; EXECUTE Stmt; END -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |