T-SQL Help Needed - Cast/Convert Varchar and Int .

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)

T-SQL Help Needed - Cast/Convert Varchar and Int .

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-05-03 : 19:18:47

I think I have a small issue with my Syntax somewhere ....

This is the specific section of the query I need help on (this is how it was prior to me changing it):

ResourceID = ISNULL((SELECT TOP 1 DoctorFacilityID FROM DoctorFacility r
INNER JOIN PatientVisitResource pvr ON pvr.ResourceID = r.DoctorFacilityID
WHERE pvr.PatientVisitID = #Patients.PatientVisitID),#Patients.DoctorID)

Instead of the client needing the DoctorFacilityID (int), she wants to pull the DotID (varchar).

So I went in and did this:

UPDATE #Patients SET ResourceID = ISNULL((SELECT TOP 1 r.DotID as ResourceID FROM DoctorFacility r
INNER JOIN PatientVisitResource pvr ON pvr.ResourceID = r.DoctorFacilityID
WHERE pvr.PatientVisitID = #Patients.PatientVisitID),#Patients.DoctorID)

and now Im getting this:

Server: Msg 245, Level 16, State 1, Line 288
Syntax error converting the varchar value 'OB718' to a column of data type int.

Ive tried every combo of CAST and CONVERT and seem to be missing something!! Can anyone help me change this from DoctorFacilityID to DotID?

The Whole Query:

/* UDS Schedule 5 - Rev. 12/05/2004 - Add Homeless Filter
12/15/2004 Add Void Visit Filter
12/16/2004 Resource Types
04/07/05 by Insurance Carrier
01/31/2006 Added Duplicate Encounter Filter
03/01/2006 Change behavior based on use of qualifiers
*/

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 #UDSTMP(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),HomelessStatusID varchar(255), AgWrkrStatusID 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 #UDSTMP
exec master..chcxp_UDS @db,2,'04/01/2007','04/30/2007'

update u
set u.homeless=h.homeless,u.homelessstatusid=h.homelessstatusid
from #udstmp u
join (select patientprofileid,min(visit) as visit from #udstmp where homeless=1 group by patientprofileid) t on t.patientprofileid=u.patientprofileid
join #udstmp h on t.patientprofileid=h.patientprofileid and t.visit=h.visit

update u
set u.homeless=h.homeless,u.homelessstatusid=h.homelessstatusid
from #udstmp u
join (
select uu.patientprofileid,min(uu.visit) as visit from #udstmp uu
left join (select patientprofileid,min(visit) as visit from #udstmp where homeless=1 group by patientprofileid) tt on tt.patientprofileid=uu.patientprofileid
where tt.patientprofileid is null
group by uu.patientprofileid
) t on t.patientprofileid=u.patientprofileid
join #udstmp h on t.patientprofileid=h.patientprofileid and t.visit=h.visit

CREATE TABLE #Patients (
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 varchar(20),
TicketNumber varchar(15),
Visit datetime,
PatientVisitID int,
DoctorID int,
ResourceID int,
ResourceType varchar(75),
DuplicateVisit int,
Qualifier varchar(255),
DOS datetime
)

DECLARE @NewVisitID int, @OldFacID varchar(20), @NewFacID varchar(20), @OldVisitDate datetime, @NewVisitDate datetime, @OldPtID int,@NewPtID int, @NewQual varchar(255),@OldQual varchar(255),@OldDrID int,@NewDrID int

IF (ISNULL(1,0)>1)
BEGIN
INSERT INTO #Patients
SELECT PatientProfileID,
PatientID,
RTRIM(RTRIM(ISNULL(t.[Last], '') + ' ' + ISNULL(t.Suffix, '')) + ', ' + ISNULL(t.[First], '') + ' ' + ISNULL(t.Middle, '')) AS PatientName,
Birthdate,
Sex,
ISNULL(ic.DotID,'0') AS DoctorName,
' ' AS Resource,
ISNULL(Homeless,0) ,
'(none)' ,
ISNULL(t.ListName,'None'),
ISNULL(df.DotID,'0'),
TicketNumber,
Visit,
PatientVisitID,
DoctorID,
0,
'',
0,
isnull(ml.Description,'Unknown'),
DOS
FROM #UDSTMP t
left join medlists ml on t.CPTProcedureCodeQualifierMId =ml.medlistsid
join DoctorFacility df on t.FacilityID = df.DoctorFacilityID
left join InsuranceCarriers ic ON t.PrimaryInsuranceCarriersID = ic.InsuranceCarriersID
WHERE Voided IS NULL AND
--Filter on facility
(
(NULL IS NOT NULL AND t.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND
--Filter on Company
(
(NULL IS NOT NULL AND CompanyID IN (NULL)) OR
(NULL IS NULL)
)
AND
--Filter on Insurance Carrier
(
(NULL IS NOT NULL AND PrimaryInsuranceCarriersID IN (NULL)) OR
(NULL 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)
)
AND --Filter on Homeless
(
('1' = '1') OR
('1' = '2' AND Homeless = 1) OR
('1' = '3' AND isnull(AgWrkrStatusID,0)<>0 AND (AgWrkrStatusID NOT IN (NULL) OR NULL IS NULL))
)
GROUP BY PatientProfileID,t.Last,t.First,t.Suffix,t.Middle, Birthdate, Homeless,ListNameB,
TicketNumber,Visit,df.DotID,t.ListName,PatientID,Sex,PatientVisitID,DoctorID,CPTProcedureCodeQualifierMId,ml.Description, DOS, ic.DotID

END
ELSE
BEGIN
INSERT INTO #Patients
SELECT PatientProfileID,
PatientID,
RTRIM(RTRIM(ISNULL(t.[Last], '') + ' ' + ISNULL(t.Suffix, '')) + ', ' + ISNULL(t.[First], '') + ' ' + ISNULL(t.Middle, '')) AS PatientName,
Birthdate,
Sex,
ISNULL(ic.DotID,'0') AS DoctorName,
' ' AS Resource,
ISNULL(Homeless,0) ,
'(none)' ,
ISNULL(t.ListName,'None'),
ISNULL(df.DotID,0),
TicketNumber,
Visit,
PatientVisitID,
DoctorID,
0,
'',
0,
'N/A',
NULL
FROM #UDSTMP t
join DoctorFacility df on t.FacilityID = df.DoctorFacilityID
left join InsuranceCarriers ic ON t.PrimaryInsuranceCarriersID = ic.InsuranceCarriersID
WHERE Voided IS NULL AND
--Filter on facility
(
(NULL IS NOT NULL AND t.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND
--Filter on Company
(
(NULL IS NOT NULL AND CompanyID IN (NULL)) OR
(NULL IS NULL)
)
AND
--Filter on Insurance Carrier
(
(NULL IS NOT NULL AND PrimaryInsuranceCarriersID IN (NULL)) OR
(NULL 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)
)
AND --Filter on Homeless
(
('1' = '1') OR
('1' = '2' AND Homeless = 1) OR
('1' = '3' AND isnull(AgWrkrStatusID,0)<>0 AND (AgWrkrStatusID NOT IN (NULL) OR NULL IS NULL))
)
GROUP BY PatientProfileID,t.Last,t.First,t.Suffix,t.Middle, Birthdate, Homeless,ListNameB,
TicketNumber,Visit,df.DotID,t.ListName,PatientID,Sex,PatientVisitID,DoctorID,CPTProcedureCodeQualifierMId, DOS,ic.DotID
END

IF 1 = 1
BEGIN

SELECT @OldFacID = '', @OldVisitDate = '01/01/1800', @OldPtID = 0, @OldQual='',@OldDrID = 0

DECLARE c CURSOR FOR
SELECT PatientVisitID, FacilityID, DOS, Qualifier, PatientProfileID
FROM #Patients p
ORDER BY PatientProfileID, FacilityID, DOS, Qualifier

OPEN c
FETCH NEXT FROM c INTO @NewVisitID, @NewFacID, @NewVisitDate, @NewQual, @NewPtID

WHILE @@FETCH_STATUS = 0
BEGIN
IF @NewFacID = @OldFacID AND @NewVisitDate = @OldVisitDate AND @NewPtID = @OldPtID AND @OldQual = @NewQual
BEGIN
UPDATE #Patients SET DuplicateVisit = 1 WHERE PatientVisitID = @NewVisitID
END
ELSE
BEGIN
SELECT @OldFacID = @NewFacID, @OldVisitDate = @NewVisitDate, @OldPtID = @NewPtID, @OldQual = @NewQual
END
FETCH NEXT FROM c INTO @NewVisitID, @NewFacID, @NewVisitDate, @NewQual, @NewPtID
END
CLOSE c
DEALLOCATE c

DECLARE c CURSOR FOR
SELECT PatientVisitID, DoctorID, DOS, Qualifier, PatientProfileID
FROM #Patients p
ORDER BY PatientProfileID, DoctorID, DOS, Qualifier

OPEN c
FETCH NEXT FROM c INTO @NewVisitID, @NewDrID, @NewVisitDate, @NewQual, @NewPtID

WHILE @@FETCH_STATUS = 0
BEGIN
IF @NewDrID = @OldDrID AND @NewVisitDate = @OldVisitDate AND @NewPtID = @OldPtID AND @OldQual = @NewQual
BEGIN
UPDATE #Patients SET DuplicateVisit = 1 WHERE PatientVisitID = @NewVisitID
END
ELSE
BEGIN
SELECT @OldDrID = @NewDrID, @OldVisitDate = @NewVisitDate, @OldPtID = @NewPtID, @OldQual = @NewQual
END
FETCH NEXT FROM c INTO @NewVisitID, @NewDrID, @NewVisitDate, @NewQual, @NewPtID
END
CLOSE c
DEALLOCATE c
END

UPDATE #Patients SET ResourceID = ISNULL((SELECT TOP 1 r.DotID as ResourceID FROM DoctorFacility r
INNER JOIN PatientVisitResource pvr ON pvr.ResourceID = r.DoctorFacilityID
WHERE pvr.PatientVisitID = #Patients.PatientVisitID),#Patients.DoctorID)

UPDATE #Patients SET Resource = (SELECT r.ListName FROM DoctorFacility r WHERE
r.DoctorFacilityID = #Patients.ResourceID)

IF 1 = 1
BEGIN
UPDATE #Patients SET ResourceType = ISNULL((SELECT TOP 1 ml.Description FROM ResourceTypeAssignments rta
LEFT JOIN MedLists ml ON rta.ResourceTypeID = ml.MedListsID
WHERE rta.ResourceID = #Patients.ResourceID),'No Resource Type')
END
ELSE
BEGIN
UPDATE #Patients SET ResourceType = 'Doctor'
END

SELECT * FROM #Patients ORDER BY DOS, ResourceID, FacilityName, DoctorName
DROP TABLE #UDSTMP
DROP TABLE #Patients

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 19:26:21

isnull and coalesce must have the same returning type for all inputs.

so one of your options is to cast an int to a varchar

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources