raj.prabhu001
Starting Member
16 Posts |
Posted - 2011-08-30 : 01:58:39
|
my query is giving value for single docno i.e 22 but it fails when run for all recordsand giving error message as below:Msg 8152, Level 16, State 13, Line 19String or binary data would be truncated.The statement has been terminated.my query:create table #final ( DocNo nvarchar(20), grouper nvarchar(20), objtype nvarchar(20), DocDate nvarchar(20), InvNo nvarchar(20), InvDate nvarchar(20), SuppCode nvarchar(20), SuppName nvarchar(20), State nvarchar(20), Description nvarchar(20) --EligiblePurchase decimal(10,4), -- Import decimal(10,4), --CST0AgstFormC decimal(10,4), --TINNO nvarchar(20) ) INSERT INTO #finalSELECT T0.[DocNum] DocNo, 'group' as grouper, T0.objtype as 'objtype', T0.[DocDate] DocDate, T0.[NumAtCard] InvNo, T0.[TaxDate] InvDate, T2.[CardCode] SuppCode, T2.[CardName] SuppName, T9.Name 'State', Isnull(T8.Dscription,'') Description FROM opch T0 INNER JOIN pch1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode LEFT JOIN CRD1 T3 ON T2.CardCode = T3.CardCode AND T0.[PayToCode] = T3.[Address] and T3.AdresType ='S' LEFT JOIN OCST T9 ON T3.State = T9.Code and T3.Country = T9.Country and T3.AdresType='S' INNER JOIN OITM T4 ON T1.ItemCode = T4.ItemCode LEFT JOIN OCHP T8 ON T4.ChapterID = T8.AbsEntry INNER JOIN OSTC T5 ON T1.TaxCode = T5.Code INNER JOIN NNM1 T6 ON T0.Series = T6.Series LEFT JOIN pch12 T7 ON T0.DocEntry = T7.DocEntry WHERE Right(T6.SeriesName,2) = 00 AND T0.[U_PType]='Interstate'--((@Type <>'Local' or T0.[U_PType] = @Type )and (@Type = 'Local' or T0.[U_PType] in (@Type,'Import'))) AND T0.[DocDate] Between '2011-04-01' and '2011-04-30' AND (T7.[TransCat] is null or T7.[TransCat]='') --and t0.docnum = 22 -- select * from @final-- -------------------------------------------------------------------------------------------------------------- SELECT DISTINCT s1.docno, objtype, DocDate , InvNo, InvDate, SuppCode, SuppName, State, STUFF((SELECT DISTINCT TOP 100 PERCENT ' , ' + s2.Description FROM #final AS s2 WHERE s2.docno = s1.docno ORDER BY ' , ' + s2.Description FOR XML PATH('')), 1, 1, '') AS DescriptionFROM #final AS s1ORDER BY s1.docno SELECT DISTINCT s1.docno, objtype, DocDate , InvNo, InvDate, SuppCode, SuppName, State, STUFF((SELECT DISTINCT TOP 100 PERCENT ' , ' + s2.Description FROM #final AS s2 WHERE s2.docno = s1.docno ORDER BY ' , ' + s2.Description FOR XML PATH('')), 1, 1, '') AS DescriptionFROM #final AS s1ORDER BY s1.docnodrop table #final |
|