473,324 Members | 2,456 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

merge statement

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
Feb 28 '07 #1
5 3850
Dave44
153 100+
Could you provide the table1 and table2 DDL.
Feb 28 '07 #2
Dave44
153 100+
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.
Feb 28 '07 #3
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;
Mar 2 '07 #4
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]
Mar 2 '07 #5
Dave44
153 100+
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..."
Mar 3 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

0
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...
1
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...
1
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,...
4
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...
16
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...
16
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...
1
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...
3
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...
7
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:...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
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...
0
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...
0
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...
1
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)...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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...
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.