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.

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

"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.

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

>> "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.

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

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