Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

The statement has been terminated - Error 1

Status
Not open for further replies.
jeffsturgeon2002

jeffsturgeon2002

Programmer
Sep 19, 2006
136
US
Can anyone describe why this was received? Ive never encountered this error before.

Code: 
Server: Msg 8152, Level 16, State 9, Line 44
String or binary data would be truncated.
The statement has been terminated.
Server: Msg 8152, Level 16, State 9, Line 132
String or binary data would be truncated.
The statement has been terminated.

Code: 
[COLOR=RED]
Line 44 = INSERT INTO #Summary
Line 132 = INSERT INTO #Summary
[/COLOR]

REPORT QUERY

Code: 
/* Reimbursement Summary  01/31/2005 
Updated for Batch and DOS information 11/01/2005
03/13/06 Treat FQHC Reimbursement Charges as Negative Insurance Adjustments
04/20/06 Added tracking of Deposit Activity */ 
SET NOCOUNT ON

CREATE TABLE #Summary (
	DoctorID int,
	DoctorName varchar(50),
	FacilityID int,
	FacilityName varchar(50),
	CompanyID int,
	CompanyName varchar(50),
	FinancialClassMID int,
	FinancialClass varchar(50),
	DepartmentMID int,
	Department varchar(50),
	InsuranceCarriersID int,
	InsuranceCarrier varchar(50),
	InsAllocation numeric(10,2),
	PatAllocation numeric(10,2),
	PatBalance numeric(10,2),
	InsBalance numeric(10,2),
	InsPayment numeric(10,2),
	PatPayment numeric(10,2),
	InsAdjustment numeric(10,2),
	PatAdjustment numeric(10,2),
	Flag varchar(50),
	CPTCode varchar(10),
	ProceduresID int,
	TicketNumber varchar(35),
        Entry datetime,
	Resource varchar(50),
	ResourceID int,
	PatientVisitID int,
	DateofServiceFrom datetime,
	Modifier varchar(10),
	POS varchar(10)
	)

IF 0 <> 1
BEGIN
-- Insert Charges 
[COLOR=RED]
INSERT INTO #Summary
[/COLOR]
SELECT	pv.DoctorId, 
	d.ListName AS DoctorName,
	pv.FacilityId, 
	f.ListName AS FacilityName,
	pv.CompanyId, 
	c.ListName AS CompanyName,
	ISNULL(pv.FinancialClassMId,0) AS FinancialClassMId, 
	ISNULL(fc.Description,  'Unknown') AS FinancialClass, 
	ISNULL(p.DepartmentMId,0) AS DepartmentMId, 
	ISNULL(dp.Description, 'Unknown') AS Department,
	ISNULL(pv.PrimaryInsuranceCarriersId,0),
	CASE WHEN 'Entry' = 'PolicyType' THEN ISNULL(pt.Description,'No Policy Type') ELSE ISNULL(ic.ListName,'Self Pay') END AS PrimaryInsuranceCarrier, 
	CASE WHEN pvp.ProceduresID IN (58) THEN 0 ELSE pvpa.OrigInsAllocation END, 
	pvpa.OrigPatAllocation,
	pvpa.OrigPatAllocation,
	pvpa.OrigInsAllocation,
	0, 
	0, 
	CASE WHEN pvp.ProceduresID IN (58) THEN - pvpa.OrigInsAllocation ELSE 0 END, 
	0,
	convert(varchar(50), 'None'), 
	ISNULL(pvp.Code,'No Code'),
	ISNULL(pvp.ProceduresID,0),
                pv.TicketNumber,
                pvp.DateofEntry,
	' ',
	0,
	pv.PatientVisitID,
	pvp.DateofServiceFrom,
	mod.Code, pos.Code
FROM   PatientVisit pv INNER JOIN
        DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId INNER JOIN
        DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId INNER JOIN
        DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId INNER JOIN
        PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId INNER JOIN
        Batch b ON pvp.BatchID = b.BatchID INNER JOIN
        PatientVisitProcsAgg pvpa ON pvp.PatientVisitProcsID = pvpa.PatientVisitProcsID LEFT OUTER JOIN
        Procedures p ON pvp.ProceduresID = p.ProceduresID LEFT OUTER JOIN
        MedLists dp ON p.DepartmentMID = dp.MedListsID LEFT OUTER JOIN
        InsuranceCarriers ic ON ic.InsuranceCarriersId = pv.PrimaryInsuranceCarriersId LEFT OUTER JOIN
        MedLists fc ON pv.FinancialClassMID = fc.MedListsID LEFT OUTER JOIN
        MedLists pt ON ic.PolicyTypeMID = pt.MedListsID LEFT OUTER JOIN
        MedLists mod ON pvp.Modifier1MID = mod.MedListsID INNER JOIN
        MedLists pos ON pvp.PlaceofServiceMID = pos.MedListsID
WHERE	pvpa.InsAllocation + pvpa.PatAllocation <> 0.00 AND 
                (
	('2' = '1' AND pvp.DateofServiceFrom >= ISNULL('01/01/2007','1/1/1900') AND pvp.DateofServiceFrom < dateadd(d, 1, ISNULL('01/31/2007','1/1/3000'))) OR
	('2' = '2' AND pvp.DateofEntry >= ISNULL('01/01/2007','1/1/1900') AND pvp.DateofEntry < dateadd(d,1,ISNULL('01/31/2007','1/1/3000')))
	)
	AND 
                (
	(pvp.DateofServiceFrom >= ISNULL(NULL,'1/1/1900') AND pvp.DateofServiceFrom < dateadd(d, 1, ISNULL(NULL,'1/1/3000')))
	)
	AND  --Filter on doctor
	(
	(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on CPTCode
	(
	(NULL IS NOT NULL AND pvp.ProceduresID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on company
	(
	(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on insurance carrier
	(
	(NULL IS NOT NULL AND pv.PrimaryInsuranceCarriersId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on batches
	(
	(NULL IS NOT NULL AND b.BatchId IN (NULL)) OR
	(NULL IS NULL)
	)
END


-- Next Import Payments and Adjustments
[COLOR=RED]
INSERT INTO #Summary
[/COLOR]
SELECT	pv.DoctorId, 
	d.ListName AS DoctorName,
	pv.FacilityId, 
	f.ListName AS FacilityName,
	pv.CompanyId, 
	c.ListName AS CompanyName,
	ISNULL(pv.FinancialClassMId,0) AS FinancialClassMId, 
	ISNULL(fc.Description,  'Unknown') AS FinancialClass, 
	ISNULL(p.DepartmentMId,0) AS DepartmentMId, 
	ISNULL(dp.Description, 'Unknown') AS Department,
	ISNULL(pv.PrimaryInsuranceCarriersId,0),
	CASE WHEN 'Entry' = 'PolicyType' THEN ISNULL(pt.Description,'No Policy Type') ELSE ISNULL(ic.ListName,'Self Pay') END AS PrimaryInsuranceCarrier, 
	0, 
	0,
	CASE WHEN pm.Source = 1 AND t.Action IN ('A','P') THEN -td.Amount ELSE 0 END,
	CASE WHEN pm.Source = 2 AND t.Action IN ('A','P') THEN -td.Amount ELSE 0 END,
	CASE WHEN pm.Source = 2 AND t.Action = ('P') THEN td.Amount ELSE 0 END, 
	CASE WHEN pm.Source = 1 AND t.Action = ('P') THEN td.Amount ELSE 0 END, 
	CASE WHEN pm.Source = 2 AND t.Action = ('A') THEN td.Amount ELSE 0 END, 
	CASE WHEN pm.Source = 1 AND t.Action = ('A') THEN td.Amount ELSE 0 END, 
	convert(varchar(50), 'None'), 
	CASE WHEN td.PatientVisitProcsID IS NULL THEN 'ZDeposit' ELSE ISNULL(pvp.Code,'No Code') END,
	CASE WHEN td.PatientVisitProcsID IS NULL THEN -99 ELSE ISNULL(pvp.ProceduresID,0) END,
                pv.TicketNumber,
                pm.DateofEntry,
	' ',
	0,
	pv.PatientVisitID,
	CASE WHEN td.PatientVisitProcsID IS NULL THEN pm.DateofEntry ELSE pvp.DateofServiceFrom END,
	mod.Code, pos.Code
FROM PatientVisit pv INNER JOIN
     PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId INNER JOIN
     DoctorFacility d ON pv.DoctorId = d .DoctorFacilityId INNER JOIN
     DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId INNER JOIN
     DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId INNER JOIN
     VisitTransactions vt ON pv.PatientVisitId = vt.PatientVisitid INNER JOIN
     PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId INNER JOIN
     Batch b ON pm.BatchID = b.BatchID INNER JOIN
     Transactions t ON vt.VisitTransactionsId = t .VisitTransactionsId INNER JOIN
     TransactionDistributions td ON t .TransactionsId = td.TransactionsId LEFT OUTER JOIN
     PatientVisitProcs pvp ON td.PatientVisitProcsId = pvp.PatientVisitProcsId LEFT OUTER JOIN
     Procedures p ON pvp.ProceduresID = p.ProceduresID LEFT OUTER JOIN
     MedLists dp ON p.DepartmentMID = dp.MedListsID LEFT OUTER JOIN
     MedLists at ON t.ActionTypeMId = at.MedListsId LEFT OUTER JOIN
     InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersID = ic.InsuranceCarriersID LEFT OUTER JOIN
     MedLists fc ON pv.FinancialClassMID = fc.MedListsID LEFT OUTER JOIN
        MedLists pt ON ic.PolicyTypeMID = pt.MedListsID   LEFT OUTER JOIN
        MedLists mod ON pvp.Modifier1MID = mod.MedListsID INNER JOIN
        MedLists pos ON pvp.PlaceofServiceMID = pos.MedListsID
WHERE	td.Amount <> 0.00 AND
	(
	('2' = '1' AND pvp.DateofServiceFrom >= ISNULL('01/01/2007','1/1/1900') AND pvp.DateofServiceFrom < dateadd(d, 1, ISNULL('01/31/2007','1/1/3000'))) OR
	('2' = '2' AND pm.DateofEntry >= ISNULL('01/01/2007','1/1/1900') AND pm.DateofEntry < dateadd(d,1,ISNULL('01/31/2007','1/1/3000')))
	)
	AND 
        	(
	(pvp.DateofServiceFrom IS NULL) OR 
	(pvp.DateofServiceFrom >= ISNULL(NULL,'1/1/1900') AND pvp.DateofServiceFrom < dateadd(d, 1, ISNULL(NULL,'1/1/3000')))
	)
	AND  --Filter on doctor
	(
	(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on CPTCode
	(
	(NULL IS NOT NULL AND pvp.ProceduresID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on company
	(
	(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on insurance carrier
	(
	(NULL IS NOT NULL AND pv.PrimaryInsuranceCarriersId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on batches
	(
	(NULL IS NOT NULL AND b.BatchId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND 
	(
	(0 = 0) OR
	(0 = 1 AND pvp.DateofServiceFrom IS NULL)
	)

IF 2 = 1
BEGIN
-- Now group the items together for a total
SELECT  DoctorId, 
	InsuranceCarriersID,
	SUM(InsAllocation) AS InsAllocation,
	SUM(PatAllocation) AS PatAllocation,
	SUM(PatBalance) AS PatBalance,
	SUM(InsBalance) AS InsBalance,
	SUM(InsPayment) AS InsPayment,
	SUM(PatPayment) AS PatPayment,
	SUM(InsAdjustment) AS InsAdjustment,
	SUM(PatAdjustment) AS PatAdjustment,
	CPTCode,
	DateofServiceFrom,
	Modifier, POS
FROM #Summary
GROUP BY DoctorId, 
	InsuranceCarriersID,
	CPTCode,
	DateofServiceFrom,
	Modifier, POS

END
ELSE
BEGIN
SELECT  DoctorId, 
	InsuranceCarriersID,
	SUM(InsAllocation) AS InsAllocation,
	SUM(PatAllocation) AS PatAllocation,
	SUM(PatBalance) AS PatBalance,
	SUM(InsBalance) AS InsBalance,
	SUM(InsPayment) AS InsPayment,
	SUM(PatPayment) AS PatPayment,
	SUM(InsAdjustment) AS InsAdjustment,
	SUM(PatAdjustment) AS PatAdjustment,
	CPTCode,
	DateofServiceFrom,
	Modifier, POS
FROM #Summary
GROUP BY DoctorId, 
	InsuranceCarriersID,
	CPTCode,
	DateofServiceFrom,
	Modifier, POS
END

DROP TABLE #Summary

Jeff

SELECT * FROM users WHERE clue > 0
 
Sort by date Sort by votes
It looks to me like you are trying to insert data in one of the fields that is longer than the field allows.

String or binary data would be truncated.

Indicates that the value to be inserted is longer than the field can hold. Unfortunately, it treats the entire INSERT INTO statement as one line of code, so it may be difficult to figure out what the culprit value is.



=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer
 
Upvote 0 Downvote
I found the pesky culprit ... your post helped me alot. Many thanks!

Jeff

SELECT * FROM users WHERE clue > 0
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
Create Function Within IF statement. 1
Replies
4
Views
488
mikrom
mikrom
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
398
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
288
Andrzejek
Andrzejek
Virendra Yaduvanshi
  • Locked
  • Question
SSRS - Subscription page error
Replies
0
Views
113
Virendra Yaduvanshi
Virendra Yaduvanshi
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
151
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top