> Does INTO work on a normal select statement (not a procedure). I have
> the following statement that gives an error on INTO
>
> SELECT
> TBL_PURCHASES.PURCHASE_ID,
> TBL_PURCHASES.SUPPLIER_ID,
> TBL_PURCHASES.PURCHASE_DATE,
> TBL_PURCHASES.NOTES,
> TBL_PURCHASES.SUPPLIER_INVOICE_NO,
> TBL_PURCHASES.JOB_CARD_ID,
> (SELECT SUM(COST_PRICE) INTO :NEW_COST_PRICE
> FROM TBL_INV_TRANS
> WHERE TBL_INV_TRANS.PURCHASE_ID = TBL_PURCHASES.PURCHASE_ID)
> FROM
> TBL_PURCHASES
>
> In MySQL I use AS instead but on researching the correct syntax I came
> to INTO

The correct syntax is:

SELECT
TBL_PURCHASES.PURCHASE_ID,
TBL_PURCHASES.SUPPLIER_ID,
TBL_PURCHASES.PURCHASE_DATE,
TBL_PURCHASES.NOTES,
TBL_PURCHASES.SUPPLIER_INVOICE_NO,
TBL_PURCHASES.JOB_CARD_ID,
(SELECT SUM(COST_PRICE)
FROM TBL_INV_TRANS
WHERE
TBL_INV_TRANS.PURCHASE_ID = TBL_PURCHASES.PURCHASE_ID
) as NEW_COST_PRICE
FROM
TBL_PURCHASES


Sean