Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server 2008 - Split Memo Length

Posted on 2013-01-11
2
Medium Priority
?
303 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 27

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

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.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
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.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

636 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