Syntax error in select case

Question

I started learning SQL few days ago and I've the following query which gives me an error near "case" and I can't seem to figure it out:

SELECT * , Stuff((SELECT ', ' +  CAST( orderid as varchar(10))
      FROM( oitems t2 
 case when (ocardtype in ('PayPal','Sofort') OR
        ocardtype in ('mastercard','visa') and
        odate is not null) then 
              FOR XML PATH(''), 1, 2, '') [Proccessed] else  FOR XML PATH(''), 1, 2, '') [NotProccessed] end)
                      FROM orders t1
    Group by orderid,t1.odate, t1.ocardtype

I guess you get what the this code is supposed to do, but I'm not sure if this is the right way to use select case.

oitems table

+---------+-----------+----------+
| orderid | catalogid | numitems |
+---------+-----------+----------+
| o737    |       353 |        1 |
| o738    |       364 |        4 |
| o739    |       353 |        3 |
| o740    |       364 |        6 |
| o741    |       882 |        2 |
| o742    |       224 |        5 |
| o743    |       224 |        2 |
+---------+-----------+----------+






orders table
    +-----------------+------------+------------+
    |         orderid | ocardtype  |   odate    |
    +-----------------+------------+------------+
    |     o737        | Paypal     |            | 'OK
    |     o738        | MasterCard | 01.02.2012 | 'OK
    |     o739        | MasterCard | 02.02.2012 | 'OK
    |     o740        | Visa       | 03.02.2012 | 'OK
    |     o741        | Sofort     |            | 'OK
    |     o742        |            |            | 'ignore because ocardtype is empty
    |     o743        | MasterCard |            | 'ignore because Mastercard no odate
    +-----------------+------------+------------+

expected result

+-----------+------------------------+--------------+
| catalogid | ProcessedSucssessfully | NotProcessed |
+-----------+------------------------+--------------+
|       353 |o737,o739               |              |
|       364 |o738,o740               |              |
|       882 |o741                    |              |
|       224 |                        |o742,o743     |
+-----------+------------------------+--------------+

i have the following conditions which you can find in the case statment of above code

1. if ocardtype is empty then add orderid to NotProcessed

2. if ocardtype for some order is MasterCard or Visa and the odate is empty then add orderid to NotProcessed

3. if ocardtype is Paypal or Sofort, then don't check for odate and add the orderid to ProcessedSucssessfully

Answer 1

check this request

SELECT CatalogId, ProcessedSucssessfully = 
       STUFF((SELECT ', ' + b.OrderId
              FROM oitmes b JOIN orders o ON b.OrderId = o.OrderId
              WHERE b.CatalogId = a.CatalogId
              AND (o.OcardtType in ('PayPal','Sofort') OR o.OcardtType in  ('mastercard','visa') and o.odate is not null)    
              FOR XML PATH('')), 1, 2, ''),
                  NotProcessed =
        STUFF((SELECT ', ' + c.OrderId
               FROM oitmes c JOIN orders o ON c.OrderId = o.OrderId
               WHERE c.CatalogId = a.CatalogId
               AND (o.OcardtType in ('mastercard') OR o.OcardtType is null) and o.odate is null
               FOR XML PATH('')), 1, 2, '')
FROM oitmes a
GROUP BY a.CatalogId

Demo on SQLFiddle