Solved

SQL Server 2008 - Split Memo Length

Posted on 2013-01-11
2
289 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:
ScottPletcher 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now