>i just don't get it what the heck i'm doing wrong. I would like to have
>something like that:
>
>select ba.artno
>from mov_invoices mi, bas_articles ba
>where mi.invdate between '2014-05-01 00:00:00' and '2014-08-01 00:00:00'
>and case
>when (ba.artno like '90__') then '9000'
>when (ba.artno like '80__') then '8000'
>when (ba.artno like '70__') then '7000'
>when (ba.artno like '60__') then '6000'
>when (ba.artno like '50__') then '5000'
>when (ba.artno like '40__') then '4000'
>when (ba.artno like '30__') then '3000'
>when (ba.artno like '20__') then '2000'
>when (ba.artno like '10__') then '1000'
>end
>
>any ideas somebody?

The reason this fails, Johannes, is that all parts a the WHERE clause should evaluate to a Boolean value and your query doesn't.

mi.invdate between '2014-05-01 00:00:00' and '2014-08-01 00:00:00' will evaluate to true or false (or null), but you have no equality, between or similar for your case statement (you have only specified one side of the comparison - it's the same as specifying mi.invdate and forgetting between and the dates).

Also, there's no need for you to use CASE in your case. Try something like:

select ba.artno
from bas_articles ba
join mov_invoices mi on substring(ba.artno from 1 for 2) || '00' = mi.<something>
where mi.invdate between '2014-05-01 00:00:00' and '2014-08-01 00:00:00'

I'm guessing that your case statement creates a value that should be compared to a value in mov_invoices, and that you do not want to return articles not related to the invoice. If I'm wrong, replace JOIN with CROSS JOIN, remove the ON part and move the substring to the WHERE clause.

HTH,
Set