Insert Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Insert Error

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-03-26 : 19:00:56

I am getting the following error message when running this Query through Query Analyzer ... not sure why!? I tried adding in the PatientName to this report and it blew up on me. Any assistance is greatly appreciated.

Server: Msg 213, Level 16, State 5, Line 28
Insert Error: Column name or number of supplied values does not match table definition.
Server: Msg 213, Level 16, State 1, Line 87
Insert Error: Column name or number of supplied values does not match table definition.

/* Payment Status of Transmitted Claims */

SET NOCOUNT ON

CREATE TABLE #Visits
(
PatientName VARCHAR(200),
TicketNumber varchar(20),
DOS datetime,
Charges money,
TotalCharge money,
FileTransmitted datetime,
FirstFiledDate datetime,
LastFiledDate datetime,
SubmittedToCarrier varchar(100),
ClearingHouse varchar(100),
Payments money,
Adjustments money,
PaidDate datetime,
CheckDate datetime,
PaidByCarrier varchar(100),
PayerName varchar(100),
PaymentType smallint
)

-- First let's get the electronic visits that user selected

INSERT INTO #Visits

SELECT dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS PatientName,
pv.TicketNumber,
pv.Visit,
ISNULL (pva.InsAllocation,0),
ISNULL (pva.InsAllocation,0),
ISNULL (edicf.FileTransmitted,''),
ISNULL (pv.FirstFiledDate,''),
ISNULL (pv.LastFiledDate,''),
ISNULL (ic.ListName,'None'),
ISNULL (cl.ClearingHouseName,'None') ,
0,
0,
'',
'',
'',
'',
0

FROM PatientVisit pv
JOIN PatientProfile pp on pp.PatientProfileID = pv.PatientProfileID
JOIN PatientVisitAgg pva ON pv.PatientVisitID = pva.PatientVisitID
LEFT JOIN EDIClaim edic ON pv.PatientVisitId = edic.PatientVisitId
LEFT JOIN EDIClaimFile edicf ON edic.EDIClaimFileId = edicf.EDIClaimFileId
LEFT JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId
LEFT JOIN Clearinghouse cl ON edicf.ClearinghouseId = cl.ClearinghouseId

WHERE pv.FilingType = 2
AND pv.FirstFiledDate >= ISNULL(NULL,'1/1/1900') AND
pv.FirstFiledDate < dateadd(d, 1,ISNULL(NULL,'1/1/3000'))
AND --Filter on insurance carrier
(
(NULL IS NOT NULL AND ic.InsuranceCarriersId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on insurance group
(
(NULL IS NOT NULL AND ic.InsuranceGroupId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on doctor
(
(NULL IS NOT NULL AND pv.DoctorID 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)
)

-- Now that we have our visit population, let's go get any payments that may have been made on these visits

INSERT INTO #Visits

SELECT dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS PatientName,
pv.TicketNumber,
pv.Visit,
v.Charges,
0,
'',
ISNULL (pv.FirstFiledDate,''),
ISNULL (pv.LastFiledDate,''),
ISNULL (icv.ListName,'None'),
'',
vt.Payments,
vt.Adjustments,
pm.DateofEntry,
pm.CheckDate,
ISNULL(ic.ListName,'None'),
ISNULL(pm.PayerName,''),
pm.PaymentType

FROM PatientVisit pv
JOIN PatientProfile pp on pp.PatientProfileID = pv.PatientProfileID
JOIN #Visits v ON pv.TicketNumber = v.TicketNumber
JOIN VisitTransactions vt ON pv.PatientVisitId = vt.PatientVisitid
JOIN PaymentMethod pm ON vt.PaymentMethodID = pm.PaymentMethodID
LEFT JOIN InsuranceCarriers icv ON pv.PrimaryInsuranceCarriersId = icv.InsuranceCarriersId
LEFT JOIN InsuranceCarriers ic ON vt.InsuranceCarriersID = ic.InsuranceCarriersID
LEFT JOIN EDIClaim edic ON pv.PatientVisitId = edic.PatientVisitId
LEFT JOIN EDIClaimFile edicf ON edic.EDIClaimFileId = edicf.EDIClaimFileId

WHERE pv.FilingType = 2 AND pm.Source = 2
AND pv.FirstFiledDate >= ISNULL(NULL,'1/1/1900') AND
pv.FirstFiledDate < dateadd(d, 1,ISNULL(NULL,'1/1/3000'))
AND --Filter on insurance carrier
(
(NULL IS NOT NULL AND ic.InsuranceCarriersId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on insurance group
(
(NULL IS NOT NULL AND ic.InsuranceGroupId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on doctor
(
(NULL IS NOT NULL AND pv.DoctorID 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)
)

SELECT * FROM #Visits

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2007-03-27 : 06:23:18

Maybe you have to remove the alias in the select statement. What on earth are all these??? ->

AND --Filter on company
(
(NULL IS NOT NULL AND pv.CompanyID IN (NULL) )
OR
(NULL IS NULL)
)

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources