我有一个存储过程,它正在构建稍后在查询中调用的表。我不得不添加使用CTE的新表,但现在当我使用;为了结束表达式,我得到了一个错误";"附近的语法不正确当我删除它时,我得到了一个错误,我需要一个;具有CTE。查询本身在存储过程之外工作。任何帮助都将不胜感激。。。。
set @cdq = 'insert #REMOVAL_FLAG
(
MYKEY,
REMOVAL_FLAG
)
WITH
create_key AS(
SELECT *,
(CONVERT(varchar(25), a.NDC11, 101) + CONVERT(varchar(25), a.PharmacyID, 101) + CONVERT(varchar(50),ABS(a.TotalNetCost),101)) as REVERSAL_KEY
FROM table1 a
),
find_rev AS (
SELECT *,
CASE
WHEN MIN(TotalAmount) OVER (PARTITION BY REVERSAL_KEY) < 0 THEN 1 ELSE NULL
END as Flag
FROM create_key
),
primary_flag as (
SELECT TOP 1500000 *,
DENSE_RANK() OVER (ORDER BY REVERSAL_KEY) as [GROUP],
ROW_NUMBER() OVER (PARTITION BY NDC11, PharmacyID, TotalNetCost ORDER BY REVERSAL_KEY) as PK
FROM find_rev
WHERE Flag = 1
ORDER BY [GROUP], PK
),
flagged as (
SELECT *,
CASE
WHEN COUNT (PK) OVER (PARTITION BY [GROUP], PK) > 1 THEN ''REMOVE''
END as REMOVAL_FLAG
FROM primary_flag
)
SELECT a1.MYKEY, f.REMOVAL_FLAG
FROM table1 a1
LEFT OUTER JOIN flagged f ON (f.MYKEY = a1.MYKEY)
'
insert
语句可以使用CTE(with
表达式)。在SQL Server中,它位于insert
之前,而不是select
:之前
WITH
create_key AS(
SELECT *,
(CONVERT(varchar(25), a.NDC11, 101) + CONVERT(varchar(25), a.PharmacyID, 101) + CONVERT(varchar(50),ABS(a.TotalNetCost),101)) as REVERSAL_KEY
FROM table1 a
),
find_rev AS (
SELECT *,
CASE
WHEN MIN(TotalAmount) OVER (PARTITION BY REVERSAL_KEY) < 0 THEN 1 ELSE NULL
END as Flag
FROM create_key
),
primary_flag as (
SELECT TOP 1500000 *,
DENSE_RANK() OVER (ORDER BY REVERSAL_KEY) as [GROUP],
ROW_NUMBER() OVER (PARTITION BY NDC11, PharmacyID, TotalNetCost ORDER BY REVERSAL_KEY) as PK
FROM find_rev
WHERE Flag = 1
ORDER BY [GROUP], PK
),
flagged as (
SELECT *,
CASE
WHEN COUNT (PK) OVER (PARTITION BY [GROUP], PK) > 1 THEN ''REMOVE''
END as REMOVAL_FLAG
FROM primary_flag
)
insert #REMOVAL_FLAG(MYKEY, REMOVAL_FLAG)
SELECT a1.MYKEY, f.REMOVAL_FLAG
FROM table1 a1
LEFT OUTER JOIN flagged f ON (f.MYKEY = a1.MYKEY)