/* Formatted on 2009/09/22 13:51 (Formatter Plus v4.8.0) */
SELECT rf1.rf_id, wk1.wassign_id, rf1.cust_addr, wa1.NAME, wk1.state,
TO_CHAR (wk1.dispatch_date, 'yyyy-mm-dd HH:mi:ss') dispath,
TO_CHAR (wk1.reply_date, 'yyyy-mm-dd HH:mi:ss') reply,
wk1.CATEGORY cata,
(SELECT MAX (sf1.NAME)
FROM (SELECT *
FROM rf_log
UNION ALL
SELECT *
FROM rf_log_his) log1, staff sf1
WHERE sf1.staff_id = log1.staff_id
AND log1.wassign_id = wk1.wassign_id
AND log1.action_code = 'WA_BROWSE') name2
FROM (SELECT temp1.rf_id, temp1.cust_addr
FROM (SELECT *
FROM rf
UNION ALL
SELECT *
FROM rf_his) temp1) rf1,
(SELECT temp2.rf_id, temp2.wassign_id, temp2.CATEGORY,
temp2.warea_id, temp2.state, temp2.dispatch_date,
temp2.reply_date
FROM (SELECT *
FROM work_assign
UNION ALL
SELECT *
FROM work_assign_his) temp2
WHERE temp2.state != 'X') wk1,
work_area wa1
WHERE wk1.rf_id = rf1.rf_id(+) AND wa1.warea_id = wk1.warea_id(+)
ORDER BY wa1.warea_id
以上是一条SQL语句,运行后总在第一个to_char函数位置提示ORA-00936: 缺少表达式,但去掉最后一个where条件,也就是去掉wk1.rf_id = rf1.rf_id(+) AND 这句,就能正常执行了,请哪位高手看下,高分重谢!
SELECT rf1.rf_id, wk1.wassign_id, rf1.cust_addr, wa1.NAME, wk1.state,
TO_CHAR (wk1.dispatch_date, 'yyyy-mm-dd HH:mi:ss') dispath,
TO_CHAR (wk1.reply_date, 'yyyy-mm-dd HH:mi:ss') reply,
wk1.CATEGORY cata,
(SELECT MAX (sf1.NAME)
FROM (SELECT *
FROM rf_log
UNION ALL
SELECT *
FROM rf_log_his) log1, staff sf1
WHERE sf1.staff_id = log1.staff_id
AND log1.wassign_id = wk1.wassign_id
AND log1.action_code = 'WA_BROWSE') name2
FROM (SELECT temp1.rf_id, temp1.cust_addr
FROM (SELECT *
FROM rf
UNION ALL
SELECT *
FROM rf_his) temp1) rf1,
(SELECT temp2.rf_id, temp2.wassign_id, temp2.CATEGORY,
temp2.warea_id, temp2.state, temp2.dispatch_date,
temp2.reply_date
FROM (SELECT *
FROM work_assign
UNION ALL
SELECT *
FROM work_assign_his) temp2
WHERE temp2.state != 'X') wk1,
work_area wa1
WHERE wk1.rf_id = rf1.rf_id(+) AND wa1.warea_id = wk1.warea_id(+)
ORDER BY wa1.warea_id
以上是一条SQL语句,运行后总在第一个to_char函数位置提示ORA-00936: 缺少表达式,但去掉最后一个where条件,也就是去掉wk1.rf_id = rf1.rf_id(+) AND 这句,就能正常执行了,请哪位高手看下,高分重谢!
是不是where 前多了一个逗号
把左连接用用left join写试试
换成 WHERE wk1.rf_id = rf1.rf_id AND wa1.warea_id = wk1.warea_id
试试看
SELECT rf1.rf_id, wk1.wassign_id, rf1.cust_addr, wa1.NAME, wk1.state,
TO_CHAR (wk1.dis, 'yyyy-mm-dd HH:mi:ss') dispath,
TO_CHAR (wk1.rep, 'yyyy-mm-dd HH:mi:ss') reply, wk1.cat cata,
(SELECT MAX (sf1.NAME)
FROM (SELECT *
FROM rf_log
UNION ALL
SELECT *
FROM rf_log_his) log1, staff sf1
WHERE sf1.staff_id = log1.staff_id
AND log1.wassign_id = wk1.wassign_id
AND log1.action_code = 'WA_BROWSE') name2
FROM (SELECT temp1.rf_id, temp1.cust_addr
FROM (SELECT *
FROM rf
UNION ALL
SELECT *
FROM rf_his) temp1) rf1,
(SELECT temp2.rf_id, temp2.wassign_id, temp2.CATEGORY cat,
temp2.warea_id, temp2.state, temp2.dispatch_date dis,
temp2.reply_date rep
FROM (SELECT *
FROM work_assign
UNION ALL
SELECT *
FROM work_assign_his) temp2
WHERE temp2.state != 'X') wk1,
work_area wa1
WHERE wk1.rf_id = rf1.rf_id(+) AND wa1.warea_id = wk1.warea_id(+)
ORDER BY wa1.warea_id
现在改成以上的行式,在第二个to_char函数位置提示ORA-00923: 未找到预期 FROM 关键字的错误