> 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?
Whenever possible I prefer to compact into a boolean, because in many
cases that is what the spec wants in the end. However I think there will
be some cases where boolean be an option (otherwise it would be too
consistent and we can't have that). If and when that happens, I'll have
to choose one of the available values to report. In those cases there are
no guidelines for me to follow, re: first, last or random. I prefer to
stay away from random... there's no need for that. Had I the choice, I'd
choose the first record. Just because.
I haven't yet figured out how to deal with these when they happen.
> 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
Excellent. I modified it a bit because I need to select against a
specific number of ids... and when there are no recrds in etprocedure that
match, I still need to return a row for that id. I also changed SUM to
MAX as that fits better. So this is what seems to be working for me so
far:
SELECT
et.caseid,
MAX(
CASE etp.protocolid
WHEN 12 THEN 1
ELSE 0
END
)
FROM
etprocedure et LEFT JOIN eTransferProtocolLink etp ON
etp.etProcedureid = et.etProcedureid
Where
et.caseid IN (76,77,78,80)
GROUP BY
et.caseid
Union
Select
ci.caseID,
'0'
>From caseInfo ci
Where ci.caseID IN (76,77,78,80) AND
ci.caseID NOT IN (
Select Distinct caseID
From etProcedure
)
Thanks so much for your help. I'm not done yet, but I have hopes that
this will get me further than before.
-Patti
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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
