Grouping case statement

[Danielbryanuk]

I am trying to create artificial groups in a query, based only on one member, in order to be used as a total in a footer.

I have tried the following to create the artificial group:

case
when [costitem] in ('x')
then 'group1'

when [costitem] in ('x','y')
then 'group2'

when [costitem] in ('x','y','z')
then 'group3'

else null
end

The problem is that the groupings are cumulative, i.e. all groups include [costitem] "x", therefore the case statement does not fully evaluate.

Any ideas anyone on query criteria for the when statement, or any other approaches?

I cannot change the package unfortunately, which I know would be the ideal answer!

Thanks
Dan

 

[drlex]

Dan,
Would the addition of NOT assist?

i.e.

case
when [costitem] in ('x') [b]and [costitem] NOT in ('y','z')[/b]
then 'group1'

when [costitem] in ('x','y') [b]and [costitem] NOT in ('z')[/b]
then 'group2'

when [costitem] in ('x','y','z')
then 'group3'

else null
end

soi là, soi carré