I have the following query :
DECLARE
tmp INTEGER;
BEGIN
tmp := 1;
DBMS_OUTPUT.PUT_LINE(tmp);
INSERT INTO WT_PTFMGT
(SESSION_ID,TYPE,L, R, C,GBL_INSTRUMENT_ID,COM,L_HOLDING, R_HOLDING, C_HOLDING)
SELECT
tmp,
'BO' TYPE,
DECODE(L_INSTRUMENT_ID,NULL,'N','Y') L,
DECODE(R_INSTRUMENT_ID,NULL,'N','Y') R,
DECODE(C_INSTRUMENT_ID,NULL,'N','Y') C,
NVL(L_INSTRUMENT_ID, NVL(R_INSTRUMENT_ID,C_INSTRUMENT_ID)) GBL_INSTRUMENT_ID,
(DECODE(L_COMMENTS,NULL,0,1) + DECODE(R_COMMENTS,NULL,0,1) + DECODE (C_COMMENTS,NULL,0,1)) COM,
L_QUANTITY L_HOLDING,
R_QUANTITY R_HOLDING,
C_QUANTITY C_HOLDING
FROM
(SELECT * FROM
(SELECT INSTRUMENT_ID C_INSTRUMENT_ID, QUANTITY C_QUANTITY, COMMENTS C_COMMENTS,
TYPE C_TYPE, HOLDING_ID C_HOLDING_ID FROM WT_HOLDINGFUSION WHERE TYPE = 'C' AND SESSION_ID = tmp)
FULL OUTER JOIN
(SELECT INSTRUMENT_ID R_INSTRUMENT_ID, QUANTITY R_QUANTITY, COMMENTS R_COMMENTS,
TYPE R_TYPE, HOLDING_ID R_HOLDING_ID FROM WT_HOLDINGFUSION WHERE TYPE = 'R' AND SESSION_ID = tmp)
ON R_INSTRUMENT_ID = C_INSTRUMENT_ID
FULL OUTER JOIN
(SELECT INSTRUMENT_ID L_INSTRUMENT_ID, QUANTITY L_QUANTITY, COMMENTS L_COMMENTS,
TYPE L_TYPE, HOLDING_ID L_HOLDING_ID FROM WT_HOLDINGFUSION WHERE TYPE = 'L' AND SESSION_ID = tmp)
ON L_INSTRUMENT_ID = C_INSTRUMENT_ID OR L_INSTRUMENT_ID = R_INSTRUMENT_ID);
END;
If I execute it, I get the error 'INVALID NUMBER'.
If I change the tmp into 1, it works fine.
The problem is that I do need the tmp, as the number can vary.
I've tried to use the TO_NUMBER, but then i get 'Missing right parenthesis'
Can anyone help me?
Thanks.