Insert Error: Column name or number of supplied va

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 2005 Forums

Transact-SQL (2005)

Insert Error: Column name or number of supplied va

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2009-02-17 : 18:07:59

I cant see where I went off... any help is appreciated. Please note, the SQL is handled dynamically by the SQL server, so some of whats in where clause will look odd. Please disregard this ...

Insert Error: Column name or number of supplied values does not match table definition.

[CODE]
/* OSHPD Principal Diagnoses
Revised 03/17/2006 Detail sent to report*/

SET NOCOUNT ON
DECLARE @qmed INT
DECLARE @qden INT
DECLARE @qbh INT
DECLARE @qsa INT
DECLARE @qen INT
DECLARE @qop INT

SELECT
@qmed = medlistsid
FROM
medlists
WHERE
tablename = 'ProcedureCodeQualifier'
AND Description = 'HCPC - MED'
SELECT
@qden = medlistsid
FROM
medlists
WHERE
tablename = 'ProcedureCodeQualifier'
AND Description = 'HCPC - DEN'
SELECT
@qbh = medlistsid
FROM
medlists
WHERE
tablename = 'ProcedureCodeQualifier'
AND Description = 'HCPC - BH'
SELECT
@qsa = medlistsid
FROM
medlists
WHERE
tablename = 'ProcedureCodeQualifier'
AND Description = 'HCPC - SA'
SELECT
@qen = medlistsid
FROM
medlists
WHERE
tablename = 'ProcedureCodeQualifier'
AND Description = 'HCPC - EN'
SELECT
@qop = medlistsid
FROM
medlists
WHERE
tablename = 'ProcedureCodeQualifier'
AND Description = 'HCPC - OP'

DECLARE @db VARCHAR(255)
SELECT
@db = DB_NAME()
CREATE TABLE #OSHPDTMP
(
PatientProfileID INT ,
PatientID VARCHAR(255) ,
[Last] VARCHAR(255) ,
Suffix VARCHAR(255) ,
[First] VARCHAR(255) ,
Middle VARCHAR(255) ,
Birthdate DATETIME ,
Sex VARCHAR(1) ,
ListNamea VARCHAR(255) ,
Homeless VARCHAR(255) ,
ListName VARCHAR(255) ,
ListNameb VARCHAR(255) ,
FacilityID INT ,
TicketNumber VARCHAR(255) ,
Visit DATETIME ,
PatientVisitID INT ,
DoctorID INT ,
Voided INT ,
CompanyID INT ,
ProceduresID INT ,
CPTProcedureCodeQualifierMId INT ,
DepartmentMId INT ,
PrimaryInsuranceCarriersID INT ,
DOS DATETIME
)
INSERT INTO
#OSHPDTMP
EXEC master..chcxp_UDS @db , 2 , '01/01/2008' , '12/31/2008'

CREATE TABLE #Encounters
(
PatientProfileID INT ,
PatientID VARCHAR(15) ,
PatientName VARCHAR(100) ,
Birthdate DATETIME ,
Sex VARCHAR(1) ,
DoctorName VARCHAR(75) ,
Resource VARCHAR(75) ,
Homeless BIT ,
Flag VARCHAR(50) ,
FacilityName VARCHAR(75) ,
FacilityID INT ,
TicketNumber VARCHAR(15) ,
PatientVisitID INT ,
DoctorID INT ,
ResourceID INT ,
ICD9Code VARCHAR(10) ,
Description VARCHAR(255) ,
DateofServiceTo DATETIME ,
Encounters INT ,
ICD9Group VARCHAR(100) ,
ICD9Codes VARCHAR(15)
)

INSERT INTO
#Encounters
SELECT
PatientProfileID ,
PatientID ,
RTRIM(RTRIM(ISNULL([Last] , '') + ' ' + ISNULL(Suffix , '')) + ', ' + ISNULL([First] , '') + ' ' + ISNULL(Middle , '')) AS PatientName ,
Birthdate ,
Sex ,
ListNameA AS DoctorName ,
' ' AS Resource ,
ISNULL(Homeless , 0) ,
'(none)' ,
ISNULL(ListName , 'None') ,
ISNULL(FacilityID , 0) ,
TicketNumber ,
PatientVisitID ,
DoctorID ,
0 ,
'' AS ICD9Code ,
'' AS Description ,
Visit ,
1 ,
'' ,
'None'
FROM
#OSHPDTMP
WHERE
Voided IS NULL
AND
--Filter on facility
(
(
'68,67' IS NOT NULL
AND FacilityID IN ( 68 , 67 )
)
OR ( '68,67' IS NULL )
)
AND
--Filter on Company
(
(
'1' IS NOT NULL
AND CompanyID IN ( 1 )
)
OR ( '1' IS NULL )
)
AND
--Filter on CPTCode
(
(
NULL IS NOT NULL
AND ProceduresID IN ( NULL )
)
OR ( NULL IS NULL )
)
AND -- Identify Dental from Medical from BH
(
( 1 = 1 )
OR (
1 = 2
AND CPTProcedureCodeQualifierMId IN ( @qmed , @qden , @qbh , @qsa , @qen , @qop )
)
OR (
1 = 3
AND CPTProcedureCodeQualifierMId = @qmed
)
OR (
1 = 4
AND CPTProcedureCodeQualifierMId = @qden
)
OR (
1 = 5
AND CPTProcedureCodeQualifierMId = @qbh
)
OR (
1 = 6
AND CPTProcedureCodeQualifierMId = @qsa
)
OR (
1 = 7
AND CPTProcedureCodeQualifierMId = @qen
)
OR (
1 = 8
AND CPTProcedureCodeQualifierMId = @qop
)
OR ( 1 IS NULL )
)
--Filter on Department
AND --Filter on Department
(
(
NULL IS NOT NULL
AND DepartmentMId IN ( NULL )
)
OR ( NULL IS NULL )
)
GROUP BY
PatientProfileID ,
Last ,
First ,
Suffix ,
Middle ,
Birthdate ,
Homeless ,
ListNameA ,
TicketNumber ,
Visit ,
FacilityID ,
ListName ,
FacilityID ,
PatientID ,
Sex ,
PatientVisitID ,
DoctorID

DECLARE
@lICD9Code VARCHAR(10) ,
@lPatientVisitID INT ,
@lDescription VARCHAR(255)

DECLARE c CURSOR STATIC FORWARD_ONLY LOCAL
FOR SELECT
PatientVisitID
FROM
#Encounters
OPEN c

FETCH NEXT FROM c INTO @lPatientVisitID

WHILE ( @@fetch_status = 0 )
BEGIN

SELECT TOP 1
@lICD9Code = pvd.ICD9Code ,
@lDescription = pvd.Description
FROM
PatientVisitDiags pvd
WHERE
pvd.PatientVisitID = @lPatientVisitID
ORDER BY
pvd.ListOrder
UPDATE
#Encounters
SET
ICD9Code = @lICD9Code ,
Description = @lDescription
WHERE
PatientVisitID = @lPatientVisitID
FETCH NEXT FROM c INTO @lPatientVisitID
END
CLOSE c
DEALLOCATE c

UPDATE
#Encounters
SET
ICD9Group = '01. Infectious and Parasitic Diseases' ,
ICD9Codes = '001-139'
WHERE
SUBSTRING(ICD9Code , 1 , 3) BETWEEN '001' AND '139'

UPDATE
#Encounters
SET
ICD9Group = '02. Neoplasms' ,
ICD9Codes = '140-239'
WHERE
SUBSTRING(ICD9Code , 1 , 3) BETWEEN '140' AND '239'

UPDATE
#Encounters
SET
ICD9Group = '03. Endocrine,Nutritional,and Metabolic Diseases and Immunity Disorders' ,
ICD9Codes = '240-279'
WHERE
SUBSTRING(ICD9Code , 1 , 3) BETWEEN '240' AND '279'

UPDATE
#Encounters
SET
ICD9Group = '04. Blood and Blood Forming Disorders' ,
ICD9Codes = '280-289'
WHERE
SUBSTRING(ICD9Code , 1 , 3) BETWEEN '280' AND '289'

UPDATE
#Encounters
SET
ICD9Group = '05. Mental Disorders' ,
ICD9Codes = '290-319'
WHERE
SUBSTRING(ICD9Code , 1 , 3) BETWEEN '290' AND '319'

UPDATE
#Encounters
SET
ICD9Group = '06. Nervous System and Sense Organs Diseases' ,
ICD9Codes = '320-389'
WHERE
SUBSTRING(ICD9Code , 1 , 3) BETWEEN '320' AND '389'

UPDATE
#Encounters
SET
ICD9Group = '07. Circulatory System Diseases' ,
ICD9Codes = '390-459'
WHERE
SUBSTRING(ICD9Code , 1 , 3) BETWEEN '390' AND '459'

UPDATE
#Encounters
SET
ICD9Group = '08. Respiratory System Diseases' ,
ICD9Codes = '460-519'
WHERE
SUBSTRING(ICD9Code , 1 , 3) BETWEEN '460' AND '519'

UPDATE
#Encounters
SET
ICD9Group = '09. Digestive System Diseases' ,
ICD9Codes = '520-579'
WHERE
SUBSTRING(ICD9Code , 1 , 3) BETWEEN '520' AND '579'

UPDATE
#Encounters
SET
ICD9Group = '10. Genitourinary System Diseases' ,
ICD9Codes = '580-629'
WHERE
SUBSTRING(ICD9Code , 1 , 3) BETWEEN '580' AND '629'

UPDATE
#Encounters
SET
ICD9Group = '11. Pregnancy, Childbirth & the Puerperium' ,
ICD9Codes = '630-677'
WHERE
SUBSTRING(ICD9Code , 1 , 3) BETWEEN '630' AND '677'

UPDATE
#Encounters
SET
ICD9Group = '12. Skin and Subcutaneous Tissue Diseases' ,
ICD9Codes = '680-709'
WHERE
SUBSTRING(ICD9Code , 1 , 3) BETWEEN '680' AND '709'

UPDATE
#Encounters
SET
ICD9Group = '13. Musculoskeletal System and Connective Tissue Diseases' ,
ICD9Codes = '710-739'
WHERE
SUBSTRING(ICD9Code , 1 , 3) BETWEEN '710' AND '739'

UPDATE
#Encounters
SET
ICD9Group = '14. Congenital Anomalies' ,
ICD9Codes = '740-759'
WHERE
SUBSTRING(ICD9Code , 1 , 3) BETWEEN '740' AND '759'

UPDATE
#Encounters
SET
ICD9Group = '15. Certain Conditions Originating in the Perinatal Period' ,
ICD9Codes = '760-779'
WHERE
SUBSTRING(ICD9Code , 1 , 3) BETWEEN '760' AND '779'

UPDATE
#Encounters
SET
ICD9Group = '16. Symptoms, Signs and Ill-defined Conditions' ,
ICD9Codes = '780-799'
WHERE
SUBSTRING(ICD9Code , 1 , 3) BETWEEN '780' AND '799'

UPDATE
#Encounters
SET
ICD9Group = '17. Injury and Poisoning' ,
ICD9Codes = '800-999'
WHERE
SUBSTRING(ICD9Code , 1 , 3) BETWEEN '800' AND '999'

UPDATE
#Encounters
SET
ICD9Group = '18. Factors Influencing Health Status and Contact with Health Services' ,
ICD9Codes = 'V01-V82'
WHERE
SUBSTRING(ICD9Code , 1 , 3) BETWEEN 'V01' AND 'V82'

UPDATE
#Encounters
SET
ICD9Group = '19. Dental Diagnoses' ,
ICD9Codes = '520-529'
WHERE
SUBSTRING(ICD9Code , 1 , 3) BETWEEN '520' AND '529'

UPDATE
#Encounters
SET
ICD9Group = '20. Family Planning S Codes' ,
ICD9Codes = ''
WHERE
SUBSTRING(ICD9Code , 1 , 1) = 'S'

UPDATE
#Encounters
SET
ICD9Group = '21. Other' ,
ICD9Codes = ''
WHERE
ICD9Codes = 'None'

SELECT
*
FROM
#Encounters

[/CODE]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-17 : 18:22:06

Replace first half of code with

SELECT    @qmed = MAX(case when Description = 'HCPC - MED' then medlistsid else null end),
          @qden = MAX(case when Description = 'HCPC - DEN' then medlistsid else null end),
          @qbh  = MAX(case when Description = 'HCPC - BH'  then medlistsid else null end),
          @qsa  = MAX(case when Description = 'HCPC - SA'  then medlistsid else null end),
          @qen  = MAX(case when Description = 'HCPC - EN'  then medlistsid else null end),
          @qop  = MAX(case when Description = 'HCPC - OP'  then medlistsid else null end)
FROM      medlists
WHERE     tablename = 'ProcedureCodeQualifier'
	  AND Description IN ('HCPC - OP', 'HCPC - EN', 'HCPC - SA', 'HCPC - BH', 'HCPC - DEN', 'HCPC - MED')

E 12°55'05.63"
N 56°04'39.26"