|
오류 1033:TOP을 함께 지정하지 않는 한 뷰, 인라인 함수, 파생 테이블, 하위 쿼리에서 ORDER
BY절을 사용할 수 없습니다. 라는 오류 메세지가 나오네요.
만든 쿼리 구문은 아래와 같구요.
+를 사용하면 이런식으로 오류메세지가 나오네요.
고수님들 부탁드립니다.. 죄송합니다. (__)
DECLARE CURS_GRPPRIORITY CURSOR FOR
select a.t_projectname, a.r, a.rnum - ISNULL(acc_sum, 0) seq, a.r2
from (select t_projectname,r, rownum rnum, r2
from (select t_projectname,req_org_biz r,
(CASE estrestart
WHEN null THEN "eststart"
ELSE "estrestart"
END)
+ "estcomplete" as r2
from pmo_tproject
where TPROJPROGRESS < 100
order by req_org_biz,
(CASE estrestart
WHEN null THEN "eststart"
ELSE "estrestart"
END)
+ "estcomplete"))
a,
(select b.r r, b.rnum, sum(c.cnt) acc_sum
from (select rownum rnum, r, cnt
from (select req_org_biz r, count(*) cnt
from pmo_tproject
where TPROJPROGRESS < 100
group by req_org_biz))) b,
(select rownum rnum, r, cnt
from (select req_org_biz r, count(*) cnt
from pmo_tproject
where TPROJPROGRESS < 100
group by req_org_biz)) c
where b.rnum > c.rnum
group by b.r, b.rnum) d
where a.r = d.r
order by 2, 3
오라클의 프로시져를 SQLSERVER TSQL로 옮기는데 원본은 다음과 같습니다.
CURSOR CURS_GRPPRIORITY IS
select a.t_projectname, a.r, a.rnum - nvl(acc_sum, 0) seq, a.r2
from (select t_projectname,r, rownum rnum, r2
from (select t_projectname,req_org_biz r,
decode(estrestart,null,eststart,estrestart)||estcomplete r2
from pmo_tproject
where TPROJPROGRESS < 100
order by req_org_biz,
decode(estrestart,null,eststart,estrestart)||estcomplete)) a,
(select b.r r, b.rnum, sum(c.cnt) acc_sum
from (select rownum rnum, r, cnt
from (select req_org_biz r, count(*) cnt
from pmo_tproject
where TPROJPROGRESS < 100
group by req_org_biz)) b,
(select rownum rnum, r, cnt
from (select req_org_biz r, count(*) cnt
from pmo_tproject
where TPROJPROGRESS < 100
group by req_org_biz)) c
where b.rnum > c.rnum
group by b.r, b.rnum) d
where a.r = d.r(+)
order by 2, 3;
|
