Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
Two different variations of Error 700 |
Mon, Sep 13 2010 2:59 AM | Permanent Link |
David Cornelius Cornelius Concepts | I just upgraded a database and application (Delphi 2009) from EDB 2.03b9 to
EDB 2.03b21 (Unicode). A big jump in builds, but I read through the latest What's New list and none of the "breaking changes" affected me. But there must have been some subtle SQL changes that I wasn't aware of because a stored procedure that used to work is now broken. This stored procedure copies information from a purchase order onto an order acknowledgement. The part that gets tripped up is near the end when it's creating a record in a CostSheet table to refer to the new order acknowledgement (I've replaced some variables with constants for the sake of easy reading): INSERT INTO "CostSheet" (OA_GUID, DocNum, CIF) VALUES ( (SELECT "GUID" FROM OrderAckHeaders WHERE DocNum = '100'), '100', (SELECT SUM(ExtPrice) FROM PODetails WHERE PO_GUID = (SELECT "GUID" FROM POHeaders WHERE DocNum = '100'))); OA_GUID is a GUID, DocNum is a VARCHAR, and CIF is a Float. This statement generates error 700: "Invalid expression SELECT found, query expression not allowed". I didn't see anything wrong, and as I said before, it used to work just fine. So I started picking it apart to see which of the sub-selects was causing it grief. In fact, I just decided to create intermediate variables and work it out to an INSERT statement followed by an UPDATE statement to really isolate the problem: DECLARE OAG GUID; EXECUTE IMMEDIATE 'SELECT "GUID" INTO ? FROM OrderAckHeaders WHERE DocNum = ''100''' USING OAG; INSERT INTO "CostSheet" (OA_GUID, DocNum) VALUES (OAG, '100'); I didn't even get to the UPDATE part. Instead, it broke again right away, but in a different variant of error 700: "Invalid expression CostSheet found, the referenced cursor does not exist." Cursor? I wasn't trying to use a cursor! What is going on here? -- David Cornelius Cornelius Concepts __________ Information from ESET NOD32 Antivirus, version of virus signature database 5445 (20100912) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com |
Mon, Sep 13 2010 3:12 AM | Permanent Link |
Uli Becker | David,
> INSERT INTO "CostSheet" (OA_GUID, DocNum) > VALUES (OAG, '100'); EXECUTE IMMEDIATE 'INSERT INTO "CostSheet" (OA_GUID, DocNum) VALUES (OAG, ''100'')'; Uli |
Mon, Sep 13 2010 10:56 PM | Permanent Link |
David Cornelius Cornelius Concepts | OK, I was tired when I posted this and didn't clarify the fact that these
statements ARE in EXECUTE IMMEDIATE statements. Here's the original stored procedure (which worked in a previous build of EDB): CREATE PROCEDURE "ConvertPOToOrderAck" (IN "CurrPODocNum" VARCHAR(20) COLLATE UNI, IN "NewOADocNum" VARCHAR(20) COLLATE UNI, IN "ConvertPrices" BOOLEAN) BEGIN DECLARE PriceFldValue VARCHAR DEFAULT '0.0'; IF ConvertPrices THEN SET PriceFldValue = 'pd.Price'; END IF; START TRANSACTION ON TABLES OrderAckHeaders, OrderAckDetails; BEGIN -- copy PO header to OA header EXECUTE IMMEDIATE 'INSERT INTO OrderAckHeaders ( YOUR_NUM, DocNum, DocDate, L_TYPE, PAGE_NO, CreatedByGUID, Active, Footer, SalesPersonGUID) SELECT YOUR_NUM, ''' + NewOADocNum + ''', CURRENT_DATE(), L_TYPE, PAGE_NO, CreatedByGUID, Active, Footer, SalesPersonGUID FROM POHeaders WHERE DocNum = ''' + CurrPODocNum + ''''; -- copy po details to OA details EXECUTE IMMEDIATE 'INSERT INTO OrderAckDetails (OA_GUID, Sequence, SKU, Category, SubCategory, QTY, Packing, UNIT, UNIT2, ItemSize, CustPartNo, ItemDescription, PRICE, H_LINE, L_TYPE) SELECT oh_new.GUID, pd.Sequence, pd.SKU, pd.Category, pd.SubCategory, pd.QTY, pd.Packing, pd.Unit, pd.Unit2, pd.ItemSize, pd.CustPartNo, pd.ItemDescription, ' + PriceFldValue + ', pd.H_Line, pd.L_Type FROM PODetails pd, OrderAckHeaders oh_new JOIN POHeaders ph ON pd.PO_GUID = ph.GUID WHERE oh_new.DocNum = ''' + NewOADocNum + ''' AND ph.DocNum = ''' + CurrPODocNum + ''''; -- now add the cost sheet for the new OA EXECUTE IMMEDIATE 'INSERT INTO CostSheet (OA_GUID, DocNum, CIF) VALUES ( (SELECT GUID FROM OrderAckHeaders WHERE DocNum = ''' + NewOADocNum + '''), ''' + UPPER(NewOADocNum) + ''', (SELECT SUM(ExtPrice) FROM PODetails WHERE PO_GUID = (SELECT GUID FROM POHeaders WHERE DocNum = ''' + UPPER(CurrPODocNum) + ''')))'; COMMIT; EXCEPTION CALL RecordError('OrderAcks', ERRORCODE(), ERRORMSG() + ' (Converting PO to OA)'); ROLLBACK; RAISE; END; END __________ Information from ESET NOD32 Antivirus, version of virus signature database 5445 (20100912) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com |
Mon, Sep 13 2010 11:18 PM | Permanent Link |
David Cornelius Cornelius Concepts | Wait, I think I remember reading that using INSERT with SELECTS was more
restrictive, like you can only have values OR Selects, not both like I'm doing (see the SQL below "now add the cost sheet for the new OA..."). So I have to rewrite that "INSERT INTO CostSheet" part, right? I'll have to either create a big SELECT with multiple JOINs, or create a bunch of intermediary variables. At least that's what I'm thinking... -- David Cornelius Cornelius Concepts __________ Information from ESET NOD32 Antivirus, version of virus signature database 5445 (20100912) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com |
Tue, Sep 14 2010 9:47 AM | Permanent Link |
David Cornelius Cornelius Concepts | Changing the last part to the following worked:
EXECUTE IMMEDIATE 'INSERT INTO CostSheet (OA_GUID, DocNum, CIF) SELECT oah.GUID AS "OAGUID", ''' + UPPER(NewOADocNum) + ''' AS NewDocNum, SUM(pod.ExtPrice) FROM OrderAckHeaders oah, PODetails pod WHERE oah.DocNum = ''' + UPPER(CurrPODocNum) + ''' AND pod.PO_GUID = (SELECT GUID FROM POHeaders WHERE DocNum = ''' + UPPER(CurrPODocNum) + ''') GROUP BY OAGUID, NewDocNum'; It's not too bad, but a mix of values and SELECT statements can no longer be used. Instead, the SELECT statements may need to be a little more complex. -- David Cornelius Cornelius Concepts |
Wed, Sep 15 2010 2:54 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | David,
<< This stored procedure copies information from a purchase order onto an order acknowledgement. The part that gets tripped up is near the end when it's creating a record in a CostSheet table to refer to the new order acknowledgement (I've replaced some variables with constants for the sake of easy reading): INSERT INTO "CostSheet" (OA_GUID, DocNum, CIF) VALUES ( (SELECT "GUID" FROM OrderAckHeaders WHERE DocNum = '100'), '100', (SELECT SUM(ExtPrice) FROM PODetails WHERE PO_GUID = (SELECT "GUID" FROM POHeaders WHERE DocNum = '100'))); OA_GUID is a GUID, DocNum is a VARCHAR, and CIF is a Float. This statement generates error 700: "Invalid expression SELECT found, query expression not allowed". >> This is a bug that was introduced as part of the row value support internals. A fix will be available, but still a week or so away. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Sep 16 2010 1:49 PM | Permanent Link |
David Cornelius Cornelius Concepts | Thanks for the note. I worked around it by restructuring the values as one
SQL statement pulling in fields from two different tables that couldn't be JOINed. INSERT INTO CostSheet (OA_GUID, DocNum, CIF) SELECT oah.GUID, '100', SUM(pod.ExtPrice) FROM PODetails pod, OrderAckHeaders oah WHERE pod.PO_GUID = (SELECT GUID FROM POHeaders WHERE DocNum = '100') AND oah.DocNum = '100') GROUP BY OAGUID, NewDocNum "Tim Young [Elevate Software]" wrote: > This is a bug that was introduced as part of the row value support > internals. A fix will be available, but still a week or so away. |
This web page was last updated on Wednesday, June 12, 2024 at 01:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |