Getting error trying to create temp table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Getting error trying to create temp table

Author

Topic

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2006-07-19 : 18:16:20

I keep getting error message below when I try to declare a temp table:

Server: Msg 8152, Level 16, State 9, Line 20
String or binary data would be truncated.
Warning: Null value is eliminated by an aggregate or other SET operation.
The statement has been terminated.

I've included the script below:

DECLARE @NLCREFIAVG TABLE (

CASENUMBER VARCHAR(10),
LASTNAME VARCHAR(30),
UAC VARCHAR(10),
DAYS INT,
LONG INT,
TOPROCESSING VARCHAR(10),
PRECLOSING VARCHAR(10),
SOC VARCHAR(10),
PURPOSE VARCHAR(20),
LOANPLAN VARCHAR(30),
RE008COND VARCHAR(5),
RE008SEQ VARCHAR(5),
RE008STATUS VARCHAR(5),
RE008DTCOMPL VARCHAR(10),
BUILDER VARCHAR(30),
PROPSTATUS VARCHAR(20),
PARCODE VARCHAR(60))

INSERT INTO @NLCREFIAVG

SELECT DISTINCT
L.CASENO,
L.BORROWERLASTNAME,
L.UAC,
CASE
WHEN RE008.STATUS NOT IN ('wav','clr')
THEN DATEDIFF(D,L.DATESENTTOPROCESSING,L.DATESENTTOPRECLOSING)
WHEN RE008.STATUS = 'clr'
THEN DATEDIFF(D,DATESENTTOPROCESSING,RE008.DATECOMPLETED)
WHEN RT.ROUTED IS NULL
THEN DATEDIFF(D,L.DATESENTTOPROCESSING,L.DATESENTTOPRECLOSING)
ELSE DATEDIFF (D,RT.ROUTED,L.DATESENTTOPRECLOSING)
END AS
DAYS,
CASE
WHEN (RE008.STATUS NOT IN ('wav','clr')
AND DATEDIFF(D,L.DATESENTTOPROCESSING,L.DATESENTTOPRECLOSING) > 30)
THEN 1
WHEN (RE008.STATUS IN ('clr')
AND DATEDIFF(D,L.DATESENTTOPROCESSING,RE008.DATECOMPLETED) > 30)
THEN 1
WHEN (RT.ROUTED IS NULL
AND DATEDIFF(D,L.DATESENTTOPROCESSING,L.DATESENTTOPRECLOSING) > 30)
THEN 1
WHEN (RT.ROUTED IS NOT NULL
AND DATEDIFF(D,RT.ROUTED,L.DATESENTTOCLOSING) > 30)
THEN 1
ELSE 0
END AS
LONG,
L.DATESENTTOPROCESSING,
CASE
WHEN RE008.STATUS = 'clr' THEN RE008.DATECOMPLETED
ELSE L.DATESENTTOPRECLOSING
END AS
[PRECLOSING],
L.SOC,
L.LOANPURPOSE,
L.LOANPLAN,
RE008.TRACKINGCONDITION,
RE008.TRACKINGSEQUENCE,
RE008.STATUS,
RE008.DATECOMPLETED,
L.BUILDERNAME,
L2.PROPERTYSTATUS,
L.PARCODE
FROM
TEMPRL.DBO.TEMP1 L (NOLOCK)
LEFT JOIN
TEMPRL.DBO.TEMP2 L2 (NOLOCK)
ON
L.CASENO = L2.CASENO
LEFT JOIN
(SELECT CASENO, TRACKINGCONDITION, TRACKINGSEQUENCE, STATUS, DATECOMPLETED
FROM TEMPRL.DBO.TEMP3 RE008 (NOLOCK)
WHERE TRACKINGCONDITION = 'RE'
AND TRACKINGSEQUENCE = '008') RE008
ON L.CASENO = RE008.CASENO
LEFT JOIN
(SELECT DISTINCT CASENO, SOC, MAX(ROUTEDON) AS ROUTED
FROM TEMPRL.dbo.TEMP4
WHERE SUBSTAGE IN ('03','04')
GROUP BY CASENO, SOC) RT
ON L.CASENO = RT.CASENO

WHERE
L.UAC NOT LIKE 'r%' AND L.UAC NOT LIKE 'c%' AND L.UAC NOT LIKE 'p%'
AND L.UAC NOT LIKE 't%' AND L.UAC NOT LIKE 'w%'
AND L.SOC BETWEEN 5 AND 8
AND L.LOANPURPOSE IN ('r','c')
AND L.PARCODE NOT LIKE '3%'
AND L.BUILDERNAME = ''
AND L2.PROPERTYSTATUS <> 'c'
AND CASE
WHEN RE008.STATUS = 'clr' THEN RE008.DATECOMPLETED

ELSE L.DATESENTTOPRECLOSING

END BETWEEN CONVERT(SMALLDATETIME,GETDATE()-10,101)
AND CONVERT(SMALLDATETIME,GETDATE()-4,101)
GROUP BY
L.CASENO,
L.BORROWERLASTNAME,
L.UAC,
L.DATESENTTOPROCESSING,
L.DATESENTTOPRECLOSING,
L.DATESENTTOCLOSING,
L.SOC,
L.LOANPURPOSE,
L.LOANPLAN,
RE008.TRACKINGCONDITION,
RE008.TRACKINGSEQUENCE,
RE008.STATUS,
RE008.DATECOMPLETED,
L.BUILDERNAME,
L2.PROPERTYSTATUS,
L.PARCODE,
RT.ROUTED

--ORDER BY L.CASENO ASC

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-19 : 19:17:27

I don't think your issue is in the DECLARE statement - that all looks in order.
Your error is referring to the fact that you're trying to insert data into that table variable that is too long.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-19 : 20:16:39

Yes timmy is correct.
Select the Declare statement only and execute it in Query Analyzer.
In the remaining code, one or more Selecting data length exceed(s) the Corresponding Field width of the Temp Table. For Sure, not the int fields

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources