Solved

SQL Server 2008 - Split Memo Length

Posted on 2013-01-11
2
295 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

737 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