Crystal SQL Expression Help

[aaronburro]

I'm trying to create a few SQL expressions in a crystal report v8.5 in order to use said expressions as possible sorting criteria (summary of a summary, basically). I've never created a SQL expression like this before. When I say "like this" i mean "with a select statement," so I am a bit lost on this. Someone in another forum has been helping me with this problem initially but they don't use MAS200, so they aren't sure why I am getting an error. Anyway, here is the expression with which I am trying to work:

{%Price}
--------------------------------
(SELECT SUM( A."SOQtyShipped" * A."SOUnitPrice")
FROM ARO_InvHistoryDetail A, AR1_CustomerMaster B
WHERE B."CustomerNumber" = AR1_CustomerMaster."CustomerNumber")

The SQL query for the report that I want to put this into is:
--------------------------------------------------------------------------
SELECT
ARO_InvHistoryDetail."SOItemNumber", ARO_InvHistoryDetail."SODescription", ARO_InvHistoryDetail."SOQtyShipped", ARO_InvHistoryDetail."SOUnitPrice", ARO_InvHistoryDetail."SOUnitCost",
IM1_InventoryMasterfile."Weight",
ARN_InvHistoryHeader."InvoiceNumber", ARN_InvHistoryHeader."InvoiceDate", ARN_InvHistoryHeader."CustomerNumber", ARN_InvHistoryHeader."SOBillToName", ARN_InvHistoryHeader."SOShipWeight", ARN_InvHistoryHeader."WarehouseCode",
AR1_CustomerMaster."SalesPersonCode"
FROM
"ARN_InvHistoryHeader" ARN_InvHistoryHeader,
"AR1_CustomerMaster" AR1_CustomerMaster,
{ oj "ARO_InvHistoryDetail" ARO_InvHistoryDetail LEFT OUTER JOIN "IM1_InventoryMasterfile" IM1_InventoryMasterfile ON
ARO_InvHistoryDetail."SOItemNumber" = IM1_InventoryMasterfile."ItemNumber"}
WHERE
ARO_InvHistoryDetail."InvoiceNumber" = ARN_InvHistoryHeader."InvoiceNumber" AND
ARO_InvHistoryDetail."HeaderSeqNumber" = ARN_InvHistoryHeader."HeaderSeqNumber" AND
ARN_InvHistoryHeader."Division" = AR1_CustomerMaster."Division" AND
ARN_InvHistoryHeader."CustomerNumber" = AR1_CustomerMaster."CustomerNumber" AND
ARN_InvHistoryHeader."InvoiceDate" >= {d 'XXXX-XX-XX'} AND
ARN_InvHistoryHeader."InvoiceDate" <= {d 'XXXX-XX-XX'}
ORDER BY
ARN_InvHistoryHeader."CustomerNumber" ASC,
ARN_InvHistoryHeader."InvoiceNumber" ASC

The invoice date is based on a CR parameter and, as such, I just put X's there to denote that. Now, when I try and save {%Price}, I get the following error:

ODBC error:[ProvideX][ODBC Driver]Expected lexical element not found: <identifier>

In then choosing not to save the expression because it has an alleged error, it then proceeds to give me the next error:

Error in compiling SQL expression :
Syntax error found here.

CR then places the cursor right to the left of the "S" in SUM. I have tried the expression w/ a {fn ... } wrapper around the SUM function, but that yields the same results.

Thus, what am I doing wrong here? Are there any specific options that I need to have selected in order for this SQL expression to work? Just in case you were curious, yes, I do have "Use Indexes..." checked on in report options.

as a little background info, i need this expression to work, as I am hoping to use it for sorting of groups.

I'm wondering if there is something about the MAS200 provideX version that doesn't like such a SQL expression w/in the context of a CR report.

Thx in advance!