>SQL.Strings:
> SELECT STOCKPO.*,
> (select sum(spod_ordqty * spod_cost) from stockpod
> where spod_spo_id = :ID) as POSUM
> FROM STOCKPO
> WHERE SPO_ID = :ID
>
>EditSQL.Strings;
> UPDATE STOCKPO STOCKPO SET
> STOCKPO.SPO_ID = :SPO_ID,
> STOCKPO.SPO_DATE = :SPO_DATE,
> STOCKPO.SPO_APVEN_ID = :SPO_APVEN_ID,
> STOCKPO.SPO_SOURCE = :SPO_SOURCE,
> STOCKPO.SPO_NOTES = :SPO_NOTES,
> STOCKPO.SPO_SHIPNAME = :SPO_SHIPNAME,
> STOCKPO.SPO_SHIPADDR1 = :SPO_SHIPADDR1,
> STOCKPO.SPO_SHIPADDR2 = :SPO_SHIPADDR2,
> STOCKPO.SPO_SHIPCITY = :SPO_SHIPCITY,
> STOCKPO.SPO_SHIPSTATE = :SPO_SHIPSTATE,
> STOCKPO.SPO_SHIPZIP = :SPO_SHIPZIP,
> STOCKPO.SPO_SHIPCOUNTRY = :SPO_SHIPCOUNTRY,
> STOCKPO.SPO_SHIPTOUS = :SPO_SHIPTOUS,
> STOCKPO.SPO_SHIPRESIDENTIAL = :SPO_SHIPRESIDENTIAL,
> STOCKPO.SPO_ORDEREDBY = :SPO_ORDEREDBY,
> STOCKPO.SPO_TOTALCOST = :SPO_TOTALCOST,
> STOCKPO.SPO_INCOMING_RECVD = :SPO_INCOMING_RECVD,
> STOCKPO.SPO_RECVD_DATE = :SPO_RECVD_DATE,
> STOCKPO.SPO_FC_ID = :SPO_FC_ID,
> STOCKPO.SPO_LOT = :SPO_LOT,
> STOCKPO.SPO_APVEN_EMAIL = :SPO_APVEN_EMAIL,
> STOCKPO.SPO_APVEN_COMPANY = :SPO_APVEN_COMPANY,
> STOCKPO.SPO_APVEN_ADDR1 = :SPO_APVEN_ADDR1,
> STOCKPO.SPO_APVEN_ADDR2 = :SPO_APVEN_ADDR2,
> STOCKPO.SPO_APVEN_CITY = :SPO_APVEN_CITY,
> STOCKPO.SPO_APVEN_STATE = :SPO_APVEN_STATE,
> STOCKPO.SPO_APVEN_ZIP = :SPO_APVEN_ZIP,
> STOCKPO.SPO_APVEN_COUNTRY = :SPO_APVEN_COUNTRY,
> STOCKPO.SPO_APVEN_PHONE = :SPO_APVEN_PHONE,
> STOCKPO.SPO_APVEN_FAX = :SPO_APVEN_FAX,
> STOCKPO.SPO_APVEN_TERMS = :SPO_APVEN_TERMS,
> STOCKPO.SPO_APVEN_CUSTOMERNUMBER = :SPO_APVEN_CUSTOMERNUMBER,
> STOCKPO.SPO_APVEN_CONTACT = :SPO_APVEN_CONTACT,
> STOCKPO.SPO_APVEN_NOTES = :SPO_APVEN_NOTES,
> STOCKPO.SPO_FC_TERMS = :SPO_FC_TERMS,
> STOCKPO.SPO_TOTALCHARGES = :SPO_TOTALCHARGES,
> STOCKPO.SPO_CODFEE = :SPO_CODFEE,
> STOCKPO.SPO_REMITCOD_NAME = :SPO_REMITCOD_NAME,
> STOCKPO.SPO_REMITCOD_ADDR1 = :SPO_REMITCOD_ADDR1,
> STOCKPO.SPO_REMITCOD_ADDR2 = :SPO_REMITCOD_ADDR2,
> STOCKPO.SPO_REMITCOD_CITY = :SPO_REMITCOD_CITY,
> STOCKPO.SPO_REMITCOD_STATE = :SPO_REMITCOD_STATE,
> STOCKPO.SPO_REMITCOD_ZIP = :SPO_REMITCOD_ZIP,
> STOCKPO.SPO_REMITCOD_COUNTRY = :SPO_REMITCOD_COUNTRY,
> STOCKPO.SPO_BOL_NOTES = :SPO_BOL_NOTES,
> STOCKPO.SPO_ATTENTION = :SPO_ATTENTION,
> STOCKPO.SPO_INTERNALNOTES = :SPO_INTERNALNOTES,
> STOCKPO.SPO_CASHCERTCHECK = :SPO_CASHCERTCHECK,
> STOCKPO.SPO_CUSCHECK = :SPO_CUSCHECK,
> STOCKPO.SPO_BROKER = :SPO_BROKER,
> STOCKPO.SPO_STATUS = :SPO_STATUS,
> STOCKPO.SPO_PROCESSING_STATUS = :SPO_PROCESSING_STATUS
> WHERE SPO_ID = :OLD_SPO_ID
>
>when we do a post it gets the following error
> Exception with message ‘Invalid text in HexTextToBinary’. Process

Stopped

>
>If I comment out the subselect it doesn’t have the error. It worked

with the subselect in IBObjects v4.

I agree that this sounds like an error, Michael, but why not try
rewriting the query to circumvent the error? I'm thinking of two
possibilities:

a) Link the subquery to the main query (having a non-related subquery is
rather unusual, making it more likely to not be part of Jasons test suite)
SELECT spo.*,
(select sum(spod_ordqty * spod_cost) from stockpod spod
where spod.spod_spo_id = spo.SPO_ID) as POSUM
FROM STOCKPO spo
WHERE SPO_ID = :ID

b) Use a CTE
with TMP(POSUM) as
(select sum(spod_ordqty * spod_cost) from stockpod
where spod_spo_id = :ID)
select STOCKPO.*, TMP.POSUM
FROM STOCKPO
CROSS JOIN TMP
WHERE SPO_ID = :ID

Though, as I said, it sounds like an error, so it may be wise for Jason
to test and fix (if it really is an error) even if one of my suggestions
work for you. One possible error that wouldn't quite be IBOs fault,
would be if SPO_ID and SPOD_SPO_ID were defined differently in
Firebird/InterBase (i.e. different character sets or different types or
length).

Set