March 22, 2014 at 7:36 am
Hi,
I get the following error , when I execute my CTE on SQL Server 2008 R2. Please help
CTE1 has more columns than were specified in the column list
--SQL CODE IS AS BELOW
WITH CTE1 (AUCTIONID,[Open Time], [Response SLA Broken], [Response SLA Deadline], [Response SLA Actual], [Responsetime SLA Broken Before Transfer], [Resolution SLA Broken], [Resolution SLADeadline], [Resolutiontime SLA Broken
Before Transfer], [Opentime Day], [Opentime Week], [Opentime Month], [Transfer Time], [Transfertime Day], [Transfertime Week], [Transfertime Month], [Time Difference In Minutes], [Time Difference In Hours])
AS
(
Select
a.NUMBER as SDNUMBER,
a.DATESTAMP as OPEN_TIME,
INTC.[RUB_RSP_SLA_BROKEN],
INTC.DB_SEVERITY_DISPLAY as 'Urgency',
INTC.RUB_RSP_TIME,
INTC.RUB_RSP_ACTUAL ,
RESPONSETIME_SLA_BROKEN_BEFORE_TRANSFER =
CASE when INTC.RUB_RSP_TIME < C.DATESTAMP AND INTC.[RUB_RSP_SLA_BROKEN] = 't' THEN 'Yes' ELSE 'No' END,
INTC.[RUB_SLA_BROKEN],
C.TransferedFrom_LaunchDESK as [SM Service Desk From],
C.TransferedFrom_AssignmentGroup as [SM SD Area From],
C.From_LaunchDESKArea as [SM LaunchDESKArea From],
C.TransferedTo_LaunchDESK as [SM Service Desk To],
C.TransferedTo_AssignmentGroup as [SM SD Area To],
C.To_LaunchDESKArea as [SM LaunchDESKArea To],
INTC.CATEGORY,
INTC.SUBCATEGORY,
INTC.PRODUCT_TYPE,
INTC.PROBLEM_TYPE,
RUB_SLA_DEADLINE,
RESOLUTIONTIME_SLA_BROKEN_BEFORE_TRANSFER =
CASE when INTC.RUB_SLA_DEADLINE < C.DATESTAMP AND INTC.RUB_SLA_BROKEN = 't' THEN 'Yes' ELSE 'No' END,
DATENAME(dw,a.DATESTAMP) as OPENTIME_DAY,
DATEPART(WEEK,a.DATESTAMP) as OPENTIME_WEEK,
DATENAME(MONTH,a.DATESTAMP) as OPENTIME_MONTH,
C.DATESTAMP AS TRANSFER_TIME,
DATENAME(dw,C.DATESTAMP) AS TRANSFERTIME_DAY,
DATEPART(WEEK,C.DATESTAMP) AS TRANSFERTIME_WEEK,
DATENAME(MONTH,C.DATESTAMP) AS TRANSFERTIME_MONTH,
DATEDIFF(MINUTE,a.DATESTAMP,C.DATESTAMP) TIMEDIFFERENCEMINUTES,
CAST(DATEDIFF( ss, a.DATESTAMP, C.DATESTAMP ) / 60.0 / 60.0 as decimal(5,2)) TIMEDIFFERENCEHOURS
--DATEDIFF(HOUR,a.DATESTAMP,C.DATESTAMP) TIMEDIFFERENCEHOURS
from
(
Select
ROW_NUMBER() over(partition by [NUMBER] order by DATESTAMP) ROWNUMBER,
NUMBER,DATESTAMP
from dbo.TABLE1 WITH(NOLOCK)
WHERE [TYPE] IN ('Open')
AND DATESTAMP >= DATEADD(wk, DATEDIFF(wk,0,GETDATE())-1, 0)
AND DATESTAMP <= DATEADD(ss,-1,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0))
) A
LEFT JOIN
(
Select * from
(
SELECT
ROW_NUMBER() over(partition by [NUMBER] order by DATESTAMP) ROWNUMBER,
NUMBER,
TYPE,
DATESTAMP,
Transfered_From,
TransferedFrom_LaunchDESK,
TransferedFrom_AssignmentGroup,
From_LaunchDESKArea,
Transfered_To,
TransferedTo_LaunchDESK,
TransferedTo_AssignmentGroup,
To_LaunchDESKArea
FROM dbo.AUCTION_Activity WITH(NOLOCK)
WHERE
[TYPE] IN ('Open','Transferred to another Service Desk')
AND DATESTAMP >= DATEADD(wk, DATEDIFF(wk,0,GETDATE())-1, 0)
AND DATESTAMP <= DATEADD(ss,-1,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0))
)
AS B WHERE ROWNUMBER IN(1,2)
) C ON A.NUMBER = C.NUMBER
LEFT JOIN dbo.AUCTIONs INTC WITH(NOLOCK)
ON A.NUMBER = INTC.INCIDENT_ID
WHERE C.TransferedFrom_LaunchDESK IS NOT NULL
AND (C.TransferedFrom_LaunchDESK = 'SHD' OR C.TransferedFrom_LaunchDESK = 'SH-FI')
AND (C.TransferedTo_LaunchDESK = 'THD' OR C.TransferedTo_LaunchDESK = 'TH-EN')
--ORDER BY A.DATESTAMP
)
SELECT * FROM CTE1
March 22, 2014 at 8:15 am
There are more columns in the SELECT list (a lot more) than are listed in the CTE's definition. They have to be the same. Either add the rest of the columns to the CTE's definition or remove the extra columns from the select
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 24, 2014 at 8:37 am
And while you are at it you might want to drop those NOLOCK hints. Are you familiar with that hint? Do you realize it is more than some magic go fast button? You can and will get missing and or duplicate data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply