Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00917 error
icon5.gif  ORA-00917 error [message #265214] Wed, 05 September 2007 15:13 Go to next message
jaylrob
Messages: 6
Registered: August 2007
Junior Member
I'm getting an error with on the last coalesce statement. Its odd, b/c it mirrors a previous one. Any suggestions? Is the concatenation not allowed within coalesce?


VALUES (

...

A.di_loc_address_1,
coalesce(A.di_loc_address_2,A.di_loc_city||' '||A.di_loc_state||' '||A.di_loc_zip),
A.name,

...

A.di_mgr_title,
A.di_ship_address_1,
coalesce(A.di_loc_address_2,A.di_loc_city||' '||A.di_loc_state||' '||A.di_loc_zip),
(CASE A.Di_Status
      When 'Active' Then '-1'
      When 'Inactive' Then '0'
End)

Re: ORA-00917 error [message #265243 is a reply to message #265214] Wed, 05 September 2007 21:58 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
ORA-00917 missing comma
Cause: A required comma has been omitted from a list of columns or values in
an INSERT statement or a list of the form ((C,D),(E,F), ...).
Action: Correct the syntax.


May be one of comma is ommit. Please provide the full insert statement.
Re: ORA-00917 error [message #265476 is a reply to message #265243] Thu, 06 September 2007 07:15 Go to previous messageGo to next message
jaylrob
Messages: 6
Registered: August 2007
Junior Member
Below is full insert

WHEN NOT MATCHED THEN
INSERT (

T.dealerid,
T.dealername,
T.dealerno,
T.fedid,
T.oldate,
T.mailaddress1,
T.mailaddress2,
T.state,
T.locaddress1,
T.locaddress2,
T.contact,
T.position,
T.telephone1,
T.teleext,
T.fax,
T.dealertype,
T.district,
T.provider,
T.notes,
T.title,
T.mgrfirstname,
T.mgrlastname,
T.mgrtitle,
T.shipaddress1,
T.shipaddress2,
T.active_status
)


VALUES (

A.di_dealer_id,
A.di_dealer_name,
A.di_dealer_no,
A.di_fed_id,
A.di_online_dte,
A.di_mail_address_1,
coalesce(A.di_mail_address_2,A.di_mail_city||' '||A.di_mail_state||' '||A.di_mail_zip),
A.di_state,
A.di_loc_address_1,
coalesce(A.di_loc_address_2,A.di_loc_city||' '||A.di_loc_state||' '||A.di_loc_zip),
A.name,
A.ct_job_title,
A.ct_tel_no,
A.ct_ext,
A.ct_fax,
A.di_dealer_type,
A.di_district,
A.di_provider,
A.di_notes,
A.ct_title,
substr(A.di_mgr_name, instr(A.di_mgr_name,' ') - 1),
substr(A.di_mgr_name, instr(A.di_mgr_name,' ') + 1),
A.di_mgr_title,
A.di_ship_address_1,
coalesce(A.di_loc_address_2,A.di_loc_city||' '||A.di_loc_state||' '||A.di_loc_zip),
(CASE A.Di_Status
      When 'Active' Then '-1'
      When 'Inactive' Then '0'
End)

);

Re: ORA-00917 error [message #265482 is a reply to message #265214] Thu, 06 September 2007 07:22 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Quote:

INSERT (


will be
INSERT into yourtable(


For insert data into table syntax is
insert into table_name(column_list) values
(value)

[Updated on: Thu, 06 September 2007 07:25]

Report message to a moderator

Re: ORA-00917 error [message #265495 is a reply to message #265482] Thu, 06 September 2007 07:35 Go to previous messageGo to next message
jaylrob
Messages: 6
Registered: August 2007
Junior Member
I'm sorry. I was using a merge

CREATE OR REPLACE PROCEDURE ... AS
BEGIN

MERGE INTO ... T
USING (SELECT * FROM ...) A
ON (T.dealerid = A.di_dealer_id)
WHEN MATCHED THEN UPDATE SET

T.dealername = A.di_dealer_name,
T.dealerno = A.di_dealer_no,
T.fedid = A.di_fed_id,
T.oldate = A.di_online_dte,
T.mailaddress1 = A.di_mail_address_1,
T.mailaddress2 = coalesce(A.di_mail_address_2,A.di_mail_city||' '||A.di_mail_state||' '||A.di_mail_zip),
T.state = A.di_state,
T.locaddress1 = A.di_loc_address_1,
T.locaddress2 = coalesce(A.di_loc_address_2,A.di_loc_city||' '||A.di_loc_state||' '||A.di_loc_zip),
T.contact = A.name,
T.position = A.ct_job_title,
T.telephone1 = A.ct_tel_no,
T.teleext = A.ct_ext,
T.fax = A.ct_fax,
T.dealertype = A.di_dealer_type,
T.district = A.di_district,
T.provider = A.di_provider,
T.notes = A.di_notes,
T.title = A.ct_title,
T.mgrfirstname = substr(A.di_mgr_name, instr(A.di_mgr_name,' ') - 1),
T.mgrlastname = substr(A.di_mgr_name, instr(A.di_mgr_name,' ') + 1),
T.mgrtitle = A.di_mgr_title,
T.shipaddress1 = A.di_ship_address_1,
T.shipaddress2 = coalesce(A.di_loc_address_2,A.di_loc_city||' '||A.di_loc_state||' '||A.di_loc_zip),
T.Active_Status = (CASE A.Di_Status
                        When 'Active' Then '-1'
                        when 'Inactive' Then '0'
                   End)


WHEN NOT MATCHED THEN
INSERT (

T.dealerid,
T.dealername,
T.dealerno,
T.fedid,
T.oldate,
T.mailaddress1,
T.mailaddress2,
T.state,
T.locaddress1,
T.locaddress2,
T.contact,
T.position,
T.telephone1,
T.teleext,
T.fax,
T.dealertype,
T.district,
T.provider,
T.notes,
T.title,
T.mgrfirstname,
T.mgrlastname,
T.mgrtitle,
T.shipaddress1,
T.shipaddress2,
T.active_status
)


VALUES (

A.di_dealer_id,
A.di_dealer_name,
A.di_dealer_no,
A.di_fed_id,
A.di_online_dte,
A.di_mail_address_1,
coalesce(A.di_mail_address_2,A.di_mail_city||' '||A.di_mail_state||' '||A.di_mail_zip),
A.di_state,
A.di_loc_address_1,
coalesce(A.di_loc_address_2,A.di_loc_city||' '||A.di_loc_state||' '||A.di_loc_zip),
A.name,
A.ct_job_title,
A.ct_tel_no,
A.ct_ext,
A.ct_fax,
A.di_dealer_type,
A.di_district,
A.di_provider,
A.di_notes,
A.ct_title,
substr(A.di_mgr_name, instr(A.di_mgr_name,' ') - 1),
substr(A.di_mgr_name, instr(A.di_mgr_name,' ') + 1),
A.di_mgr_title,
A.di_ship_address_1,
coalesce(A.di_loc_address_2,A.di_loc_city||' '||A.di_loc_state||' '||A.di_loc_zip),
(CASE A.Di_Status
      When 'Active' Then '-1'
      When 'Inactive' Then '0'
End)

);
END;

[Updated on: Thu, 06 September 2007 07:35]

Report message to a moderator

Re: ORA-00917 error [message #265505 is a reply to message #265214] Thu, 06 September 2007 08:21 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
my post was wrong

[Updated on: Thu, 06 September 2007 11:21]

Report message to a moderator

Re: ORA-00917 error [message #265552 is a reply to message #265505] Thu, 06 September 2007 11:30 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you only use two values, you could use a simple nvl.

Tested it in XE (10.2.0.1)
My script
create table tab_t
( id  number
, txt varchar2(20)
);
create table tab_a
( id number
, txt varchar2(20)
);

insert all
into tab_a values (1, 'A_ONE')
into tab_a values (2, 'A_TWO')
into tab_t values (1, 'T_ONE')
select * from dual;

merge into tab_t t
using (select * from tab_a) a
on (t.id = a.id)
when matched then update set t.txt = t.txt || '/' || a.txt
when not matched then
insert (
t.id,
t.txt
) values (
a.id,
coalesce(a.txt, '#'||a.txt||'#')
);

select * from tab_t; 


The result:
SQL> create table tab_t
  2  ( id  number
  3  , txt varchar2(20)
  4  );

Table created.

SQL> create table tab_a
  2  ( id number
  3  , txt varchar2(20)
  4  );

Table created.

SQL>
SQL> insert all
  2  into tab_a values (1, 'A_ONE')
  3  into tab_a values (2, 'A_TWO')
  4  into tab_t values (1, 'T_ONE')
  5  select * from dual;

3 rows created.

SQL>
SQL> merge into tab_t t
  2  using (select * from tab_a) a
  3  on (t.id = a.id)
  4  when matched then update set t.txt = t.txt || '/' || a.txt
  5  when not matched then
  6  insert (
  7  t.id,
  8  t.txt
  9  ) values (
 10  a.id,
 11  coalesce(a.txt, '#'||a.txt||'#')
 12  );

2 rows merged.

SQL>
SQL> select * from tab_t;

        ID TXT
---------- --------------------
         1 T_ONE/A_ONE
         2 A_TWO
Previous Topic: table creation date
Next Topic: ORA-29275 partial multibyte character
Goto Forum:
  


Current Time: Wed Dec 07 22:35:03 CST 2016

Total time taken to generate the page: 0.10280 seconds