Hello,
I'm trying to use MERGE statement in a procedure, please see the code below:
procedure gen_nou (p_data date) is
v_atribut_id number;
begin
v_atribut_id=100;
merge into TABLE1 x
using (select distinct id, 'D' nou from TABLE2
where data=p_data) a
on (x.atribut_id=v_atribut_id
and x.id=)
when matched then update set valoare=a.nou
when not matched then
INSERT (date_created, created_by, date_modified, modified_by, id, atribut_id, valoare)
VALUES( sysdate, user, sysdate, user, a.id, v_atribut_id, a.nou);
commit;
end;
If I try to execute the procedure
begin
gen_nou ('31-jan-2007');
end;
I get the following error:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
If I modify the procedure as below the procedure can be executed without errors:
procedure gen_nou (p_data date) is
v_atribut_id number;
begin
v_atribut_id=100;
merge into TABLE1 x
using (select distinct id, 'D' nou from TABLE2
where data='31-jan-2007'
) a
on (x.atribut_id=v_atribut_id
and x.id=)
when matched then update set valoare=a.nou
when not matched then
INSERT (date_created, created_by, date_modified, modified_by, id, atribut_id, valoare)
VALUES( sysdate, user, sysdate, user, a.id, v_atribut_id, a.nou);
commit;
end;
Could you help me to understand why it fails when I try to set with parameter p_data in MERGE statement?
Thank you!!!
Maria
5 3850
Could you provide the table1 and table2 DDL.
Just as a followup. i suspect the issue between the 2 procedures is the following:
in the first statement you are comparing a date parameter to table2.data
and in the second you are comparing a string (varchar2) to table2.data
using (select distinct id, 'D' nou
from TABLE2
where data=p_data) a
using (select distinct id, 'D' nou
from TABLE2
where data='31-jan-2007' ) a
so if the second procedure works i suspect it is because table2.data is of type varchar2 or char. so you would need to use something like the following:
using (select distinct id, 'D' nou
from TABLE2
where data=to_char(p_data,'dd-mon-yyyy') a
assuming '31-jan-2007' is the correct format.
Hello,
I'm trying to use MERGE statement in a procedure, please see the code below:
procedure gen_nou (p_data date) is
v_atribut_id number;
begin
v_atribut_id=100;
merge into TABLE1 x
using (select distinct id, 'D' nou from TABLE2
where data=p_data) a
on (x.atribut_id=v_atribut_id
and x.id=)
when matched then update set valoare=a.nou
when not matched then
INSERT (date_created, created_by, date_modified, modified_by, id, atribut_id, valoare)
VALUES( sysdate, user, sysdate, user, a.id, v_atribut_id, a.nou);
commit;
end;
If I try to execute the procedure
begin
gen_nou ('31-jan-2007');
end;
I get the following error:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
If I modify the procedure as below the procedure can be executed without errors:
procedure gen_nou (p_data date) is
v_atribut_id number;
begin
v_atribut_id=100;
merge into TABLE1 x
using (select distinct id, 'D' nou from TABLE2
where data='31-jan-2007'
) a
on (x.atribut_id=v_atribut_id
and x.id=)
when matched then update set valoare=a.nou
when not matched then
INSERT (date_created, created_by, date_modified, modified_by, id, atribut_id, valoare)
VALUES( sysdate, user, sysdate, user, a.id, v_atribut_id, a.nou);
commit;
end;
Could you help me to understand why it fails when I try to set with parameter p_data in MERGE statement?
Thank you!!!
Maria
try this for your call begin
gen_nou (to_date('31-jan-2007','yy-mon-yyyy'));
end;
try this for your call begin
gen_nou (to_date('31-jan-2007','yy-mon-yyyy'));
end;
sorry the call is
B]begin
gen_nou (to_date('31-jan-2007','dd-mon-yyyy'));
end;[/b][/quote]
sorry the call is
B]begin
gen_nou (to_date('31-jan-2007','dd-mon-yyyy'));
end;[/b]
[/quote]
the error message leads me to beleive that the statement was trying to compare to different data types.
usually if the call of a function or procedure is wrong you get the " Wrong number or types in call to..."
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Private Pyle |
last post by:
Version 8.1 fixpack 5, AIX 5.1 32-bit
I have a situation where I want to use a merge statement to perform
insert else update logic to move data from one table to another. I
have a working merge...
|
by: Bob Stearns |
last post by:
Does the following message get generated before or after the additional
conditions given in the merge are evaluated?
Error: The same row of target table "NULLID.AICA" was identified more
than...
|
by: rebeaj |
last post by:
Merge SQL statement will not compile. Can anyone help me out?
Create Or Replace Procedure P_pvt_merge
As
Begin
Merge Into Employees2 B
Using (
Select Employee_id, First_name, Last_name,...
|
by: marklawford |
last post by:
Hi guys,
I'm getting the following error message when trying to run a MERGE
statement I'm putting together. The syntax looks right to me But i must
be missing something. The "srce" table...
|
by: UDBDBA |
last post by:
Hi All:
I need some clarification on a MERGE statement. The database is on V8
FP12 (AIX) 64bit.
The source table is tableA.
The target is a View "FACT" with UNION ALL because of the 512 Gig...
|
by: Sam Durai |
last post by:
Hello, I need to merge a small table (of rows less than 100,sometimes
even 0 rows) to a big table (of rows around 4 billion). I used the PK
of the big table as merge key but merge does a table scan...
|
by: UDBDBA |
last post by:
Hi,
If someone can clarify this, it would be of great help!
We have a merge, which is written *only* to update the target table.
but the access plan shows branches with inserts and deletes on...
|
by: Michel Esber |
last post by:
Hi all,
DB2 V8 LUW FP 15
There is a table T (ID varchar (24), ABC timestamp). ID is PK.
Our application needs to frequently update T with a new value for ABC.
update T set ABC=? where ID...
|
by: Henry J. |
last post by:
I got a dumb question on the merge statement. I read the following
example of merge statement at the IBM page:...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |