日前,一个92版的查询如下
select * from IF_INSTRDATA_T t1 left join IF_INSTRDATA_T t2
on T1.INSTR_ID=T2.INSTR_ID and t2.read_time =
(SELECT MAX (t3.read_time)
FROM if_instrdata_t t3
WHERE t3.instr_id = t1.instr_id
AND t3.read_time < t1.read_time)
where T1.INSTR_ID=45001
就是有一个表读数,要将这一个表读数的净值用一个视图表现出来,在9i中该视图运行正常,但升级到10g运行该查询出现
“ORA-01799: 列不能外部联接到子查询”提示
请教高手在10g中怎么写条件中带有子查询的左外联接查询。
select * from IF_INSTRDATA_T t1 left join IF_INSTRDATA_T t2
on T1.INSTR_ID=T2.INSTR_ID and t2.read_time =
(SELECT MAX (t3.read_time)
FROM if_instrdata_t t3
WHERE t3.instr_id = t1.instr_id
AND t3.read_time < t1.read_time)
where T1.INSTR_ID=45001
就是有一个表读数,要将这一个表读数的净值用一个视图表现出来,在9i中该视图运行正常,但升级到10g运行该查询出现
“ORA-01799: 列不能外部联接到子查询”提示
请教高手在10g中怎么写条件中带有子查询的左外联接查询。
select *
from IF_INSTRDATA_T t1
left join IF_INSTRDATA_T t2 on T1.INSTR_ID = T2.INSTR_ID
and t2.read_time =
(SELECT MAX(t3.read_time)
FROM if_instrdata_t t3,IF_INSTRDATA_T t4
WHERE t3.instr_id = t4.instr_id
AND t3.read_time < t4.read_time)
where T1.INSTR_ID = 45001
提示
“ORA-01799: 列不能外部联接到子查询”
select *
from IF_INSTRDATA_T t1
left join (select *
from IF_INSTRDATA_T t2
where t2.read_time =
(SELECT MAX(t3.read_time)
FROM if_instrdata_t t3, IF_INSTRDATA_T t4
WHERE t3.instr_id = t4.instr_id
AND t3.read_time < t4.read_time)) t on T1.INSTR_ID = t.INSTR_ID
where T1.INSTR_ID = 45001
from IF_INSTRDATA_T t1
left join (select *
from IF_INSTRDATA_T t2
where t2.read_time =
(SELECT MAX(t3.read_time)
FROM if_instrdata_t t3, IF_INSTRDATA_T t4
WHERE t3.instr_id = t4.instr_id
AND t3.read_time < t4.read_time)) t on T1.INSTR_ID = t.INSTR_ID
where T1.INSTR_ID = 45001查询结果不正确,其实这个查询是要这样的结果有表如下
A B C
1 2008-10-1 23
1 2008-11-1 45
1 2008-12-3 100
要求查询结果如下:
A B C B1 C1 净值
1 2008-10-1 23 23
1 2008-11-1 45 2008-10-1 23 22
1 2008-12-3 100 2008-11-1 45 55这在9i中很容易实现,但在10g中就实现不了
要求是在10g中正确运行的Sql语句
1 2008-10-1 23
1 2008-11-1 45
1 2008-12-3 100
要求查询结果如下:
A B C B1 C1 净值
1 2008-10-1 23 23
1 2008-11-1 45 2008-10-1 23 22
1 2008-12-3 100 2008-11-1 45 55
FROM (SELECT T1.*,LAG(B) OVER(ORDER BY ROWNUM) B1,LAG(C) OVER(ORDER BY ROWNUM) C1
FROM IF_INSTRDATA_T T1)
1 2008-10-1 23
1 2008-11-1 45
1 2008-12-3 100
2 2008-10-1 33
2 2008-11-1 40
2 2008-12-3 60
要求查询结果如下:
A B C B1 C1 净值
1 2008-10-1 23 23
1 2008-11-1 45 2008-10-1 23 22
1 2008-12-3 100 2008-11-1 45 55
2 2008-10-1 33 33
2 2008-11-1 40 2008-10-1 33 7
2 2008-12-3 60 2008-11-1 40 20
FROM (SELECT T1.*,
LAG(B) OVER(PARTITION BY ID ORDER BY ID) B1,
LAG(C) OVER(PARTITION BY ID ORDER BY ID) C1
FROM IF_INSTRDATA_T T1)