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

David,

>  INSERT INTO "CostSheet" (OA_GUID, DocNum)
>     VALUES (OAG, '100');

 EXECUTE IMMEDIATE 'INSERT INTO "CostSheet" (OA_GUID, DocNum)
    VALUES (OAG, ''100'')';

Uli

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

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

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

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

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.