Skip to Main Content

select column_name INTO var problem

858930Jul 12 2011 — edited Jul 14 2011
for mssql i would do it this way

select top 1 status
from T_EVOUCHER_AUDIT_TRAIL
WHERE VOUCHERID = v_VID
ORDER BY CREATEDDATETIME DESC

This query select the latest record based on the time. I am having problem converting this into oracle syntax in a view.

DECLARE variable_name VARCHAR2(100);

SELECT STATUS INTO variable_name
FROM (
SELECT STATUS
FROM TABLE
ORDER BY CREATEDDATETIME DESC
)
WHERE ROWNUM <= 1;

This is what i have done for oracle in a trigger but i believe this is wrong as i already pinpoint this portion is wrong. ( no compilation error but when run it throws an error ).

I am trying to take column_name value and assign it to variable_name
I am not sure if this is the right syntax.

Can anyone care to comment or advice me on this. How to get a value from a nested select into a variable in a trigger. Thanks a bunch

----------------------------------------------
The real problem is this portion of syntax

DECLARE variable_status VARCHAR2(100);

SELECT STATUS INTO variable_status
FROM (
SELECT STATUS
FROM TABLE
ORDER BY CREATEDDATETIME DESC
)
WHERE ROWNUM <= 1;

Can i use nested select query to put into a variable ?

------------------------------------------

REAL SCENARIO OVER HERE

CREATE TABLE "T_EVOUCHER_MASTER"
(
"VOUCHERID" VARCHAR2(30 CHAR) NOT NULL,
"UPDATEDBY" VARCHAR2(10 CHAR),
"UPDATEDDATETIME" TIMESTAMP (6),
"STATUS" VARCHAR2(10 CHAR)
/* more column below here which i erased off for simplicity*/
);

CREATE OR REPLACE TRIGGER "TR_EVOUCHER_AUDIT" BEFORE
UPDATE ON T_EVOUCHER_MASTER FOR EACH ROW DECLARE v_VID CHAR(30);
v_NEWSTATUS CHAR(1);
v_OLDSTATUS CHAR(1);
v_UPDATEDBY CHAR(10);
v_UPDATEDDATETIME TIMESTAMP(6);
v_TRANSTYPE CHAR(50);

BEGIN
SELECT :NEW.VOUCHERID ,
:NEW.STATUS ,
:NEW.UPDATEDBY ,
:NEW.UPDATEDDATETIME
INTO v_VID,
v_NEWSTATUS,
v_UPDATEDBY,
v_UPDATEDDATETIME
FROM DUAL ;
SELECT STATUS
INTO v_OLDSTATUS
FROM
(SELECT STATUS
FROM
(SELECT STATUS
FROM T_EVOUCHER_AUDIT_TRAIL
WHERE VOUCHERID = v_VID
ORDER BY CREATEDDATETIME DESC
)
)
WHERE ROWNUM <= 1;
/* more logic flow below here which i erased off for simplicity */
END;
/
ALTER TRIGGER "TR_EVOUCHER_AUDIT" ENABLE;


SELECT VOUCHERID,STATUS,UPDATEDBY,UPDATEDDATETIME
FROM T_EVOUCHER_MASTER
WHERE STATUS IN ('N','W')
AND TRUNC(EFFECTIVESTART,'DDD') >= TO_DATE(SYSDATE,'DD/MM/YYYY')

VOUCHERID STATUS UPDATEDBY UPDATEDDATETIME
1011548 N rajev 13-JUL-11 12.03.00.502000000 PM
1011550 N rajev 13-JUL-11 12.11.30.416000000 PM

UPDATE T_EVOUCHER_MASTER SET STATUS = 'E',
UPDATEDBY = 'system',
UPDATEDDATETIME = CURRENT_TIMESTAMP
WHERE VOUCHERID = '1011548';

Error starting at line 7 in command:
UPDATE T_EVOUCHER_MASTER SET STATUS = 'E',
UPDATEDBY = 'system',
UPDATEDDATETIME = CURRENT_TIMESTAMP
WHERE VOUCHERID = '1011548'
Error report:
SQL Error: ORA-01403: no data found
ORA-06512: at "HONDASTAGING12.TR_EVOUCHER_AUDIT", line 22
ORA-04088: error during execution of trigger 'HONDASTAGING12.TR_EVOUCHER_AUDIT'
01403. 00000 - "no data found"
*Cause:
*Action:


As promise this is the real scenario

btw i have fix this error, but not 100% sure if it right. Let see if my fix matches yours.

Edited by: DominicNg85 on Jul 13, 2011 1:40 AM

Edited by: DominicNg85 on Jul 12, 2011 8:18 AM

Edited by: DominicNg85 on Jul 12, 2011 8:19 AM

Edited by: DominicNg85 on Jul 12, 2011 8:20 AM

Edited by: DominicNg85 on Jul 12, 2011 8:41 AM

Edited by: DominicNg85 on Jul 12, 2011 10:00 AM

Edited by: DominicNg85 on Jul 12, 2011 10:23 AM

Edited by: DominicNg85 on Jul 13, 2011 1:41 AM
Comments
Post Details
Added on Jul 12 2011
19 comments
2,372 views