[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 832
  • Last Modified:

Rewrite Merge statement to Update statement

Experts,

last night I received help on here to write this query

MERGE INTO RMORHP@ermsd r
	USING (SELECT user_field1||route_id route, order_number, route_id, sequence_number from apex_sessiond@rn where rn_session_pkey = 28 and route_id = '658') a
	on (a.order_number = r.orhordnum)
WHEN MATCHED THEN update set r.orhrtenum = a.route;

Open in new window


my problem is that i cannot merge over a DB link, I can however, do an update over a DB link.

Is it possible to rewrite the above to an update statement and get the same functionality ?
0
FutureDBA-
Asked:
FutureDBA-
  • 12
  • 8
1 Solution
 
slightwv (䄆 Netminder) Commented:
Why can't you merge over a link?  What is the error?

Based on your previous question both databases are 11gR2.  I saw there used to be a bug prior to 10.2.0.4.
0
 
FutureDBA-Author Commented:
no, there are 3 databases involved.

Apex Server (11gr2)
Routing Server (11gr2)
ERP. (MS SQL Server)

When i was writing the merge statement yesterday, I was doing it to a local table that is a replica of the table i really want to update on the ERP. Once the syntax was correct, I added the database link to the true table that needs to be updated.

When executing the merge, all 3 servers are involved. data being fetched from routign server to apex, and apex server updating the SQL server

The Error is

Error starting at line : 1 in command -
MERGE INTO RMORHP@ermsd r
	USING (SELECT user_field1||route_id route, order_number, route_id, sequence_number from apex_sessiond@rn where rn_session_pkey = 28 and route_id = '658') a
	on (a.order_number = r.orhordnum)
WHEN MATCHED THEN update set r.orhrtenum = a.route
Error at Command Line : 1 Column : 1
Error report -
SQL Error: ORA-02070: database ERMSD does not support  in this context
02070. 00000 -  "database %s%s does not support %s in this context"
*Cause:    The remote database does not support the named capability in
           the context in which it is used.
*Action:   Simplify the SQL statement.

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Cool.

Then did you add the where clause from your other question as suggested by Mark?

http://www.experts-exchange.com/Database/Oracle/Q_28473490.html#a40189131
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
FutureDBA-Author Commented:
A bit confused.

This is where I am,

UPDATE RNORHP n SET n.ORHRTENUM = (SELECT 
                                        o.USER_FIELD1||r.ROUTE_ID ERMS_ROUTE
                                      FROM 
                                        apex_sessiond@rn
                                      WHERE r.RN_SESSION_PKEY = 26 and n.ORHORDNUM = o.ORDER_NUMBER AND ROUTE_ID <> 'Unassigned' AND ROUTE_ID <> 'LI') a
                                      
                              where exists (select n.orhordnum from a join n on a.order_number = n.orhordnum);

Open in new window

0
 
FutureDBA-Author Commented:
I've refined it to this


UPDATE RNORHP SET ORHRTENUM = (SELECT 
                                        USER_FIELD1||ROUTE_ID ERMS_ROUTE
                                      from apex_sessiond@rn where rn_session_pkey = 28 and route_id = '658') 
                                      
                              where exists 
                                    (select NULL
                                        from RNORHP n join apex_sessiond@rn a on a.order_number = n.orhordnum);

Open in new window


now i get

SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 -  "single-row subquery returns more than one row"
*Cause:    
*Action:

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
You still need the where clause on the inner query as well as on the update.

The inner query needs to return only one row.

If you can provide table setups, sample data and expected results we can provide a working example.
0
 
FutureDBA-Author Commented:
i get the same issue with an update statement, i got the update statement working correctly, but i get the same problem as the merge.

Is there a way to update tables on a remote sqlserver link based on values in a local view ?

SQL Error: ORA-02070: database ERMSD does not support subqueries in this context
02070. 00000 -  "database %s%s does not support %s in this context"
*Cause:    The remote database does not support the named capability in
           the context in which it is used.
*Action:   Simplify the SQL statement.

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I've never set up a heterogeneous system so I cannot say how to do this.

Something in one of the sub queries is causing problems.  You would need to play around with rewriting the update to get around the issue and still update only the rows you need updated.

I might also look at writing a stored procedure to do this.  Then the select can be a loop and the update statement inside the loop.
0
 
FutureDBA-Author Commented:
Can you give me a an example of how to write something like this as a loop?

I am trying to write it as a pl/sql block. but am having issues with

ORA-01422: exact fetch returns more than requested number of rows

DECLARE
  ORDNUM  VARCHAR(15);
  RTENUM  VARCHAR(4);
  SEQNUM  VARCHAR(3);
  PKEY    VARCHAR(5);
BEGIN
  SELECT 
      RN_SESSION_PKEY, 
      ROUTE, 
      SEQUENCE_NUMBER, 
      ORDER_NUMBER 
INTO PKEY, RTENUM, SEQNUM, ORDNUM
FROM  RN_SESSIOND;
UPDATE RNORHP SET ORHRTENUM = RTENUM WHERE ORHORDNUM = ORDNUM;
 end;
 /

Open in new window

0
 
FutureDBA-Author Commented:
also, the subqueries are fine now, its a matter of updating with a select statement a remotedb.

i can however, update the DB if i did something like

"update remotedb@link set col1 = 'Blah';"

that executes successfully over the link, i suspect your theory of the loop, will work.
0
 
slightwv (䄆 Netminder) Commented:
>>its a matter of updating with a select statement a remotedb.
>>"update remotedb@link set col1 = 'Blah';"

Since that works, then it is the sub queries throwing it off.  It's the update with a sub-select across the link.

>>Can you give me a an example of how to write something like this as a loop?

Something like:
DECLARE
BEGIN
for I in (
  SELECT 
      RN_SESSION_PKEY, 
      ROUTE, 
      SEQUENCE_NUMBER, 
      ORDER_NUMBER 
FROM  RN_SESSIOND
) loop
UPDATE RNORHP SET ORHRTENUM = i.RTENUM WHERE ORHORDNUM = i.ORDNUM;
end loop;
 end;
 /

Open in new window

0
 
FutureDBA-Author Commented:
I used the above example and rewrote it to

DECLARE
BEGIN
for I in (
  SELECT 
      RN_SESSION_PKEY, 
      ROUTE, 
      SEQUENCE_NUMBER, 
      ORDER_NUMBER 
FROM  RN_SESSIOND where rn_session_pkey = 30 and ROUTE <> '8Unassigned' and ORDER_NUMBER IN ('798560', '798425', '798573')) 
  loop
UPDATE RMORHP@ermsd SET ORHRTENUM = i.ROUTE WHERE ORHORDNUM = i.ORDER_NUMBER;
end loop;
 commit;
 end;
 /

Open in new window


but I am still getting

ORA-02070: database ERMSD does not support TO_NUMBER in this context
ORA-06512: at line 11
02070. 00000 -  "database %s%s does not support %s in this context"
*Cause:    The remote database does not support the named capability in
           the context in which it is used.
*Action:   Simplify the SQL statement.

Open in new window


note that the error changed from subquery to to_number context
0
 
slightwv (䄆 Netminder) Commented:
Check the data types.  It looks like it is trying to do an implicit data conversion somewhere.

Make strings strings and numbers numbers explicitly with to_number and to_char function calls.
0
 
FutureDBA-Author Commented:
I did

BEGIN
for I in (
  SELECT 
      RN_SESSION_PKEY, 
      to_number(ROUTE) ROUTE, 
      SEQUENCE_NUMBER, 
      ORDER_NUMBER 
FROM  RN_SESSIOND where rn_session_pkey = 30 and ROUTE <> '8Unassigned' and ORDER_NUMBER IN ('798560', '798425', '798573')) 
  loop
UPDATE RMORHP@ermsd SET ORHRTENUM = i.ROUTE WHERE ORHORDNUM = i.ORDER_NUMBER;
end loop;
 commit;
 end;

Open in new window



ORHRTENUM is decimal,6 on the SQL Server. not sure  how to prcoeed.

the data on i.ROUTE is strictly numbers
0
 
slightwv (䄆 Netminder) Commented:
>>the data on i.ROUTE is strictly numbers

Are you saying it is defined as a number or it is a varchar2 that only has numbers in it?
0
 
FutureDBA-Author Commented:
desc rn_sessiond
Name            Null Type          
--------------- ---- ------------- 
RN_SESSION_PKEY      NUMBER(38)    
ROUTE                NUMBER        
SEQUENCE_NUMBER      NUMBER(38)    
ORDER_NUMBER         NVARCHAR2(15) 

Open in new window

0
 
FutureDBA-Author Commented:
rn_sessiond is a view, not a table. that view is pulling table from a oracle 11gr2 db over a dblink.

the table i am updating is remote as well. mssql server.
0
 
slightwv (䄆 Netminder) Commented:
Table or view doesn't matter.

>>ORDER_NUMBER         NVARCHAR2(15)

ORHORDNUM  is a 'decimal'.  ORDER_NUMBER is a string.  Any reason it is declared as NVARCHAR2?

I've never messed with NVARCHAR2 but I assume you can convert it to a number with to_number.

try:
...
UPDATE RMORHP@ermsd SET ORHRTENUM = i.ROUTE WHERE ORHORDNUM = to_number(i.ORDER_NUMBER);
...
0
 
FutureDBA-Author Commented:
the nvarchar2 is coming from the oracle11gr2 box for our routing and logistics software, the db was designed by that company.

ROUTE is really an nvarchar2 converted to number on the view.

Using
BEGIN
for I in (
  SELECT 
      RN_SESSION_PKEY, 
      to_number(ROUTE) ROUTE, 
      SEQUENCE_NUMBER, 
      ORDER_NUMBER 
FROM  RN_SESSIOND where rn_session_pkey = 30 and ORDER_NUMBER IN ('798560', '798425', '798573')) 
  loop
UPDATE RMORHP@ermsd SET ORHRTENUM = i.ROUTE WHERE ORHORDNUM = to_number(i.ORDER_NUMBER);
end loop;
 commit;
 end;

Open in new window


I am getting

Error report -
ORA-02070: database ERMSD does not support TO_NUMBER in this context
ORA-06512: at line 10
02070. 00000 -  "database %s%s does not support %s in this context"
*Cause:    The remote database does not support the named capability in
           the context in which it is used.
*Action:   Simplify the SQL statement.

Open in new window


afterwords, i converted to number in the a copy of the view (RNS)that's being called.

that seemed to do the trick.

BEGIN
for I in (
  SELECT 
      RN_SESSION_PKEY, 
      ROUTE, 
      SEQUENCE_NUMBER, 
      ORDER_NUMBER 
FROM  RNS where rn_session_pkey = 30 and ORDER_NUMBER IN (798560, 798425, 798573)) 
  loop
UPDATE RMORHP@ermsd SET ORHRTENUM = i.ROUTE WHERE ORHORDNUM = i.ORDER_NUMBER;
end loop;
 commit;
 end;
 /

Open in new window

0
 
FutureDBA-Author Commented:
thank you sir, extremely helpful
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 12
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now