SQL Question... Can i get away from the unions?

[Imagination]

Hi!

I'm looking at a table to see if there are 2 Group Variables in it. Based on if one or both are in the table, i'm trying to create a sql row based on 3 function id's. I'm am just wondering if there is a simple way to do it then what i'm doing (ie: get rid of the unions).

Thanks!




declare @GRPREV varchar(12)
select @GRPREV = groupid from [function] where groupid = 'GRPREV'
declare @GRPREC varchar(12)
select @GRPREC = groupid from [function] where groupid = 'GRPREC'

SELECT 'A' as product, 'REV' as functionid, 'Reverse' as description, 'sys' as groupid,
(Case when exists (select * from userfunction where product = 'A' and functionid = 'REV') then 1 else 0 end) as entitled
WHERE @GRPREV = 'GRPREV'
UNION
SELECT 'A' as product, 'REC' as functionid, 'Record' as description, 'sys' as groupid,
(Case when exists (select * from userfunction where product = 'A' and functionid = 'REC') then 1 else 0 end) as entitled
WHERE @GRPREC = 'GRPREC'
UNION
SELECT 'A' as product, 'VerifyRev' as functionid, 'Verify Reverse/Record' as description, 'sys' as groupid,
(Case when exists (select * from userfunction where product = 'A' and functionid = 'VerifyRev') then 1 else 0 end) as entitled
WHERE (@GRPREC = 'GRPREC' or @GRPREV = 'GRPREV')

 

[imported_Dhaval00]

I didn't look at your SQL too closely, but it is one of those things that is best understood in the Query Execution Plan (I am assuming you're using SQL Server).

UNION is one way of doing it. Other ways would be:
1) a complex join (this may not feasible depending on what kind of indexes you have defined)
2) temporary tables

Again, there are multiple ways (again, didnt look at your SQL too closely, so sorry if I misunderstood), and if performance is an issue, try and test all of them.

 

[oog]

how about using an OR?