• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

SQL Server 2008 - Split Memo Length

Hi,

Below is the SQL. The field LongDocument returns data more than 65K. The Crystal Reports is used to display the data from this stored proc. Crystal cannot handle data field more than 64K! So, the field LongDocument is getting truncated in the Crystal. SAP suggested to split the field based on the length in SQL itself. How can I do this? I need the solution ASAP as this is affecting Production. Thanks




ALTER PROCEDURE [dbo].[hsp_DischargeDocumentReport_R_PR]  
 @IDCode varchar(50),  
 @VisitIdCode varchar(50)  
AS  

 
SET NOCOUNT ON  
 
DECLARE @ClientGUID HVCIDdt, @ChartGUID HVCIDdt  
 
SELECT @ClientGUID = ClientGUID, @ChartGUID = ChartGUID  
FROM [dbo].[CV3ClientVisit] WITH(NOLOCK)  
WHERE REPLACE(IDCode, '-', '') = @IDCode and REPLACE(VisitIdCode, '-', '') = @VisitIDCode  
   
 
CREATE TABLE #TEMP_DOCUMENTS(  
    [Name] varchar(60),  
    [PatCareDocGUID] numeric(16,0),  
    [cmaxDocCode] varchar(5),  
 [cmaxdocumenttype] varchar(60),  
 [cmaxbarcode] varchar(10)  
)  
 
 
INSERT INTO #TEMP_DOCUMENTS ([Name],[PatCareDocGUID], [cmaxDocCode], [cmaxdocumenttype], [cmaxbarcode])  
SELECT [Name], [GUID], [cmaxdoccode], [cmaxdocumenttype], [cmaxbarcode]  
FROM [dbo].[cv3patientcaredocument] pcd WITH(NOLOCK)  
 INNER JOIN [dbo].[zhoagdischargepacket] zdd on pcd.[name] = zdd.[documentname]  
WHERE zdd.DocumentTypes = 'Discharge Document'  
 
CREATE INDEX [IX_PatCareDocGUID] ON #TEMP_DOCUMENTS([PatCareDocGUID])  
 
DECLARE @Pointer varbinary(16), @Counter int, @DocLine varchar(max),  
  @StartingPoint int, @LastLoop int, @ClientDocDetailGUID numeric(16,0),  
  @DocTextType int, @StartChar int  
   
SET @Counter = 1  
SET @StartingPoint = 0  
 
CREATE TABLE #tmpLongDocument(  
 [ClientGUID] numeric (16,0) NULL,  
 [ChartGUID] numeric(16,0) NULL,  
 [ClientVisitGUID] numeric(16,0) NULL,  
 [ClientDisplayName] varchar(50) NULL,  
 [CurrentLocation] varchar(50) NULL,  
 [ProviderDisplayName] varchar(50) NULL,  
 [AdmitDtm] datetime NULL,  
 [DischargeDtm] datetime NULL,  
 [InternalVisitStatus] char(3) NULL,  
 [IDCode] char(20) NULL,  
 [VisitIDCode] char(20) NULL,  
    [GenderCode] char(1) NULL,  
    [BirthDayNum] int NULL,  
    [BirthMonthNum] int NULL,  
    [BirthYearNum] int NULL,  
 [DocumentName] varchar(60) NULL,  
 [EntryType] int NULL,  
 [CLientDocumentGUID] numeric (16,0) NULL,  
 [PatientCareDocGUID] numeric(16,0) NULL,  
 [AuthoredDtm] datetime NULL,  
 [DocTouchedWhen] datetime NULL,  
 [DocCreatedWhen] datetime NULL,  
 [HasBeenModified] bit NULL,  
 [IsCanceled] bit null,  
 [HistoryType] int NULL,  
 [HistoryDtm] datetime NULL,  
 [ClientDocDetailGUID] numeric (16,0) NULL,  
 [cddTouchedWhen] datetime NULL,  
 [AuthoredBy] varchar(50) NULL,  
 [AuthoredByOcc] varchar(30) NULL,  
 [EnteredBy] varchar(50) NULL,  
 [EnteredByOcc] varchar(30) ,  
 [HasImageType] tinyint,  
 [DocTextType] int,  
 [LongDocument] varchar(max) NULL,  
 [Pointer] varbinary(16) NULL,
 ArcType INT  )  
 
INSERT INTO #tmpLongDocument  
SELECT  CV3ClientVisit.ClientGUID,  
        CV3ClientVisit.ChartGUID,  
  CV3CLientVisit.GUID as ClientVisitGUID,  
  CV3ClientVisit.ClientDisplayName,  
  CV3ClientVisit.CurrentLocation,  
  CV3ClientVisit.ProviderDisplayName,  
  CV3ClientVisit.AdmitDtm,  
  CV3ClientVisit.DischargeDtm,  
  CV3ClientVisit.InternalVisitStatus,  
 CV3ClientVisit.IDCode,  
 CV3ClientVisit.VisitIDCode,  
    Left(CV3Client.GenderCode,1),  
    CV3Client.BirthDayNum,  
    CV3Client.BirthMonthNum,  
    CV3Client.BirthYearNum,  
 CV3ClientDocument.DocumentName,  
 CV3ClientDocument.EntryType,  
 CV3ClientDocument.GUID as CLientDocumentGUID,  
 CV3ClientDocument.PatCareDocGUID,  
 CV3ClientDocument.AuthoredDtm,  
 CV3ClientDocument.TouchedWhen as DocTouchedWhen,  
 CV3ClientDocument.CreatedWhen as DocCreatedWhen,  
 CV3ClientDocument.HasBeenModified,  
 CV3ClientDocument.IsCanceled,  
 NULL as HistoryType,  
 CV3ClientDocument.TouchedWhen as HistoryDtm,  
 CV3CLientDocDetail.GUID as ClientDocDetailGUID,  
 CV3ClientDocDetail.TouchedWhen as cddTouchedWhen,  
 CV3User.DisplayName as AuthoredBy,  
 CV3User.OccupationCode as AuthoredByOcc,  
 CV3EnteredBy.DisplayName as EnteredBy,  
 CV3EnteredBy.OccupationCode as EnteredByOcc,  
 isnull(CV3ClientDocument.HasImageType,0),  
 CV3ClientDocDetail.DocTextType,  
      LongDocument = ' ',  
 NULL ,-- as Pointer  ,
 CV3ClientVisit.ArcType  
FROM [dbo].[CV3ClientVisit] WITH(nolock)  
 INNER JOIN [dbo].[CV3Client] WITH(nolock) ON CV3ClientVisit.ClientGUID = CV3Client.GUID  
 INNER JOIN [dbo].[CV3ClientDocument] WITH(nolock) ON CV3ClientVisit.ClientGUID = CV3ClientDocument.ClientGUID  
  AND CV3ClientVisit.ChartGUID = CV3ClientDocument.ChartGUID  
  AND CV3ClientDocument.Active = 1  
  AND CV3ClientDocument.EntryType != 1 --Exclude Free Text  
  AND CV3ClientVisit.ArcType=CV3ClientDocument.ArcType
 INNER JOIN [dbo].[CV3User] WITH(nolock) ON CV3ClientDocument.AuthoredProviderGUID = CV3User.GUID  
 INNER JOIN [dbo].[CV3User] CV3EnteredBy WITH(nolock) ON CV3ClientDocument.UserGUID = CV3EnteredBy.GUID  
 INNER JOIN [dbo].[CV3ClientDocDetail] WITH(nolock) ON CV3ClientDocument.GUID = CV3ClientDocDetail.ClientDocumentGUID  
  AND CV3ClientDocDetail.Active = 1  
  AND CV3ClientDocument.ArcType=CV3ClientDocDetail.ArcType  
WHERE  
 [CV3ClientVisit].[ChartGUID] = @ChartGUID and [CV3ClientVisit].[ClientGUID] = @ClientGUID  
 and EXISTS(SELECT * FROM #TEMP_DOCUMENTS a WHERE  cv3clientdocument.PatCareDocGUID = a.PatCareDocGUID)  
 

INSERT INTO #tmpLongDocument  
Select  CV3ClientVisit.ClientGUID,  
 CV3ClientVisit.ChartGUID,  
 CV3ClientVisit.GUID,  
 CV3ClientVisit.ClientDisplayName,  
 CV3ClientVisit.CurrentLocation,  
 CV3ClientVisit.ProviderDisplayName,  
 CV3ClientVisit.AdmitDtm,  
 CV3ClientVisit.DischargeDtm,  
 CV3ClientVisit.InternalVisitStatus,  
 CV3ClientVisit.IDCode,  
 CV3ClientVisit.VisitIDCode,  
    Left(CV3Client.GenderCode,1),  
    CV3Client.BirthDayNum,  
    CV3Client.BirthMonthNum,  
    CV3Client.BirthYearNum,  
 CV3ClientDocument.DocumentName,  
 CV3ClientDocument.EntryType,  
 CV3ClientDocument.GUID as CLientDocumentGUID,  
 CV3ClientDocument.PatCareDocGUID,  
 CV3ClientDocument.AuthoredDtm,  
 CV3ClientDocument.TOuchedWhen as DocTouchedWhen,  
 CV3ClientDocument.CreatedWhen as DocCreatedWhen,  
 CV3ClientDocument.HasBeenModified,  
 CV3ClientDocument.IsCanceled,  
 CV3ClientDocHistory.HistoryType as HistoryType,  
 CV3ClientDocHistory.HistoryDtm as HistoryDtm,  
 CV3CLientDocDetail.GUID as ClientDocDetailGUID,  
 CV3ClientDocDetail.TouchedWhen as cddTouchedWhen,  
 CV3User.DisplayName as AuthoredBy,  
 CV3User.OccupationCode as AuthoredByOcc,  
 CV3EnteredBy.DisplayName as EnteredBy,  
 CV3EnteredBy.OccupationCode as EnteredByOcc,  
 isnull(CV3ClientDocument.HasImageType,0),  
 CV3ClientDocDetail.DocTextType,  
      LongDocument = ' ',  
 NULL ,-- as Pointer  ,
 CV3ClientVisit.ArcType
FROM [dbo].[CV3ClientVisit] WITH(nolock)  
 INNER JOIN [dbo].[CV3Client] WITH(nolock) ON CV3ClientVisit.ClientGUID = CV3Client.GUID  
 INNER JOIN [dbo].[CV3ClientDocument] WITH(nolock) ON CV3ClientVisit.ClientGUID = CV3ClientDocument.ClientGUID  
  AND CV3ClientVisit.ChartGUID = CV3ClientDocument.ChartGUID  
  AND CV3ClientDocument.Active = 1  
  AND CV3ClientDocument.EntryType = 1 -- Free Text only  
   AND CV3ClientVisit.ArcType=CV3ClientDocument.ArcType
 INNER JOIN [dbo].[CV3ClientDocHistory] WITH(nolock) ON CV3ClientDocument.ClientGUID = CV3ClientDocHistory.ClientGUID  
  AND CV3ClientDocument.GUID = CV3ClientDocHistory.ClientDocGUID  
  AND CV3ClientDocument.ArcType=CV3ClientDocHistory.ArcType
 INNER JOIN [dbo].[CV3ClientDocDetailHistXRef] WITH(nolock) ON CV3ClientDocHistory.ClientGUID = CV3ClientDocDetailHistXRef.ClientGUID  
  AND CV3ClientDocHistory.GUID = CV3ClientDocDetailHistXRef.ClientDocHistoryGUID  
  AND CV3ClientDocHistory.ArcType=CV3ClientDocDetailHistXRef.ArcType
 INNER JOIN [dbo].[CV3ClientDocDetail] WITH(nolock) ON CV3ClientDocDetailHistXRef.ClientGUID = CV3ClientDocDetail.ClientGUID  
  AND CV3ClientDocDetailHistXRef.ClientDocDetailGUID = CV3ClientDocDetail.GUID  
  AND CV3ClientDocDetailHistXRef.ArcType=CV3ClientDocDetail.ArcType
 INNER JOIN [dbo].[CV3User] WITH(nolock) ON CV3ClientDocument.AuthoredProviderGUID = CV3User.GUID  
 INNER JOIN [dbo].[CV3User] CV3EnteredBy WITH(nolock) ON CV3ClientDocHistory.UserGUID = CV3EnteredBy.GUID  
WHERE CV3ClientVisit.ClientGUID = @ClientGUID  
 and CV3ClientVisit.ChartGUID = @ChartGUID  
 
 
/***************************************************************************  
 This is the start of the logic that selects the binary data and converts it  
 to text.  The converted document is inserted into the TEXT field named  
 LongDocument.  You can the print LongDocument as a single memo field  
****************************************************************************/  
 
  UPDATE #tmpLongDocument  
SET LongDocument =   CAST(txt.detailtext as varchar(max))
FROM #tmpLongDocument  as tmp
inner join SXACDClientDocDetailText as txt
on tmp.ClientDocDetailGUID = txt.ClientDocDetailGUID
WHERE tmp.HasImageType = 0  

Select  
 #tmpLongDocument.ClientDisplayName,  
 #tmpLongDocument.CurrentLocation,  
 #tmpLongDocument.AdmitDtm,  
 #tmpLongDocument.DischargeDtm,  
 #tmpLongDocument.InternalVisitStatus,  
 #tmpLongDocument.IDCode,  
 #tmpLongDocument.VisitIDCode,  
 #tmpLongDocument.ProviderDisplayName,  
 #tmpLongDocument.ClientGUID,  
 #tmpLongDocument.ClientVisitGUID,  
 #tmpLongDocument.ChartGUID,  
 50 as Codenum,  
 NULL as CodeName,  
 NULL as TypeCode,  
 NULL as ClientInfoText,  
 NULL as OnsetMonNum,  
 NULL as OnsetDayNum,  
 NULL as OnsetYearNum,  
 NULL as ClientEventCreated,  
 NULL as ClientTouchedWhen,  
 NULL as ClientInfoDescription,  
 NULL as ClientInfoAddlInfo,  
 #tmpLongDocument.DocumentName,  
 #tmpLongDocument.EntryType,  
 #tmpLongDocument.ClientDocumentGUID,  
 #tmpLongDocument.PatientCareDocGUID,  
 #tmpLongDocument.AuthoredDtm,  
 #tmpLongDocument.DocTouchedWhen,  
 #tmpLongDocument.CLientDocDetailGUID,  
 #tmpLongDocument.cddTouchedWhen,  
 #tmpLongDocument.IsCanceled,  
 #tmpLongDocument.HistoryDtm,  
 #tmpLongDocument.HistoryType,  
 Case  
  When  (#tmpLongDocument.EntryType = 1  
        or #tmpLongDocument.EntryType = 3) Then  
   #tmpLongDocument.AuthoredBy + ' (' + #tmpLongDocument.AuthoredByOcc + ')'  
  Else NULL  
 END as FreeTextAuthor,  
 Case  
  When  (#tmpLongDocument.EntryType = 1  
        or #tmpLongDocument.EntryType = 3) Then  
   #tmpLongDocument.EnteredBy + ' (' + #tmpLongDocument.EnteredByOcc + ')'  
  Else NULL  
 END as DocEnteredBy,  
 LongDocument =  
  Case  
  When #tmpLongDocument.HasImageType = 0 then  
   #tmpLongDocument.LongDocument  
  else 'Document contains image and cannot be printed in this report.'  
  End,  
 NULL,  
 NULL,  
 NULL,  
 NULL,  
 NULL,  
 NULL,  
 NULL,  
 NULL,  
 #tmpLongDocument.HasBeenModified,  
 CV3ClientDocHistory.HistoryReason as CancelReason,  
 CV3ClientDocHistory.HistoryDtm as CancelDtm,  
 CV3User.DisplayName + ' (' + CV3User.OccupationCode + ')'  
  as CancelBy,  
 #tmpLongDocument.HasImageType,  
    #tmpLongDocument.GenderCode,  
    #tmpLongDocument.BirthDayNum,  
    #tmpLongDocument.BirthMonthNum,  
    #tmpLongDocument.BirthYearNum,  
    cmaxdoccode as DocCode,  
    CV3PatientCareDocument.Description,  
 replace(#tmpLongDocument.IDCode,'-','') as IDCodeClean,  
 replace(#tmpLongDocument.VisitIDCode,'-','') as VisitIDCodeClean,  
 cmaxdocumenttype,  
 cmaxbarcode  
FROM #tmpLongDocument  
 LEFT OUTER JOIN [dbo].[CV3ClientDocHistory] WITH (nolock) ON
  #tmpLongDocument.ClientGUID = CV3ClientDocHistory.ClientGUID  
  and #tmpLongDocument.ClientDocumentGUID = CV3ClientDocHistory.ClientDocGUID  
  and CV3ClientDocHistory.HistoryType = 3  
  AND #tmpLongDocument.ArcType=CV3ClientDocHistory.ArcType
 LEFT OUTER JOIN [dbo].[CV3User] WITH(nolock) on CV3ClientDocHistory.UserGUID = CV3User.GUID  
    LEFT OUTER JOIN [dbo].[CV3AncillaryName] WITH(nolock) ON #tmpLongDocument.PatientCareDocGUID = CV3AncillaryName.MainCatItemGUID  
  AND CV3AncillaryName.UpperCodingStd = 'HIS BARCODE'  
 INNER JOIN #TEMP_DOCUMENTS zHoagTable ON #tmpLongDocument.PatientCareDocGUID = zHoagTable.PatCareDocGUID  
    LEFT OUTER JOIN CV3PatientCareDocument WITH(nolock) ON  #tmpLongDocument.PatientCareDocGUID = CV3PatientCareDocument.GUID  
 
drop table #temp_DOCUMENTS  
drop table #tmpLongDocument
0
rustypoot
Asked:
rustypoot
1 Solution
 
Chris LuttrellSenior Database ArchitectCommented:
Split it how? 2+ columns (requires knowing max # needed) or 2+rows (then do you repeate the other values?)
Split on what? just length, then what if it cuts a word in half, can Crystal Reports put it back together?
0
 
Scott PletcherSenior DBACommented:
CREATE TABLE #tmpLongDocument(  
 [ClientGUID] numeric (16,0) NULL,  
 ...
 [LongDocument] varchar(max) NULL,  
 [LongDocument2] varchar(max) NULL,  --<<-- add this column to table
 [Pointer] varbinary(16) NULL,
 ArcType INT  )  

...

UPDATE #tmpLongDocument  
SET LongDocument =   LEFT(CAST(txt.detailtext as varchar(max)), 64000),
    LongDocument = SUBSTRING(CAST(txt.detailtext as varchar(max)), 64001, 64000)
FROM #tmpLongDocument  as tmp
inner join SXACDClientDocDetailText as txt
on tmp.ClientDocDetailGUID = txt.ClientDocDetailGUID
WHERE tmp.HasImageType = 0  



Then in the SELECT that returns the result, instead of just "LongDocument = ", you would also add a "LongDocument2 = ", setting each to the respective column from the temp table.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now