Error while executing the MERGE statement (merged) [message #415969] |
Thu, 30 July 2009 00:27  |
rtnataraj
Messages: 102 Registered: December 2008
|
Senior Member |

|
|
Hi All,
When I try to execute the below MERGE statement
MERGE INTO summary.osfi_feed_core_data_h h
USING (SELECT a.ntrl_k_odc_instrument,
LAST_DAY
(MIN (account_write_off_posting_date)
) account_write_off_posting_date,
MAX
(account_write_off_posted_amt
) account_write_off_posted_amt
FROM landing_h.mortgage_dwo_cr154 a
WHERE a.account_write_off_posting_type = 'DR'
AND a.account_write_off_posting_date =
(SELECT MIN (b.account_write_off_posting_date)
FROM landing_h.mortgage_dwo_cr154 b
WHERE b.ntrl_k_odc_instrument = a.ntrl_k_odc_instrument
AND b.account_write_off_posting_type = 'DR')
GROUP BY a.ntrl_k_odc_instrument, a.account_write_off_posting_date) mdwor
ON ( mdwor.ntrl_k_odc_instrument = h.ntrl_k_odc_instrument
AND mdwor.account_write_off_posting_date = h.month_end_date)
WHEN MATCHED THEN
UPDATE
SET h.recovery_amt =
NVL (h.recovery_amt, 0)
+ NVL (mdwor.account_write_off_posted_amt, 0)
WHEN NOT MATCHED THEN
INSERT (ntrl_k_odc_instrument, month_end_date, row_source, recovery_amt)
VALUES (mdwor.ntrl_k_odc_instrument,
mdwor.account_write_off_posting_date, 'MTGDWO',
NVL (mdwor.account_write_off_posted_amt, 0));
in the below environment.
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
regards,
Nataraj.
[remove surplus CR/LF]
[Updated on: Wed, 27 January 2010 03:19] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Error while executing the MERGE statement (merged) [message #440820 is a reply to message #415969] |
Wed, 27 January 2010 03:34   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The only time I've ever seen ORA-01791 is when you have a SELECT DISTINCT and and ORDER BY clause that doesn't contain one of the expressions from the SELECT list.
I'd post the actual error text, but it's not available in the online documentation (as far as I can see).
I'd agree with @Michel that the most likely suspect for this is a trigger on the summary.osfi_feed_core_data_h table.
Points:
1) Triggers on that table can be owned by other schemas, and you might not have the privileges to see them.
2) Is that a table or a view?
|
|
|
|