今天执行一个sql语句,报错 java.sql.SQLSyntaxErrorException: ORA-00942,这是sql语句:
SELECT p.proj_id As PROJECTDBID,
proj.PROJ_SHORT_NAME PROJECTID,
p.wbs_name PROJNAME,
P0.UDF_TEXT As MRPNumber,
e.EPS,
SUBSTR(e.EPS, instr(e.EPS, '.', -1) + 1) AS EPS_NODE
FROM PROJWBS p
INNER JOIN PROJECT proj ON p.proj_id = proj.proj_id
INNER JOIN (SELECT pc.proj_id,
pv.proj_catg_short_name,
pt.proj_catg_type_id,
pv.proj_catg_id
FROM PROJPCAT PC
INNER JOIN PCATVAL PV on pc.proj_catg_id = pv.proj_catg_id
INNER JOIN PCATTYPE PT on pt.proj_catg_type_id =
pv.proj_catg_type_id
WHERE pt.proj_catg_type =
'Mandatory Integration - SyncProject') pcvt1 on p.proj_id =
pcvt1.proj_id
AND pcvt1.proj_catg_short_name = 'Y'
LEFT JOIN (SELECT FK_ID, UDF_TEXT, UDF_DATE, UDF_NUMBER, 'Y' AS UDF_EXISTS
FROM UDFVALUE UV
JOIN UDFTYPE UT ON UT.UDF_TYPE_ID = UV.UDF_TYPE_ID
AND UT.UDF_TYPE_LABEL LIKE
'MAND INT- DS COMMOPS ERP PROJ#'
AND UT.TABLE_NAME LIKE 'PROJECT') P0 ON P0.FK_ID =
P.PROJ_ID
INNER JOIN (SELECT p.WBS_ID,
REPLACE(trim(leading '\' FROM
sys_connect_by_path(p.wbs_short_name, '\')),
'\',
'.') EPS,
LEVEL
FROM PROJWBS p
INNER JOIN PROJECT proj ON p.proj_id = proj.proj_id
START WITH p.parent_wbs_id IS NULL
CONNECT BY PRIOR p.wbs_id = p.parent_wbs_id
AND proj.project_flag = 'N') e ON e.WBS_ID =
p.parent_wbs_id
WHERE proj.project_flag = 'Y'
AND p.proj_node_flag = 'Y'
AND proj.orig_proj_id IS NULL
AND (UPPER(proj.PROJ_SHORT_NAME) like ? or UPPER(p.wbs_name) like ?)
ORDER BY EPS, EPS_NODE, PROJNAME
java调用:
String projectUp = "%" + project.toUpperCase() + "%";
StringBuffer totalSQL = new StringBuffer(" SELECT count(*) FROM (");
totalSQL.append(getProjects);
totalSQL.append(") temp");
System.out.println("totalSQL="+totalSQL); return jdbcTemplate.queryForInt(totalSQL.toString(),new String[]{projectUp,projectUp});
其中project是从页面传递的参数,getProject就是指向如上sql语句的属性,有setter,getter方法,希望大家帮忙解决,不胜感激
SELECT p.proj_id As PROJECTDBID,
proj.PROJ_SHORT_NAME PROJECTID,
p.wbs_name PROJNAME,
P0.UDF_TEXT As MRPNumber,
e.EPS,
SUBSTR(e.EPS, instr(e.EPS, '.', -1) + 1) AS EPS_NODE
FROM PROJWBS p
INNER JOIN PROJECT proj ON p.proj_id = proj.proj_id
INNER JOIN (SELECT pc.proj_id,
pv.proj_catg_short_name,
pt.proj_catg_type_id,
pv.proj_catg_id
FROM PROJPCAT PC
INNER JOIN PCATVAL PV on pc.proj_catg_id = pv.proj_catg_id
INNER JOIN PCATTYPE PT on pt.proj_catg_type_id =
pv.proj_catg_type_id
WHERE pt.proj_catg_type =
'Mandatory Integration - SyncProject') pcvt1 on p.proj_id =
pcvt1.proj_id
AND pcvt1.proj_catg_short_name = 'Y'
LEFT JOIN (SELECT FK_ID, UDF_TEXT, UDF_DATE, UDF_NUMBER, 'Y' AS UDF_EXISTS
FROM UDFVALUE UV
JOIN UDFTYPE UT ON UT.UDF_TYPE_ID = UV.UDF_TYPE_ID
AND UT.UDF_TYPE_LABEL LIKE
'MAND INT- DS COMMOPS ERP PROJ#'
AND UT.TABLE_NAME LIKE 'PROJECT') P0 ON P0.FK_ID =
P.PROJ_ID
INNER JOIN (SELECT p.WBS_ID,
REPLACE(trim(leading '\' FROM
sys_connect_by_path(p.wbs_short_name, '\')),
'\',
'.') EPS,
LEVEL
FROM PROJWBS p
INNER JOIN PROJECT proj ON p.proj_id = proj.proj_id
START WITH p.parent_wbs_id IS NULL
CONNECT BY PRIOR p.wbs_id = p.parent_wbs_id
AND proj.project_flag = 'N') e ON e.WBS_ID =
p.parent_wbs_id
WHERE proj.project_flag = 'Y'
AND p.proj_node_flag = 'Y'
AND proj.orig_proj_id IS NULL
AND (UPPER(proj.PROJ_SHORT_NAME) like ? or UPPER(p.wbs_name) like ?)
ORDER BY EPS, EPS_NODE, PROJNAME
java调用:
String projectUp = "%" + project.toUpperCase() + "%";
StringBuffer totalSQL = new StringBuffer(" SELECT count(*) FROM (");
totalSQL.append(getProjects);
totalSQL.append(") temp");
System.out.println("totalSQL="+totalSQL); return jdbcTemplate.queryForInt(totalSQL.toString(),new String[]{projectUp,projectUp});
其中project是从页面传递的参数,getProject就是指向如上sql语句的属性,有setter,getter方法,希望大家帮忙解决,不胜感激
解决方案 »
- pl/sql菜鸟求助
- 求一条sql语句
- rac关闭一个节点后,客户端断开连接的问题
- 急:游标中使用变量出现错误
- 请问怎样将两个字符型的日期比较大小?
- 我想创建一个用户,用该用户名登陆后只能看该用户名对应的模式
- 用OCI如何插入LONG字段?每次都提示:00CFE264 "Error - ORA-01704: string literal too long\n"
- Oracle入门大概要多久时间
- 想不通的问题:运行正确, 过程编译出错???????????
- 关于递归或迭代的问题
- oracle存储过程有 out游标的存数过程怎么调用 请大师帮忙
- 新手,求写一个Linux自动登陆ORACLE数据库的.sh文件
这个你查看一下是否有表和视图是否存在,
其次是有对这个表和视图的访问权限。