Notifications
Clear all
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
Posted : May 24, 2012 3:04 am
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
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
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
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
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
