Board index » delphi » TQuery Error Capability Not Supported

TQuery Error Capability Not Supported

I am using Paradox tables.  I notice that localsql.hlp (the help file for
BDE SQL) states for INSERT INTO that "Insertion of one or multiple rows from
one table to another through a subquery is not supported."  I do not know
precisely what that means.  I will paste two queries below, the first is
successfully running in Delphi 5, and the second gives me the "Capability
not supported" whenever ExecSQL is called for it.  Does anyone know why!?
Thanks!

This query runs:

INSERT INTO "LoadBom.db"
(NodeKey, FGNum, NodeItemNum, QtyPerParent, QtyPerFG, ParentNodeKey)
SELECT -(Bom.NodeKey), Bom.FGNum, RM.ItemNumRawMat,
    RM.QtyRawMat, Bom.QtyPerFG * RM.QtyRawMat, Bom.NodeKey
FROM "LoadBom.db" Bom
INNER JOIN "RawMatDf.db" RM
ON (Bom.NodeItemNum = RM.ItemNumPRM)

This query will not run, but gives the error:

INSERT INTO "RoutDflt.db"
(ItemNum, RoutSequence, RoutDeptKey, RsGroupKey,
    ProcInstKey, MillInstKey, SetupTime, RunTime, Skilled, Unskilled)
SELECT ItemNum, RoutSequence, RoutDeptKey, RsGroupKey,
    ProcInstKey, MillInstKey, SetupTime, RunTime, Skilled, Unskilled
FROM "RoutDflt.db" Rd
WHERE (Rd."ItemNum" = :ItemNum)

--
Joseph Misko

 

Re:TQuery Error Capability Not Supported


Quote
> I will paste two queries below, the first is
>successfully running in Delphi 5, and the second gives me the "Capability
>not supported" whenever ExecSQL is called for it.  Does anyone know why!?

If the first query works my guess is that it is the parameter in the second
query that is not supported.  Try hard coding the value of ItemNum in the second
query and see if that works.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:TQuery Error Capability Not Supported


Hardcoding into the SQL did not work.

Joseph Misko

Re:TQuery Error Capability Not Supported


Quote
>Hardcoding into the SQL did not work.

Does it work with out a Where clause?
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:TQuery Error Capability Not Supported


On Wed, 29 Aug 2001 20:40:32 -0400, "Joseph Misko"

Quote
<josephmi...@noham.hotmail.com> wrote:
>This query runs:

>INSERT INTO "LoadBom.db"
>(NodeKey, FGNum, NodeItemNum, QtyPerParent, QtyPerFG, ParentNodeKey)
>SELECT -(Bom.NodeKey), Bom.FGNum, RM.ItemNumRawMat,
>    RM.QtyRawMat, Bom.QtyPerFG * RM.QtyRawMat, Bom.NodeKey
>FROM "LoadBom.db" Bom
>INNER JOIN "RawMatDf.db" RM
>ON (Bom.NodeItemNum = RM.ItemNumPRM)

>This query will not run, but gives the error:

>INSERT INTO "RoutDflt.db"
>(ItemNum, RoutSequence, RoutDeptKey, RsGroupKey,
>    ProcInstKey, MillInstKey, SetupTime, RunTime, Skilled, Unskilled)
>SELECT ItemNum, RoutSequence, RoutDeptKey, RsGroupKey,
>    ProcInstKey, MillInstKey, SetupTime, RunTime, Skilled, Unskilled
>FROM "RoutDflt.db" Rd
>WHERE (Rd."ItemNum" = :ItemNum)

Local SQL doesn't allow you to insert from the same table.  That is
why the second query fails.  This is documented in LocalSQL.HLP.

The reason that the first query succeeds is that the SELECT part of
this query returns a temporary result set because of the JOIN.  As a
consequence Local SQL doesn't detect that it inserts from the same
table and allows this construct.

A possible work-around for the second query (I haven't tried it, but
it that should work) is to use a pseudo-view.  Just create a text file
with the .SQL extension (e.g. "MyView.SQL") that contains the SELECT
part of the second query.  Then insert using this pseudo-view:
    INSERT INTO "RoutDflt.db"
    (ItemNum, RoutSequence, RoutDeptKey, RsGroupKey,
    ProcInstKey, MillInstKey, SetupTime, RunTime, Skilled, Unskilled)
   SELECT ItemNum, RoutSequence, RoutDeptKey, RsGroupKey,
    ProcInstKey, MillInstKey, SetupTime, RunTime, Skilled, Unskilled
   FROM "MyView.SQL"

HTH,

Jan

Other Threads