August 12, 2008 at 2:43 pm
I am attempting to do the following except you can't use an IN filter with a CASE. Does anyone have any idea of a better way to approach this?
m.ManagingGroupID in case s.MajorArea
when 'tb_Core' then
(select ManagingGroupID from tb_CoreManGroup where CoreID = @id)
when 'tb_Event' then
(select ManagingGroupID from tb_EventManGroup where EventID = @id)
when 'tb_FocusGroup' then
(select ManagingGroupID from tb_FocusGroupManGroup where FocusGroupID = @id)
when 'tb_Grant' then
(select ManagingGroupID from tb_GrantManGroup where GrantID = @id)
when 'tb_Membership' then
(select ManagingGroupID from tb_MemberManGroup where MemberID = @id)
when 'tb_Patient' then
(select ManagingGroupID from tb_PatientManGroup where PatientID = @id)
when 'tb_Program' then
(select ManagingGroupID from tb_ProgramManGroup where ProgramID = @id)
when 'tb_Protocol' then
m.ManagingGroupID in (select ManagingGroupID from tb_ProtManGroup where ProtocolID = @id)
when 'tb_Publication' then
(select ManagingGroupID from tb_PubManGroup where PublicationID = @id)
end
Thanks!
August 13, 2008 at 5:33 am
Maybe something like:
WHERE EXISTS
(
    SELECT *
    FROM
    (
        SELECT ManagingGroupID, 'tb_Core' FROM tb_CoreManGroup WHERE CoreID = @id
        UNION ALL
        SELECT ManagingGroupID, 'tb_Event' FROM tb_EventManGroup WHERE EventID = @id
        UNION ALL
        SELECT ManagingGroupID, 'tb_FocusGroup' FROM tb_FocusGroupManGroup WHERE FocusGroupID = @id
        UNION ALL
        SELECT ManagingGroupID, 'tb_Grant' FROM tb_GrantManGroup WHERE GrantID = @id
        UNION ALL
        SELECT ManagingGroupID, 'tb_Membership' FROM tb_MemberManGroup WHERE MemberID = @id
        UNION ALL
        SELECT ManagingGroupID, 'tb_Patient' FROM tb_PatientManGroup WHERE PatientID = @id
        UNION ALL
        SELECT ManagingGroupID, 'tb_Program' FROM tb_ProgramManGroup WHERE ProgramID = @id
        UNION ALL
        SELECT ManagingGroupID, 'tb_Protocol' FROM tb_ProtManGroup WHERE ProtocolID = @id
        UNION ALL
        SELECT ManagingGroupID, 'tb_Publication' FROM tb_PubManGroup WHERE PublicationID = @id
    ) D (ManagingGroupID, MajorArea)
    WHERE D.ManagingGroupID = M.ManagingGroupID
        AND D.MajorArea = S.MajorArea
)
August 13, 2008 at 8:36 am
That won't work because it gets everything. I need it to ONLY get from the appropriate table. At least, that what I think from looking at this. I am open to correction.
August 13, 2008 at 8:38 am
I think you should try it or try 9 separate queries if MajorArea is the same for each row in the result set.
August 13, 2008 at 8:44 am
evaleah (8/12/2008)
I am attempting to do the following except you can't use an IN filter with a CASE. Does anyone have any idea of a better way to approach this?
m.ManagingGroupID in case s.MajorAreawhen 'tb_Core' then
(select ManagingGroupID from tb_CoreManGroup where CoreID = @id)
when 'tb_Event' then
(select ManagingGroupID from tb_EventManGroup where EventID = @id)
when 'tb_FocusGroup' then
(select ManagingGroupID from tb_FocusGroupManGroup where FocusGroupID = @id)
when 'tb_Grant' then
(select ManagingGroupID from tb_GrantManGroup where GrantID = @id)
when 'tb_Membership' then
(select ManagingGroupID from tb_MemberManGroup where MemberID = @id)
when 'tb_Patient' then
(select ManagingGroupID from tb_PatientManGroup where PatientID = @id)
when 'tb_Program' then
(select ManagingGroupID from tb_ProgramManGroup where ProgramID = @id)
when 'tb_Protocol' then
m.ManagingGroupID in (select ManagingGroupID from tb_ProtManGroup where ProtocolID = @id)
when 'tb_Publication' then
(select ManagingGroupID from tb_PubManGroup where PublicationID = @id)
end
Thanks!
Actually, from this snippet, I have no idea what you are trying to accomplish. It would help to have the entire query you are working on.
😎
August 13, 2008 at 9:55 am
Ken's code looks like it'll work to me based on what we know about the query. You might be getting tripped up because you posted a very small portion of the WHERE clause and Ken followed suit and also posted that same section. It's the EXISTS that is the key there.
August 13, 2008 at 10:23 am
Julie Zeien (8/13/2008)
Ken's code looks like it'll work to me based on what we know about the query. You might be getting tripped up because you posted a very small portion of the WHERE clause and Ken followed suit and also posted that same section. It's the EXISTS that is the key there.
Actually, my error in reading Ken's suggestion was the size of my screen. I totally missed the scroll bar and therefore missed the final limiting line of "AND D.MajorArea = S.MajorArea". This was why I thought it was getting the entire thing. Of course this is the solution!
Thank you to Ken and everyone else for patiently waiting for me to get the bolts in the right place 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply