Solved

SQL Server 2008 - Split Memo Length

Posted on 2013-01-11
2
294 Views
Last Modified: 2013-02-25
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
Comment
Question by:rustypoot
2 Comments
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38768984
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 38769115
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question