Patricia G.L.Hall wrote:
>
> A patient's records are stored throughout many tables in the db. For
> the most part it is one row per patient record. Sometimes, in some
> tables, there can be multiple records per patient record.
>
> I have to flatten the patient record into a text file. The format the
> text file takes is spec'ed out by a third party and it is what I have
> to conform to. It turns out that this text file does not expect there
> to be multiple records per patient record... ever.
>
> So I have to query the database and deal with the cases where I have
> multiple records, but I have to flatten them into one.
>
> I can think of two ways to do it in theory... but I don't know how to
> make it work in SQL in practice.
>
> 1) Somehow cause the query to only return the Top 1 record per primary
> key. That wold be acceptable, but I have no idea if its a legal idea.
>
> 2) Somehow, through subqueries, aggregate the multiple values when they
> appear. That is what I was attempting to do in the question I
> originally posted. The Case statement works fine unless I hit a record
> with multiple rows. It returns both rows and that violates my
> parameters.
For arguments sake, say you have 1 record for a patient, and 3
X-ray records for that patient because there are 3 X-ray photo's.
Which X-ray record do you want to send to the third party? A
random one, the last one? Or do you just want to send a boolean
indicating there is a number of X-ray records?
From your code I get the impression you just want to send the
boolean, so I will give an example of that based on your original
post:
SELECT
et.caseid,
SUM(
CASE etp.protocolid
WHEN 12 THEN 1
ELSE 0
END
)
FROM
etprocedure et LEFT JOIN eTransferProtocolLink etp ON
etp.etProcedureid = et.etProcedureid
GROUP BY
et.caseid
(This will not really return a boolean, it will return the number
of records but CF will be able to interpret it as a boolean. Use
another CASE statement if you really want a boolean.)
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community.
http://www.fusionauthority.com/ads.cfm
