sql 语句报错 ，请高手指点，早

Question

if OBJECT_ID('P_Report_GetDailyCoeeByEqptype') is not null
drop proc P_Report_GetDailyCoeeByEqptype
goCREATE PROCEDURE [dbo].[P_Report_GetDailyCoeeByEqptype]@MfgDate nvarchar(128),
@EqpType nvarchar(128)
AS
BEGIN

SET NOCOUNT ON;
Set @MfgDate = CONVERT(nvarchar(128),Convert(Datetime,@Mfgdate),111)
Declare @Coeetarget nvarchar(128)
Declare @CoeeTargetExcludeIdle nvarchar(128)  Select @CoeeTarget=COEETarget,@CoeeTargetExcludeIdle=CoeeTargetExcludeIdle from UserDataModel.T_ST_DailyCoeeTarget
 where MfgDate = @MfgDate    Select RawData.ResourceID,RawData.ResourceName,RawData.Model,RawData.PCode,Convert(float,RawData.Duration) AS Duration,Pcode.CoeeGroup into #Temp from 
    (
(Select ResourceID,ResourceName,Model,PCode,Duration from Dbo.T_ETL_CoeeDailyRawData where MfgDate = @MfgDate And Model = @EqpType) RawData
inner join
UserDataModel.T_ST_TesterStatePcode Pcode
on RawData.PCode = Pcode.Pcode
    )
    
    
    
--select * from t1
--union all
--select a.resourceid,a.resourcename,a.model,b.Value,0,a.CoeeTargert,a.CoeeTargetExcludeldle,a.coee,a.coeeExcludeldle
--from (select distinct resourceid,resourcename,model,CoeeTargert,CoeeTargetExcludeldle,coee, coeeExcludeldle from t1) a,t2 b
--where not exists(select 1 from t1 where ResourceID=a.ResourceID and coeegroup=b.Value )
--order by resourceid    select * from 
    
    (select aa.ResourceID,aa.ResourceName,aa.Model,aa.CoeeGroup,aa.Duration, aa.CoeeTargert,aa.CoeeTargetExcludeIdle,bb.coee,bb.coeeExcludeIdle from 
    (
    Select ResourceID,ResourceName,Model,CoeeGroup,SUM(Duration) As Duration,@Coeetarget As CoeeTargert,@CoeeTargetExcludeIdle As CoeeTargetExcludeIdle
    From #Temp 
    group by ResourceID,ResourceName,model,CoeeGroup
    ) as aa
    left OUTER join 
    (select * from dbo.T_ETL_PCodeCalCoee) bb 
    on aa.ResourceID=bb.EqpID and bb.Mfgdate=@MfgDate ) txt1
    
    union  all
    select ftf.ResourceID,ftf.ResourceName,ftf.Model,t22.CoeeGroup,ftf.CoeeTargert,ftf.CoeeTargetExcludeIdle,ftf.coee, ftf.coeeExcludeIdle
    from (select distinct ResourceID,ResourceName,Model,CoeeTargert,CoeeTargetExcludeIdle,coee, coeeExcludeIdle from txt1)ftf,
    (select value as CoeeGroup from  dbo.T_LookUpTableValue
where LookUpTableId=(select LookUpTableId 
from  dbo.T_LookUpTable where Name='CoeeGroup'))t22  
where not exists (select 1 from txt1 where ResourceID=ftf.ResourceID and CoeeGroup=t22.CoeeGroup)
END
GO---执行
USE [NavigoFTODS]
GODECLARE @return_value intEXEC @return_value = [dbo].[P_Report_GetDailyCoeeByEqptype]
@MfgDate = N'2011/7/20',
@EqpType = N'D10'
--SELECT 'Return Value' = @return_valueGO-----提示说 没有txt1

Answer 1

---其中 我把  select * from 
    -------从这里开始，当做  一个表
    (select aa.ResourceID,aa.ResourceName,aa.Model,aa.CoeeGroup,aa.Duration, aa.CoeeTargert,aa.CoeeTargetExcludeIdle,bb.coee,bb.coeeExcludeIdle from 
    (
    Select ResourceID,ResourceName,Model,CoeeGroup,SUM(Duration) As Duration,@Coeetarget As CoeeTargert,@CoeeTargetExcludeIdle As CoeeTargetExcludeIdle
    From #Temp 
    group by ResourceID,ResourceName,model,CoeeGroup
    ) as aa
    left OUTER join 
    (select * from dbo.T_ETL_PCodeCalCoee) bb 
    on aa.ResourceID=bb.EqpID and bb.Mfgdate=@MfgDate )     txt1
----------------表在这里结束

Answer 2

(select 1 from txt1 where ResourceID=ftf.ResourceID and CoeeGroup=t22.CoeeGroup
这块错了。
上面指代的，在下面不能这么引用。替代方法，用原语句替代Txt1/或者用CTE WITH txt1
 AS
 ( SELECT    aa.ResourceID ,
                    aa.ResourceName ,
                    aa.Model ,
                    aa.CoeeGroup ,
                    aa.Duration ,
                    aa.CoeeTargert ,
                    aa.CoeeTargetExcludeIdle ,
                    bb.coee ,
                    bb.coeeExcludeIdle
          FROM      ( SELECT    ResourceID ,
                                ResourceName ,
                                Model ,
                                CoeeGroup ,
                                SUM(Duration) AS Duration ,
                                @Coeetarget AS CoeeTargert ,
                                @CoeeTargetExcludeIdle AS CoeeTargetExcludeIdle
                      FROM      #Temp
                      GROUP BY  ResourceID ,
                                ResourceName ,
                                model ,
                                CoeeGroup
                    ) AS aa
                    LEFT OUTER JOIN ( SELECT    *
                                      FROM      dbo.T_ETL_PCodeCalCoee
                                    ) bb ON aa.ResourceID = bb.EqpID
                                            AND bb.Mfgdate = @MfgDate
 
 )

Answer 3

----CTE   是做什么的，还能详解，还有 ，我试过，把txt1  换成原先的 sql  一整句

Answer 4

不上 2个图 ，下巴  value  下面  有的值，在   coeegroup  下面，补上去

Answer 5

可是，我把全部 的  原语句  ，带入到     子句中  ，还是报错  ，这个存储过程中 ，只有   2个txt1