ORA-01722以下报错
select a.rowid, a.* from result a
WHERE id = 2
and key_id in (select t.value from my_value t where t.key_id = '0001')id number(4)以下正确1:
select a.rowid, a.* from result a
WHERE to_char(id) = to_char(2)
and key_id in (select t.value from my_value t where t.key_id = '0001')以下正确2:
select a.rowid, a.* from result a
WHERE id = 2
select a.rowid, a.* from result a
WHERE id = 2
and key_id in (select t.value from my_value t where t.key_id = '0001')id number(4)以下正确1:
select a.rowid, a.* from result a
WHERE to_char(id) = to_char(2)
and key_id in (select t.value from my_value t where t.key_id = '0001')以下正确2:
select a.rowid, a.* from result a
WHERE id = 2
解决方案 »
- 将大批量数据导入到 orcal
- 求oracle数据库全备份+日志备份方案 谢谢
- dblink权限控制问题
- 如何提高Oracle对系统CPU和IO的利用率
- pl/sql
- 如何将rowtype或RECORD当做参数传递给procedure????
- 两个关联的表关系应该怎么建
- oracel触发器,每隔一天触发一次?谁有示例?急用.
- 对函数返回值格式的要求!!!
- 刚开始学oracle9i,怎样打开跟SQL里面一样的企业管理器建表啊
- Oracle Job:我想每天中午12:00,下午6:00定时调用JOB,如何设置Job的interval(执行间隔时间)?
- 为什么下载下来的OCL(oracle class library),里面的lib是空的?
谢谢!
create table AUDIT_DATA_RESULT
(
ID NUMBER(13) not null,
ENTITY_ID NUMBER(4) not null,
KEY_ID NUMBER(13) not null,
RESULT_CODE NUMBER(2)
)
空格一些细节问题,应该不存在
select a.rowid, a.* from result a
WHERE id = 2
and key_id in (select t.value from my_value t where t.key_id = '0001')id number(4)以下正确1:
select a.rowid, a.* from result a
WHERE to_char(id) = to_char(2)
and key_id in (select t.value from my_value t where t.key_id = '0001')
---------------------------------------------------------------------这2个语句是在同一个会话窗口执行的吗?字段value类型会不会是字符型的,
执行
select a.rowid, a.* from result a
WHERE id = 2
and key_id in (select t.value from my_value t where t.key_id = '0001')
前有没有insert了不能转换为数字的字符串?
create table AUDIT_DATA_RESULT
(
ID NUMBER(13) not null,
ENTITY_ID NUMBER(4) not null,
KEY_ID NUMBER(13) not null,
RESULT_CODE NUMBER(2)
)
create table RESULT
(
ID NUMBER(13) not null,
ENTITY_ID NUMBER(4) not null,
KEY_ID NUMBER(13) not null,
RESULT_CODE NUMBER(2)
)
;
create table my_value(key_id varchar2(10));
alter table my_value add "value" number;
select * from result for update;
select * from my_value for update;
insert into my_value values('0002',2);select "value" from my_value
select a.rowid, a.* from result a
WHERE id = 2
and key_id in (select t."value" from my_value t where t.key_id = '0001')
ROWID ID ENTITY_ID KEY_ID RESULT_CODE
AAAIAJAAIAAAAKSAAD 2 3 2 1
VALUE是系统自带的关键字,你换个列名试试
为什么select的是t.value,key_id与t.value的类型相同吗?
t.APPROVE_ID as "approveId" ,
t.DEV_ACTION_ID as "devActionId" ,
t.APPROVOR_ID as "approvorId" ,
(select tt.emp_name from t_hrim_employee tt where tt.emp_code =t.approve_id) as "approvorName" ,
t.RESULT as "result" ,
t.OPINION as "opinion" ,
t.PRE_STATUS as "preStatus" ,
t.AFTER_STATUS as "afterStatus" ,
t.CREATER as "creater" ,
To_CHAR(t.CREATE_DATE,'YYYY-MM-DD') as "createDate" ,
t.MODIFIER as "modifier" ,
To_CHAR(t.MODIFY_DATE,'YYYY-MM-DD') as "modifyDate"
FROM T_HRED_DEV_ACTION_APPROVE t ;
SELECT
t.ASSESS_ID as "assessId" ,
t.SUMMARY_ID as "summaryId" ,
t.TEACHER_EMP_CODE as "teacherEmpCode" ,
(select tt.emp_name from t_hrim_employee tt where tt.emp_code= t.TEACHER_EMP_CODE) as "teacherEmpName" ,
t.EMP_CODE as "empCode" ,
(select tt.emp_name from t_hrim_employee tt where tt.emp_code=t.EMP_CODE) as "empName" ,
t.ASSESS as "assess" ,
t.STATUS as "status" ,
To_CHAR(t.FROM_DATE,'YYYY-MM-DD') as "fromDate" ,
To_CHAR(t.TO_DATE,'YYYY-MM-DD') as "toDate" ,
t.ALIVE_FLAG as "aliveFlag" ,
CREATER as "creater" ,
To_CHAR(t.CREATE_DATE,'YYYY-MM-DD') as "createDate" ,
MODIFIER as "modifier" ,
To_CHAR(t.MODIFY_DATE,'YYYY-MM-DD') as "modifyDate"
FROM T_HRED_TEACHER_ASSESS t ;
和我这个问题一样,其实就是在传参查询的时候,字段类型不能为number类型 ,改成字符串类型就行了-- Create table
create table XHTR.T_HRED_DEV_ACTION_APPROVE
(
APPROVE_ID NUMBER(20) not null,
DEV_ACTION_ID NUMBER(20),
APPROVOR_ID VARCHAR2(16),
RESULT VARCHAR2(16),
OPINION VARCHAR2(1024),
PRE_STATUS VARCHAR2(16),
AFTER_STATUS VARCHAR2(16),
CREATER NUMBER(20),
CREATE_DATE DATE,
MODIFIER NUMBER(20),
MODIFY_DATE DATE
)
tablespace DS_XHTR01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table XHTR.T_HRED_DEV_ACTION_APPROVE
is '员工培养活动审批记录';
-- Add comments to the columns
comment on column XHTR.T_HRED_DEV_ACTION_APPROVE.APPROVE_ID
is '审批记录ID';
comment on column XHTR.T_HRED_DEV_ACTION_APPROVE.DEV_ACTION_ID
is '培养信息ID';
comment on column XHTR.T_HRED_DEV_ACTION_APPROVE.APPROVOR_ID
is '审批人';
comment on column XHTR.T_HRED_DEV_ACTION_APPROVE.RESULT
is '审批结果';
comment on column XHTR.T_HRED_DEV_ACTION_APPROVE.OPINION
is '意见';
comment on column XHTR.T_HRED_DEV_ACTION_APPROVE.PRE_STATUS
is '审批前状态';
comment on column XHTR.T_HRED_DEV_ACTION_APPROVE.AFTER_STATUS
is '审批后状态';
comment on column XHTR.T_HRED_DEV_ACTION_APPROVE.CREATER
is '创建人';
comment on column XHTR.T_HRED_DEV_ACTION_APPROVE.CREATE_DATE
is '创建时间';
comment on column XHTR.T_HRED_DEV_ACTION_APPROVE.MODIFIER
is '修改人';
comment on column XHTR.T_HRED_DEV_ACTION_APPROVE.MODIFY_DATE
is '修改时间';
-- Create/Recreate primary, unique and foreign key constraints
alter table XHTR.T_HRED_DEV_ACTION_APPROVE
add constraint PK_T_HRED_DEV_ACTION_APPROVE primary key (APPROVE_ID)
using index
tablespace DS_XHTR01
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Grant/Revoke object privileges
grant select, insert, update, delete on XHTR.T_HRED_DEV_ACTION_APPROVE to XHADMIN;
-- Create table
create table XHED.T_HRED_TEACHER_ASSESS
(
ASSESS_ID NUMBER(20) not null,
SUMMARY_ID NUMBER(20),
TEACHER_EMP_CODE VARCHAR2(16),
EMP_CODE VARCHAR2(16),
ASSESS VARCHAR2(1024),
STATUS VARCHAR2(16),
FROM_DATE DATE,
TO_DATE DATE,
ALIVE_FLAG VARCHAR2(2),
CREATER NUMBER(20),
CREATE_DATE DATE,
MODIFIER NUMBER(20),
MODIFY_DATE DATE
)
tablespace DS_XHED01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table XHED.T_HRED_TEACHER_ASSESS
is '导师评价';
-- Add comments to the columns
comment on column XHED.T_HRED_TEACHER_ASSESS.ASSESS_ID
is '评价ID';
comment on column XHED.T_HRED_TEACHER_ASSESS.SUMMARY_ID
is '总结ID';
comment on column XHED.T_HRED_TEACHER_ASSESS.TEACHER_EMP_CODE
is '导师工号';
comment on column XHED.T_HRED_TEACHER_ASSESS.EMP_CODE
is '员工工号';
comment on column XHED.T_HRED_TEACHER_ASSESS.ASSESS
is '评价';
comment on column XHED.T_HRED_TEACHER_ASSESS.STATUS
is '状态';
comment on column XHED.T_HRED_TEACHER_ASSESS.FROM_DATE
is '开始日期';
comment on column XHED.T_HRED_TEACHER_ASSESS.TO_DATE
is '结束日期';
comment on column XHED.T_HRED_TEACHER_ASSESS.ALIVE_FLAG
is '有效标志';
comment on column XHED.T_HRED_TEACHER_ASSESS.CREATER
is '创建人';
comment on column XHED.T_HRED_TEACHER_ASSESS.CREATE_DATE
is '创建时间';
comment on column XHED.T_HRED_TEACHER_ASSESS.MODIFIER
is '修改人';
comment on column XHED.T_HRED_TEACHER_ASSESS.MODIFY_DATE
is '修改时间';
-- Create/Recreate primary, unique and foreign key constraints
alter table XHED.T_HRED_TEACHER_ASSESS
add constraint PK_T_HRED_TEACHER_ASSESS primary key (ASSESS_ID)
using index
tablespace DS_XHED01
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Grant/Revoke object privileges
grant select, insert, update, delete on XHED.T_HRED_TEACHER_ASSESS to XHADMIN;