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
解决方案 »
- 如何将一个dump格式的数据文件导入到PLSQL Developer中?
- sql语句求解
- 问一个问题
- oracle xmldb 操作xml节点
- $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$关于ORA-00937 不是单组分组函数 问题,在线等,解决即给分$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
- sql脚本中使用条件判断
- 请问:存储过程调用中的参数传递问题
- ORACLE出现错误警告,导致无法连接:oracle instance jh - can not allocate log , archival required
- 帮忙看一下,这个Update是什么意思?
- 如何能够在存储过程的代码段定义游标呢?
- 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;