Home » Developer & Programmer » Forms » ora-00911 invalid character (oracle 10g )
| ora-00911 invalid character [message #577030] |
Tue, 12 February 2013 01:30  |
kame
Messages: 69
Registered: July 2009
|
Member |
|
|
I am using this query in MERGE statement , result is also shows here
select TO_CHAR(emp_id) EMP_ID ,TO_DATE(at_date,'RRRR/MM/DD') AT_DATE,TO_CHAR(time_in) TIME_IN,TO_CHAR(time_out) TIME_OUT,TO_CHAR(status) STATUS from (
select emp_code emp_id,at_date,at_time Time_in, Null Time_out, status from (
select a.*,substr(data_row,24,5) Emp_code,substr(data_row,3,8) AT_Date, substr(data_row,11,4) At_Time,
substr(data_row,15,2) Shift, substr(data_row,17,2) STatus
from data_load a
Order by 1 )
where status = 01
union all
select emp_code emp_id,at_date,null Time_in, at_time Timeout, status from (
select a.*,substr(data_row,24,5) Emp_code,substr(data_row,3,8) AT_Date, substr(data_row,11,4) At_Time,
substr(data_row,15,2) Shift, substr(data_row,17,2) STatus
from data_load a
Order by 1 )
where status = 02
) order by emp_id, at_date,status
EMP_ID AT_DATE TIME_IN TIME_OUT STATUS
------ --------------------- ------- -------- ------
00002 12/26/2012 0933 01
00002 12/26/2012 2044 02
00002 12/27/2012 0926 01
00003 12/26/2012 1850 02
00003 12/27/2012 0741 01
00004 12/26/2012 1117 01
00004 12/26/2012 2023 02
00004 12/27/2012 1050 01
00005 12/26/2012 0932 01
00005 12/26/2012 2031 02
00005 12/27/2012 0926 01
00006 12/27/2012 0940 01
00007 12/27/2012 0940 01
00009 12/27/2012 0940 01
00010 12/27/2012 0940 01
Table structure is:
Table:
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_ID CHAR(8)
AT_DATE DATE
TIME_IN VARCHAR2(4)
TIME_OUT VARCHAR2(4)
STATUS VARCHAR2(2)
SQL>
using this query in MERGE statement giving a error ora-00911 invalid character
MERGE INTO Attendance I
USING (
select TO_CHAR(emp_id) EMP_ID ,TO_DATE(at_date,'RRRR/MM/DD') AT_DATE,TO_CHAR(time_in) TIME_IN,TO_CHAR(time_out) TIME_OUT,TO_CHAR(status) STATUS from (
select emp_code emp_id,at_date,at_time Time_in, Null Time_out, status from (
select a.*,substr(data_row,24,5) Emp_code,substr(data_row,3,8) AT_Date, substr(data_row,11,4) At_Time,
substr(data_row,15,2) Shift, substr(data_row,17,2) STatus
from data_load a
Order by 1 )
where status = '01'
union all
select emp_code emp_id,at_date,null Time_in, at_time Timeout, status from (
select a.*,substr(data_row,24,5) Emp_code,substr(data_row,3,8) AT_Date, substr(data_row,11,4) At_Time,
substr(data_row,15,2) Shift, substr(data_row,17,2) STatus
from data_load a
Order by 1 )
where status = '02'
) order by emp_id, at_date,status
) E
ON (i.emp_id = e.emp_id)
WHEN MATCHED THEN
UPDATE SET I.EMP_ID = E.EMP_ID, I.AT_DATE = E.AT_DATE, I.TIME_IN = E.TIME_IN, I.TIME_OUT = E.TIME_OUT,I.STATUS=E.STATUS
WHEN NOT MATCHED THEN
INSERT (EMP_ID,AT_DATE,TIME_IN,TIME_OUT,STATUS)
VALUES (E.EMP_ID,E.AT_DATE,E.TIME_IN,E.TIME_OUT,E.STATUS) ;
|
|
|
|
|
|
| Re: ora-00911 invalid character [message #577034 is a reply to message #577031] |
Tue, 12 February 2013 01:47  |
kame
Messages: 69
Registered: July 2009
|
Member |
|
|
Create table Data_load (
Data_row Varchar2(32) )
NSERT INTO data_load
VALUES (31201212260701000100000000700001);
INSERT INTO data_load
VALUES (31201212260759000100000000390001);
INSERT INTO data_load
VALUES (31201212260800000100000010020001);
INSERT INTO data_load
VALUES (31201212260932000100000000050001);
INSERT INTO data_load
VALUES (31201212260932000100000000370001);
INSERT INTO data_load
VALUES (31201212260933000100000000020001);
INSERT INTO data_load
VALUES (31201212260933000100000000300001);
INSERT INTO data_load
VALUES (31201212260933000100000000550001);
INSERT INTO data_load
VALUES (31201212260933000100000000570001);
INSERT INTO data_load
VALUES (31201212260933000100000000150001);
INSERT INTO data_load
VALUES (31201212260933000100000000560001);
INSERT INTO data_load
VALUES (31201212260933000100000000240001);
INSERT INTO data_load
VALUES (31201212261026000100000000230001);
|
|
|
|
| Re: ora-00911 invalid character [message #577039 is a reply to message #577034] |
Tue, 12 February 2013 02:07 |
 |
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
1/ VALUES (31201212261026000100000000230001) a NUMBER is NOT a STRING
2/
SQL> merge INTO attendance I
2 USING (SELECT To_char(emp_id) EMP_ID,
3 To_date(at_date, 'RRRR/MM/DD') AT_DATE,
4 To_char(time_in) TIME_IN,
5 To_char(time_out) TIME_OUT,
6 To_char(status) STATUS
7 FROM (SELECT emp_code emp_id,
8 at_date,
9 at_time Time_in,
10 NULL Time_out,
11 status
12 FROM (SELECT a.*,
13 Substr(data_row, 24, 5) Emp_code,
14 Substr(data_row, 3, 8) AT_Date,
15 Substr(data_row, 11, 4) At_Time,
16 Substr(data_row, 15, 2) Shift,
17 Substr(data_row, 17, 2) STatus
18 FROM data_load a
19 ORDER BY 1)
20 WHERE status = '01'
21 UNION ALL
22 SELECT emp_code emp_id,
23 at_date,
24 NULL Time_in,
25 at_time Timeout,
26 status
27 FROM (SELECT a.*,
28 Substr(data_row, 24, 5) Emp_code,
29 Substr(data_row, 3, 8) AT_Date,
30 Substr(data_row, 11, 4) At_Time,
31 Substr(data_row, 15, 2) Shift,
32 Substr(data_row, 17, 2) STatus
33 FROM data_load a
34 ORDER BY 1)
35 WHERE status = '02')
36 ORDER BY emp_id,
37 at_date,
38 status) e
39 ON (i.emp_id = e.emp_id)
40 WHEN matched THEN
41 UPDATE SET I.emp_id = e.emp_id,
42 I.at_date = e.at_date,
43 I.time_in = e.time_in,
44 I.time_out = e.time_out,
45 I.status = e.status
46 WHEN NOT matched THEN
47 INSERT (emp_id,
48 at_date,
49 time_in,
50 time_out,
51 status)
52 VALUES (e.emp_id,
53 e.at_date,
54 e.time_in,
55 e.time_out,
56 e.status);
ON (i.emp_id = e.emp_id)
*
ERROR at line 39:
ORA-00904: "I"."EMP_ID": invalid identifier
I have not your "attendance" table
3/ Don't you think the way the formatter formats your query FAR FAR easier to read than your?
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Mon Jul 01 10:27:07 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
