代码之家 › 专栏 › 技术社区 › ASH

似乎无法将CTE插入表

tsql sql-server-2008 sql-server

ASH · 技术社区 · 7 年前

我的cte运行查找,并给出我期望的数字,但我似乎无法将结果插入表中。在发布之前,我在网上做了一些研究,根据我所看到的情况,设置似乎是正确的,但我肯定错过了一些步骤,因为这个脚本不起作用。有人能看到我看不到的东西吗?我使用的是SQL Server 2008。

with cte as 
(
select 
       *, rn = row_number() over (partition by Credit_Line_NO order by REVIEW_FREQUENCY)
from TBL_FBNK_LIMIT_HIST
) 

(select CREDIT_LINE_NO
    ,LIMIT_CURRENCY
    ,(CAST(AVAIL_AMT AS DECIMAL(30,15)) * (CAST(SUBSTRING(CUSIP_NO,1,CHARINDEX('%',CUSIP_NO)-1) AS DECIMAL(30,15))/100))/(12/CAST(LEFT(SUBSTRING(REVIEW_FREQUENCY, CHARINDEX('M',review_frequency)+1,LEN(REVIEW_FREQUENCY)),2) AS DECIMAL)) AS AMOUNT
    ,REVIEW_FREQUENCY   
    ,CAST(LEFT(REVIEW_FREQUENCY, 8) AS DATE) AS STARTDATE
    ,CAST(EXPIRY_DATE AS DATE)  AS EXPIRY_DATE
    ,CAST(round((DATEDIFF(MONTH,cast(LEFT(REVIEW_FREQUENCY,8) as DATE),CAST(EXPIRY_DATE AS DATE)))/cast(LEFT(SUBSTRING  (REVIEW_FREQUENCY, CHARINDEX('M',review_frequency)+1,LEN(REVIEW_FREQUENCY)),2) as decimal)+0.4,0) AS INTEGER) AS FREQUENCY  
    ,CAST(DATEADD(MONTH, (rn-1)* LEFT((SUBSTRING(REVIEW_FREQUENCY, CHARINDEX('M',review_frequency)+1,LEN(REVIEW_FREQUENCY))),2),LEFT(REVIEW_FREQUENCY, 8)) AS DATE) AS EFFECTIVESTARTDATE
FROM cte
WHERE AVAIL_AMT NOT LIKE '%]%'
    AND CUSIP_NO IS NOT NULL  
    AND CUSIP_NO <> '0' 
    AND AVAIL_AMT <> '0'
    AND AVAIL_AMT IS NOT NULL)
INSERT TBL_FBNK_LIMIT_HIST_TRANS_SPLIT (CREDIT_LINE_NO,LIMIT_CURRENCY,AMOUNT,REVIEW_FREQUENCY,START_DATE,EXPIRY_DATE,FREQUENCY,AsOfDate,EFFECTIVESTARTDATE)
Select CREDIT_LINE_NO,LIMIT_CURRENCY,AMOUNT,REVIEW_FREQUENCY,START_DATE,EXPIRY_DATE,FREQUENCY,AsOfDate,EFFECTIVESTARTDATE
From cte

谢谢

3 回复 | 直到 7 年前

Cetin Basoz 7 年前

您在插入中并没有真正使用可爱。像这样尝试:

with cte as 
(
select 
       *, rn = row_number() over (partition by Credit_Line_NO order by REVIEW_FREQUENCY)
from TBL_FBNK_LIMIT_HIST
), 
cte2 as 
(select CREDIT_LINE_NO
    ,LIMIT_CURRENCY
    ,(CAST(AVAIL_AMT AS DECIMAL(30,15)) * (CAST(SUBSTRING(CUSIP_NO,1,CHARINDEX('%',CUSIP_NO)-1) AS DECIMAL(30,15))/100))/(12/CAST(LEFT(SUBSTRING(REVIEW_FREQUENCY, CHARINDEX('M',review_frequency)+1,LEN(REVIEW_FREQUENCY)),2) AS DECIMAL)) AS AMOUNT
    ,REVIEW_FREQUENCY   
    ,CAST(LEFT(REVIEW_FREQUENCY, 8) AS DATE) AS STARTDATE
    ,CAST(EXPIRY_DATE AS DATE)  AS EXPIRY_DATE
    ,CAST(round((DATEDIFF(MONTH,cast(LEFT(REVIEW_FREQUENCY,8) as DATE),CAST(EXPIRY_DATE AS DATE)))/cast(LEFT(SUBSTRING  (REVIEW_FREQUENCY, CHARINDEX('M',review_frequency)+1,LEN(REVIEW_FREQUENCY)),2) as decimal)+0.4,0) AS INTEGER) AS FREQUENCY  
    ,CAST(DATEADD(MONTH, (rn-1)* LEFT((SUBSTRING(REVIEW_FREQUENCY, CHARINDEX('M',review_frequency)+1,LEN(REVIEW_FREQUENCY))),2),LEFT(REVIEW_FREQUENCY, 8)) AS DATE) AS EFFECTIVESTARTDATE
FROM cte
WHERE AVAIL_AMT NOT LIKE '%]%'
    AND CUSIP_NO IS NOT NULL  
    AND CUSIP_NO <> '0' 
    AND AVAIL_AMT <> '0'
    AND AVAIL_AMT IS NOT NULL)
INSERT TBL_FBNK_LIMIT_HIST_TRANS_SPLIT (CREDIT_LINE_NO,LIMIT_CURRENCY,AMOUNT,REVIEW_FREQUENCY,START_DATE,EXPIRY_DATE,FREQUENCY,AsOfDate,EFFECTIVESTARTDATE)
Select CREDIT_LINE_NO,LIMIT_CURRENCY,AMOUNT,REVIEW_FREQUENCY,START_DATE,EXPIRY_DATE,FREQUENCY,AsOfDate,EFFECTIVESTARTDATE
From cte2;

Pittsburgh DBA 7 年前

然后,您有一个完全独立的语句,它尝试再次读取插入的CTE。这是不允许的,因为第二个查询的上下文中不存在CTE。因此,从INSERT语句的角度来看,CTE不存在。我相信您会收到以下信息:

消息208,级别16,状态1,[x]行对象名称“cte”无效。

去掉SELECT语句并用INSERT替换它。或者,如果必须在SELECT和INSERT语句中使用SELECT语句,则CTE可能不适合该用例,或者需要为SELECT和INSERT都包含CTE定义。

-1

paparazzo 7 年前

除非这些列不在TBL\U FBNK\U LIMIT\U HIST\U TRANS\U SPLIT中,或者数据类型不匹配,否则它应该可以正常工作。

中间的选择不是插入的一部分。

INSERT TBL_FBNK_LIMIT_HIST_TRANS_SPLIT 
       (CREDIT_LINE_NO, LIMIT_CURRENCY, AMOUNT, REVIEW_FREQUENCY, START_DATE, EXPIRY_DATE, FREQUENCY, AsOfDate, EFFECTIVESTARTDATE)
Select  CREDIT_LINE_NO, LIMIT_CURRENCY, AMOUNT, REVIEW_FREQUENCY, START_DATE, EXPIRY_DATE, FREQUENCY, AsOfDate, EFFECTIVESTARTDATE
From cte