Welcome to the NEW CHUG— your ultimate destination for cutting-edge insights and discussions at the intersection of healthcare and technology.

Our platform is dedicated to exploring the dynamic landscape of healthcare technology, empowering professionals, enthusiasts, and innovators alike with the knowledge and resources to navigate the rapidly evolving field.

From telemedicine revolutionizing patient care to the transformative potential of artificial intelligence in diagnostics, we delve into a myriad of topics shaping the future of healthcare delivery. Whether you’re a seasoned healthcare IT professional, an aspiring entrepreneur in the digital health space, or simply curious about the latest innovations, our curated content, expert interviews, and thought-provoking articles provide a comprehensive exploration of healthcare technology’s most pressing issues.

Join us as we navigate the forefront of innovation, driving forward the transformation of healthcare for a healthier, more connected world.

Have questions or suggestions? Please use the contact button below.

Notifications

Clear all

Problem with query or crystal report

MEL, SQL, Oracle

Last Post by Anonymous 1 second ago

2 Posts

1 Users

0 Reactions

56 Views

RSS

jkratzer

(@jkratzer)

Posts: 138

Estimable Member

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

jkratzer

(@jkratzer)

Posts: 138

Estimable Member

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

Share:

24 Forums
9,940 Topics
39.9 K Posts
0 Online
6,470 Members

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed

Powered by wpForo Powered by wpForo version 2.4.0