SQL. Multiple values for the condition.

Factual Questions
1

I am trying to pick one output if the input is one of several string values.

For example (using foodstuff as an example) …

if the foodstuff is one of these - banana, strawberry, apple, orange then output ‘fruit’ else output ‘not fruit’
I can’t seem to find any online case help that explains how to construct the code when you have multiple condition values.

The action to be performed when the condition is one of the values is quite large, so I don’t want to have to repeat it for every individual item

2

You mean in the context of a stored procedure? The following would work in mySql:

if (foodstuff like ‘banana’ || foodstuff like ‘strawberry’ || foodstuff like ‘apple’) then
– blah blah blah
end if;

or

if foodstuff in (‘banana’, ‘strawberry’, ‘apple’) then
– blah blah blah
end if;

The advantage of the former way is you can use like with wildcard matches. AFAIK in is restricted to equality testing.

Is that what you’re looking for?

3

Depending on what SQL implementation you’re using, there may be a function that can help. For example, in MySQL, you could write it as

SELECT IF(FIND_IN_SET(fruit_name, ‘banana,apple,orange’) > 0, ‘fruit’, ‘not fruit’)
FROM …
WHERE …

Another thought: you could have a separate table with entries for each fruit. Then, you can join your fruit_name column to the separate table to get the group. You could have entries for everything (all fruits and all non-fruits) or just entries for fruits and use a left join (if you get a null back, you know it’s not a fruit).

4

The replies use alternatives to the ‘case’ statement. Is there a way of doing it within a case statement? (in c++ it would be a ‘fall-through’ to one action for many conditions)

Here’s what I’ve had to do in the meantime…

((case pool_type

when ‘WN’ then (select commission from amtotecommissions where program_name=a.program_name and pool_type=‘WN’ and _date=a._date)
when ‘PL’ then (select commission from amtotecommissions where program_name=a.program_name and pool_type=‘WN’ and _date=a._date)
when ‘SH’ then (select commission from amtotecommissions where program_name=a.program_name and pool_type=‘WN’ and _date=a._date)
when ‘WP’ then (select commission from amtotecommissions where program_name=a.program_name and pool_type=‘WN’ and _date=a._date)
when ‘PS’ then (select commission from amtotecommissions where program_name=a.program_name and pool_type=‘WN’ and _date=a._date)
when ‘WS’ then (select commission from amtotecommissions where program_name=a.program_name and pool_type=‘WN’ and _date=a._date)
when ‘WPS’ then (select commission from amtotecommissions where program_name=a.program_name and pool_type=‘WN’ and _date=a._date)

else
(select commission from amtotecommissions where program_name=a.program_name and pool_type=a.pool_type and _date=a._date)

You can see that the action for WN,PL,SH,WP,PS,WS,WPS, is exactly the same each time.

The table structure is fixed. I can’t change it.

5

Okay, I’d probably try a searched case, where you leave out the input expression but can put any complex expressions into the when spot, like:



case pool_type 

when pool_type in ('WN', 'PL', 'SH', 'WP', 'PS', 'WS', 'WPS')  then 
       (select commission from amtotecommissions 
               where program_name=a.program_name and pool_type='WN' and _date=a._date)

else
(select commission from amtotecommissions 
        where program_name=a.program_name and pool_type=a.pool_type and _date=a._date)

Does that work?

The other option would be writing a database function, if you can do that in the database.

6

I did try that (it was my first thought - I’ve used the very convenient ‘in’ feature in other queries) but it didn’t work - complained about ‘in’ as a sytax errror.
I can write functions. I would have thought that something as powerful as SQL would allow this quite basic and probably common feature in it’s case construct.

Anyway… having now written the code with the repeated action I am not in need of an answer - but it would be useful to know so that I can make my sql queries look less like traditional ten million line programming language code!
edit:hang on a minute…

7

babapoopoo - I missed the edit window…

It does work afterall… Just have to modify the case syntax …

instead of * case [field] when [value]* (mine) or case [field] when [field] in ([values]) you use case when [field] in ([values])

My excuse is that the ‘standard’ of constructing the case statement is inconsistent between the two ways- (between ‘when the value is these do this’ and ‘when the value is this do this’)
so, thanks chrisk for showing me how to do it :slight_smile:

If only there were a way of returning the favour to all the people I’ve leeched SQL help from! :smiley:

8

Actually, it’s not really ‘when the value is these do this’ (or ‘when the value is these return this’)

It’s “when this is true then return this”

Which has the advantage of giving you all kinds of possible things that you can test, as long as they come down to a true or false - letting you use complicated tests that involve three different columns, as well as ‘is this value one of these’. But the syntax is indeed different and a bit harder to get your head around. Even I have to check the SQL server manual sometimes.

I’ve developed a bit of a reputation at work, I think, for doing clever and tricky things with case statements - like group by case, order by case, and join on fieldname = case. :wink:

9

chrisk I think I love you - You may have solved another problem I haven’t even posted about, with your last sentence - ‘Group By case’.
I am wondering if I can do the same thing with a sum, when the pool_type is ‘WN’,'PL,‘SH’,‘WP’,‘PS’,‘WS’ combine into ‘WPS’ else group by what’s left.

Off to try it I go…
[sub]p.s. If you feel a little uncomfortable - I don’t really love you, but I might owe you a pint![/sub]

10

So you might be able to help me…

how would I go about grouping all the variations of ‘win’ ‘place’ and ‘show’ into one WPS group?
(I’ve tried unsuccesfully)

11

Hmm… I’m not sure about what your table is like, but the basics would look like:

select case when result in (‘win’, ‘place’, ‘show’) then ‘win-place-show’
else ‘you suck!’ end label, count(*)
from race_horses
group by case when result in (‘win’, ‘place’, ‘show’) then ‘win-place-show’
else ‘you suck!’ end

12

There is smoke coming out of my ears.
In your example ‘you suck’ needs to be a field rather than a string. so "group by ‘WPS’ when the value is (WN PL SH etcc) or by pool_type

so I could end up with a table like this…

WPS (value)
EX (value)
QN (value)
TR (value)
instead of
WN (value)
PL (value)
SH (value)
WP (value)
PS (value)
WS (value)
WPS (value)
EX (value)
QN (value)
TR (value)
But it doesn’t work (I get the complaint - ‘pool type’ is invalid in the select list because it is not contained in an agregate function or group by clause (even though it is!))

so for now - I’ve solved it temporarily by wrapping the whole mega query inside another query with a simple group by clause (in other words crate a table that shows ‘WPS’ for all the variants and put it in a new field) then run a query on that grouping by the new field…
select site,program_name,pool_type,sum(handle) as handle,sum(commission) as commission from

(select (case trackingid when 2 then ‘site1’ else ‘site2’ end) as site,
program_name,
(case when pool_type in (‘WN’,‘PL’,‘SH’,‘WP’,‘PS’,‘WS’,‘WPS’) then ‘WPS’
else pool_type end) pool_type,

sum(cast(total_bet_amount as real)-cast(refund_amt as real)) as handle,

((case when pool_type in (‘WN’,‘PL’,‘SH’,‘WP’,‘PS’,‘WS’,‘WPS’) then (select commission from amtotecommissions where program_name=a.program_name and pool_type=‘WN’ and _date=a._date)

else
(select commission from amtotecommissions where program_name=a.program_name and pool_type=a.pool_type and _date=a._date)

end

)/100)* sum(cast(total_bet_amount as real)-cast(refund_amt as real)) as commission
from amtoteaccountactivity a inner join clients on a.accountnumber = clients.accountnumber where _date=‘08/06/11’ and transaction_type = ‘Bet’
group by _date,program_name,trackingid,pool_type) q1

group by program_name,site,pool_type
order by program_name,site,pool_type

13

I have been cursed with being the SQL man for the worst designed database on earth.

14

Hmm… the tricky thing about group by case is that the case statement in the select has to be EXACTLY the same as the one that you’re grouping by - well, differences in white space are the same, but it has to parse exactly the same way. If you do that right, then you shouldn’t get that error message. ‘Pool type’ is not actually in the group by clause by itself, but you can get around that by grouping on the complex expression.

And - condolences for having to work with a badly designed database. It really can make certain things about 10 thousand times harder to do. :frowning:

15

I’ve seen you ask a lot of SQL questions recently: perhaps you should suggest to your boss that you go on a SQL course?