ftaco96
asked on
UPDATE statement help
Hi,
I've got this stored proc that needs to update multiple rows in a table (DETAIL) using specific criteria that's in a subquery, but I get this error, I believe on the WHERE clause at the end of the update:
PLS-00201: identifier 'smy.CYCLE_ID' must be declared
CREATE PROCEDURE SP_DETAIL
( PARM_MARKET IN VARCHAR2 )
IS
dtLastRpt DATE;
dtPrevRpt DATE;
iMarketID NUMBER;
BEGIN
SELECT market_id INTO iMarketID
FROM MARKET
WHERE MARKET = PARM_MARKET;
SELECT MAX(wmd.START_TIME)
INTO dtLastRpt
FROM MARKET_DAILY md
WHERE md.market_id = iMarketID
AND md.job_id = 41
AND md.start_time < SYSDATE;
SELECT MAX(md.START_TIME)
INTO dtPrevRpt
FROM MARKET_DAILY md
WHERE md.market_id = iMarketID
AND md.job_id = 41
AND md.start_time < dtLastBLNHB;
UPDATE DETAIL det
SET (rejects, modified_by, modify_date) =
(SELECT
smy.REJECT, 'myid' as modified_by, sysdate as modify_date
FROM
ACTUALS act
, CYCLE cyc
, SUMMARY smy
WHERE
act.CYCLE_ID = cyc.CYCLE_ID
AND smy.CYCLE_ID = cyc.CYCLE_ID
AND smy.MONTH_YEAR = act.MONTH_YEAR
AND cyc.MARKET_ID = iMarketID
AND act.EVENT_ID = 32
AND act.CYCLE_TYPE = 'M'
AND act.actual_time < dtLastRpt
AND act.actual_time > dtPrevRpt
)
WHERE det.CYCLE_ID = smy.cycle_id
and det.MONTH_YEAR = smy.month_year;
END;
Anybody have any ideas?
I've got this stored proc that needs to update multiple rows in a table (DETAIL) using specific criteria that's in a subquery, but I get this error, I believe on the WHERE clause at the end of the update:
PLS-00201: identifier 'smy.CYCLE_ID' must be declared
CREATE PROCEDURE SP_DETAIL
( PARM_MARKET IN VARCHAR2 )
IS
dtLastRpt DATE;
dtPrevRpt DATE;
iMarketID NUMBER;
BEGIN
SELECT market_id INTO iMarketID
FROM MARKET
WHERE MARKET = PARM_MARKET;
SELECT MAX(wmd.START_TIME)
INTO dtLastRpt
FROM MARKET_DAILY md
WHERE md.market_id = iMarketID
AND md.job_id = 41
AND md.start_time < SYSDATE;
SELECT MAX(md.START_TIME)
INTO dtPrevRpt
FROM MARKET_DAILY md
WHERE md.market_id = iMarketID
AND md.job_id = 41
AND md.start_time < dtLastBLNHB;
UPDATE DETAIL det
SET (rejects, modified_by, modify_date) =
(SELECT
smy.REJECT, 'myid' as modified_by, sysdate as modify_date
FROM
ACTUALS act
, CYCLE cyc
, SUMMARY smy
WHERE
act.CYCLE_ID = cyc.CYCLE_ID
AND smy.CYCLE_ID = cyc.CYCLE_ID
AND smy.MONTH_YEAR = act.MONTH_YEAR
AND cyc.MARKET_ID = iMarketID
AND act.EVENT_ID = 32
AND act.CYCLE_TYPE = 'M'
AND act.actual_time < dtLastRpt
AND act.actual_time > dtPrevRpt
)
WHERE det.CYCLE_ID = smy.cycle_id
and det.MONTH_YEAR = smy.month_year;
END;
Anybody have any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Definitely double-check the column as seazodiac suggested. I had a problem yesterday with exactly the same error message. Even though I was sure I had the column names right, seazodiac recommended the DESCRIBE <table> and I discovered the column name wasn't what I thought it was.
Actually, SDutta is on the right track, and the summary table cannot be referenced in the where clause of the update statement because it is only declared in the nested select statement. His solution should work nicely for you. If the column CYCLE_ID was not in the summary table, I would think that the Error would show in the nested select statement instead.
@ftaco96,
Any updates - if my solution worked or not ? In fact what Seazodiac suggests may also be true but then it is too simple a scenario and there would be no possibility of your update statement working at all.
Any updates - if my solution worked or not ? In fact what Seazodiac suggests may also be true but then it is too simple a scenario and there would be no possibility of your update statement working at all.
ASKER
SDutta, I just tried it and it works like a charm. Thanks.
ASKER
I was running this query again today, and I got an error:
ORA01427 - single row sub-query returns more than one row
I didn't think this was an issue with this query because the whole point was to update multiple rows. Apparently, though, the first select subquery is getting more than one row, and Oracle is choking on it.
ORA01427 - single row sub-query returns more than one row
I didn't think this was an issue with this query because the whole point was to update multiple rows. Apparently, though, the first select subquery is getting more than one row, and Oracle is choking on it.
Yes, you can only get one row with a select statement inside a procedure unless you make a cursor of it.
If you have multiple rows with the same condition and you are sure that the rows will return the same values, you can add to the existing condition " AND ROWNUM = 1" to get only one row.
If you have multiple rows with the same condition and you are sure that the rows will return the same values, you can add to the existing condition " AND ROWNUM = 1" to get only one row.
I suspect in Summary table, there is no cycle_id column, it might be named idfferently.
try to post the output of this command:
SQL>desc summary