把oracle start with 转为 sql server with语句,创建视图不成功

oracle start with语句
create or replace view VV_Select as
select "CORPCODE","CORPDESC","USERCODE","USERDESC"
from
(
select bc.corpcode,bc.corpdesc, aa.usercode,aa.userdesc from base_corp bc,base_user aa where bc.corpcode in (select distinct(corpcode)
from base_corp t
start with t.corpcode in
(select c.corpcode
from RIGHT_RIGHTUSER A, BASE_CORP c
WHERE A.DATACODE = c.CORPCODE
AND A.CLASSFICATIONID = '1'
AND A.USERCODE = aa.usercode)
CONNECT BY PRIOR t.corpid = t.parentid)
) a
group by a.corpcode, a.corpdesc, a.usercode, a.userdesc;

SQL SERVER with 语句
create view VV_Select as
select CORPCODE,CORPDESC,USERCODE,USERDESC
from
(
SELECT BC.CORPCODE, BC.CORPDESC, AA.USERCODE, AA.USERDESC
FROM MLS_BASE_CORP BC, MLS_BASE_USER AA
WHERE BC.CORPCODE IN
(WITH STEPCTE(CORPCODE) AS
(SELECT DISTINCT (CORPCODE)
FROM MLS_BASE_CORP T
WHERE T.CORPCODE IN
(SELECT C.CORPCODE
FROM MLS_RIGHT_RIGHTUSER A,
MLS_BASE_CORP C,
MLS_BASE_USER UU
WHERE A.DATACODE = C.CORPCODE
AND A.CLASSFICATIONID = '1'
AND A.USERCODE = UU.USERCODE)
UNION ALL
SELECT A.CORPCODE
FROM MLS_BASE_CORP A
INNER JOIN STEPCTE CTE
ON CTE.CORPCODE = A.CORPCODE)
SELECT CORPCODE FROM STEPCTE
)
) a group by a.corpcode, a.corpdesc, a.usercode, a.userdesc;
创建的时候报错
[Err] 42000 - [SQL Server]关键字 'WITH' 附近有语法错误。
42000 - [SQL Server]关键字 'with' 附近有语法错误。如果此语句是公用表表达式、xmlnamespaces 子句或者更改跟踪上下文子句,那么前一个语句必须以分号结尾。
42000 - [SQL Server]')' 附近有语法错误。

3个回答

 create view VV_Select as
select CORPCODE,CORPDESC,USERCODE,USERDESC
from
(
SELECT BC.CORPCODE, BC.CORPDESC, AA.USERCODE, AA.USERDESC
FROM MLS_BASE_CORP BC, MLS_BASE_USER AA
WHERE BC.CORPCODE IN (SELECT DISTINCT CORPCODE
                        FROM MLS_BASE_CORP T
                            WHERE T.CORPCODE IN (SELECT C.CORPCODE 
                                                    FROM MLS_RIGHT_RIGHTUSER A,
                                                    MLS_BASE_CORP C,
                                                    MLS_BASE_USER UU
                                                    WHERE A.DATACODE = C.CORPCODE
                                                    AND A.CLASSFICATIONID = '1'
                                                    AND A.USERCODE = UU.USERCODE)
                        UNION ALL
                    SELECT A.CORPCODE
                        FROM MLS_BASE_CORP A
                            INNER JOIN STEPCTE CTE ON CTE.CORPCODE = A.CORPCODE)
) a group by a.corpcode, a.corpdesc, a.usercode, a.userdesc;
u010152179
u010152179 我对with as 怎么使用不熟,为什么你改的没有with关键字了呢?
大约 5 年之前 回复

我对with as 怎么使用不熟,为什么你改的没有with关键字了呢?

首先需要清楚oracle语句中你的start with 的具体作用
你的t.corpcode需要存在与下面的子查询中,是吧?
假如是这样,mssql中使用in就可以了。

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问