Notifications
Clear all
Topic starter
I have created a query that I am trying to turn into a report in CPS PM.
Query works perfect in SQL Management Studio, but gives unknown error 0x800A0E78 when I run it against the report in CPS.
I got an SQL trace and copied the query as seen in the trace. Ran it in Management Studio and it worked fine.....
What am I missing?
Thanks
Query is as follows:
DECLARE @COUNTERS INT
DECLARE @intFlag INT
DECLARE @DOB VARCHAR(20)
DECLARE @LAST VARCHAR(100)
DECLARE @ListofIDs TABLE(LAST VARCHAR(100),DOB VARCHAR(100), ID INT IDENTITY(1,1))
DECLARE @VISITIDS TABLE(PatientVisitID varchar(20), ID INT IDENTITY(1,1))
INSERT INTO @ListofIDs
SELECT PatientProfile.Last as LAST,convert(varchar,convert(date, PatientProfile.Birthdate, 103),101) as DOB
FROM (PatientProfile PatientProfile INNER JOIN Appointments Appointments ON PatientProfile.PatientProfileId=Appointments.OwnerId) INNER JOIN PatientVisit PatientVisit ON Appointments.PatientVisitId=PatientVisit.PatientVisitId
WHERE convert(varchar,convert(date, Appointments.ApptStart, 103),101) = convert(varchar,?ApptsDate.DATE?,101)
AND Appointments.ApptKind=1
set @COUNTERS=(select top 1 ID FROM @ListofIDs ORDER BY ID DESC)
SET @intFlag=1
WHILE (@intFlag <= @COUNTERS)
BEGIN
SET @DOB = '%'+(SELECT DOB FROM @ListofIDs WHERE id=@intFlag)+'%'
SET @LAST = '%'+(SELECT LAST FROM @ListofIDs WHERE id=@intFlag)+'%'
INSERT INTO @VISITIDS
SELECT PatientVisitID
from PatientVisit
WHERE Description LIKE @DOB
AND Description like @LAST
AND PatientProfileId in ('204985','204986','214381','214383')
AND BillStatus in (11,13,14)
SET @intFlag = @intFlag + 1
END
SELECT PatientVisit.TicketNumber, PatientVisit.Description, PatientVisitAgg.PatBalance,convert(varchar,convert(date, PatientVisit.Visit, 103),101) as VisitDate
FROM PatientVisitAgg PatientVisitAgg INNER JOIN PatientVisit PatientVisit ON PatientVisitAgg.PatientVisitId=PatientVisit.PatientVisitId
WHERE PatientVisit.PatientVisitId in (select PatientVisitId from @VISITIDS)
AND PatBalance > 0
Posted : June 24, 2016 6:06 am
Topic starter
I figured it out.
Was missing the following in the code:
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
Posted : June 24, 2016 6:46 am
