2 u010152179 u010152179 于 2014.12.15 17:57 提问

把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个回答

lk13962517093
lk13962517093   2014.12.16 11:09
 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关键字了呢?
大约 3 年之前 回复
u010152179
u010152179   2014.12.29 09:45

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

lk13962517093
lk13962517093   2014.12.29 10:26

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

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!