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

Looking for a Crystal Report that uses a UNION query

Trading Post

Last Post by Anonymous 1 second ago

6 Posts

2 Users

0 Reactions

68 Views

RSS

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

Topic starter

I'm looking for a sample Crystal Report that uses a Union Query. For example, say I want to pull all females over age 50 and I want to data display their last mammogram and show a blank if they've never had one. I know this can be done by pulling every OBS and using the footer to display just the Mammogram OBS but that's not really very efficient (and what I'm actually trying to accomplish is quite elaborate). If anyone has a report that uses a Union query I would LOVE to take a look. I've tried but so far I've been unsuccessful. Thanks!

Joanne

[email protected]

Posted : May 24, 2012 3:04 am

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

That's not a UNION query, just a left join, I think.

select Person.pId, Person.patientId
        , Person.firstName as patFirst, Person.lastName as patLast
        , Person.dateOfBirth
        , MammObs.obsName, MammObs.obsDate, MammObs.obsValue, MammObs.maxDate
    from ml.Person
        left join (
            select Obs.pId, Obs.obsDate, Obs.obsValue
                    , ObsHead.name as obsName
                    , max(Obs.obsDate) over (partition by Obs.pId, Obs.hdId) as maxDate
                from ml.Obs
                    join ml.ObsHead on Obs.hdId = ObsHead.hdId
                where Obs.xId = 1.e+035
                    and Obs.change = 2
                    and ObsHead.name = 'MAMMOGRAM'
            ) MammObs on Person.pId = MammObs.pId
                and MammObs.obsDate = MammObs.maxDate
    where Person.isPatient = 'Y'
        and Person.pStatus = 'A'
        and Person.sex = 'F'
        and Person.dateOfBirth < add_months(sysdate, -50 * 12)

Posted : May 24, 2012 5:04 am

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

Topic starter

OK, I'll buy that for now but where would I paste this statement? When you view the SQL you can't edit it. Sorry if this is a stupid question. I am very proficant with Crystal EXCEPT the SQL Query piece of it. Thanks!!

Joanne

Posted : May 24, 2012 5:45 am

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

Assuming you're using something above CR8.5, you would put this in under Add Command -- it's right below MLO_ (or whatever your connection is called) in the Database Expert.

Posted : May 24, 2012 5:47 am

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

Topic starter

Thanks! I found it.....

Remember when I mentioned earlier that what I was trying to do is "quite elaborate", well, I tried to translate what I am trying to do in to the syntax example you gave me and I keep getting an error that a key word is missing. Looks like they're all there to me. Would you mind taking a quick peek when you get a minute? Here it is:

SELECT "PERSON"."SEARCHNAME", "PERSON"."HOMELOCATION", "LOCREG"."ABBREVNAME", "PERSON"."ISPATIENT", "USRINFO"."LASTNAME", "USRINFO"."FIRSTNAME", "PERSON"."PID", "PROBLEM"."CODE", "PROBLEM"."DESCRIPTION", "PROBLEM"."XID", "PROBLEM"."QUALIFIER", "PROBLEM"."ONSETDATE", "PROBLEM"."CHANGE", "PROBLEM"."PRID", "PERSON"."EXTERNALID", "PERSON"."DATEOFBIRTH", "ORDERS"."ORDERDATE", "ORDERS"."CODE", "ORDDX"."RANK", "ORDDX"."DXCODE"

FROM ((("ML"."PERSON" "PERSON" INNER JOIN "ML"."LOCREG" "LOCREG" ON "PERSON"."HOMELOCATION"="LOCREG"."LOCID") INNER JOIN "ML"."USRINFO" "USRINFO" ON "PERSON"."RESPPROVID"="USRINFO"."PVID") INNER JOIN "ML"."PROBLEM" "PROBLEM" ON "PERSON"."PID"="PROBLEM"."PID")

Left Join (

SELECT "ORDERS"."PID", "ORDERS"."ORDERDATE", "ORDERS"."CODE", "ORDERS"."DESCRIPTION", "ORDDX"."RANK", "ORDDX"."DXCODE", "ORDDX"."DXDESC", "ORDERS"."ORDERID", "ORDERS"."XID"

FROM "ML"."ORDERS" "ORDERS" LEFT OUTER JOIN "ML"."ORDDX" "ORDDX" ON "ORDERS"."DXGROUPID"="ORDDX"."DXGROUPID"

WHERE "ORDERS"."ORDERDATE">={ts '2012-01-01 00:00:00'} AND ("ORDERS"."CODE" LIKE '%72100%' OR "ORDERS"."CODE" LIKE '%72110%' OR "ORDERS"."CODE" LIKE '%72114%' OR "ORDERS"."CODE" LIKE '%72120%' OR "ORDERS"."CODE" LIKE '%72131%' OR "ORDERS"."CODE" LIKE '%72132%' OR "ORDERS"."CODE" LIKE '%72133%' OR "ORDERS"."CODE" LIKE '%72148%' OR "ORDERS"."CODE" LIKE '%72149%' OR "ORDERS"."CODE" LIKE '%72158%') AND "ORDERS"."XID">=1.e+035) "ML"."ORDERS" "ORDERS" ON "PERSON"."PID"="ORDERS"."PID"

WHERE "PROBLEM"."XID">=1.e+035 AND ("PROBLEM"."CODE" LIKE '%721.3%' OR "PROBLEM"."CODE" LIKE '%721.90%' OR "PROBLEM"."CODE" LIKE '%722.10%' OR "PROBLEM"."CODE" LIKE '%722.52%' OR "PROBLEM"."CODE" LIKE '%722.6%' OR "PROBLEM"."CODE" LIKE '%722.93%' OR "PROBLEM"."CODE" LIKE '%724.02%' OR "PROBLEM"."CODE" LIKE '%724.2%' OR "PROBLEM"."CODE" LIKE '%724.3%' OR "PROBLEM"."CODE" LIKE '%724.5%' OR "PROBLEM"."CODE" LIKE '%724.6%' OR "PROBLEM"."CODE" LIKE '%724.70%' OR "PROBLEM"."CODE" LIKE '%724.71%' OR "PROBLEM"."CODE" LIKE '%724.79%' OR "PROBLEM"."CODE" LIKE '%738.5%' OR "PROBLEM"."CODE" LIKE '%739.3%' OR "PROBLEM"."CODE" LIKE '%739.4%' OR "PROBLEM"."CODE" LIKE '%846.0%' OR "PROBLEM"."CODE" LIKE '%846.1%' OR "PROBLEM"."CODE" LIKE '%846.2%' OR "PROBLEM"."CODE" LIKE '%846.3%' OR "PROBLEM"."CODE" LIKE '%846.8%' OR "PROBLEM"."CODE" LIKE '%846.9%' OR "PROBLEM"."CODE" LIKE '%847.2%') AND "PROBLEM"."CODE" NOT LIKE 'CPT%' AND NOT ("PROBLEM"."CHANGE"=10 OR "PROBLEM"."CHANGE"=11 OR "PROBLEM"."CHANGE"=12) AND "PERSON"."ISPATIENT"='Y'

ORDER BY "PERSON"."HOMELOCATION", "PERSON"."SEARCHNAME", "PERSON"."PID", "PROBLEM"."PRID", "PROBLEM"."ONSETDATE" "ORDERS"."ORDERDATE"

Posted : May 24, 2012 7:11 am

Anonymous

(@Anonymous)

Posts: 0

New Member Guest

It looks like the problem is that you have an extra "ML"."ORDERS" after your left join. I found this by editing the code that Crystal hands back, formatting it using mixed case and whitespace and removing extraneous text like the extra "tablenames" and redundant INNER and OUTERs. When you do this, errors jump out at you more easily.

Also, I've found that LIKE comparisons usually work better if you can avoid starting them with a wildcard. And looking at the PROBLEM and ORDERS lists above, it looks to me like you could change them all (or most) to a straight = query, just adding the leading "ICD-" or "CPT-" to the beginning of the string and removing the trailing %. XID will never be greater than 1.e+035, so you can change that to a straight = comparison, too.

You can see what you need to improve query performance if you download Oracle SQL Developer and tell it to show you the Explain Plan for the query. If you see a large number next to a table that doesn't say "INDEX SCAN", you can look at the indexes for that table and try to figure out if you can add (or remove) something to your query that will allow it to use the indexes better.

Posted : May 29, 2012 12:09 am

24 Forums
9,940 Topics
39.9 K Posts
1 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