Using CASE WHEN and MAX() in SPSS

Question

What is the correct syntax for using CASE WHEN and MAX() in an SQL Query in SPSS?

I've tried using the following query in the SPSS syntax window but it does not recognize either:

SELECT * FROM 
(
  SELECT id, MAX(CASE WHEN val='A' THEN 'A' END) as Val_1, 
             MAX(CASE WHEN val='B' THEN 'B' END) as Val_2
  FROM table1 GROUP BY id
)a
WHERE Val_1 IS NULL OR Val_2 IS NULL;

I'm using SPSS 20.

Update: I tried using both my query and the one presented by AK47.

My query's error:

Warning. Command name: GET DATA SQLExecDirect failed :[Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression 'MAX(CASE WHEN val='A' THEN 'A' END)'

AK47's Error:

Warning. Command name: GET DATA SQLExecDirect failed :[Microsoft][ODBC Excel Driver] Syntax error in FROM clause.

Is this all a matter of finding the correct syntax in SPSS or do you think SPSS just doesn't support some SQL functions?

Answer 1

I am not familier with SPSS, but as per my sql exp. i have given following ans, hope it helps you,

SELECT * FROM 
(
  SELECT id, CASE WHEN MAX(val)='A' THEN 'A' END as Val_1, 
             CASE WHEN MAX(val)='B' THEN 'B' END as Val_2
  FROM table1 GROUP BY id
)a
WHERE Val_1 IS NULL OR Val_2 IS NULL;

Answer 2

Presumably you are doing this within a GET DATA TYPE=ODBC command. The exact SQL that works depends on the particular database and ODBC driver you are using. When you say the SQL isn't recognized, what do you mean? Are you getting an error from the driver or something else?

Answer 3

As it turns out, the ODBC Driver (the same driver used in MS Access) does not support CASE...WHEN. Instead, use SWITCH:

SELECT * FROM 
(
  SELECT id, MAX(SWITCH( val='A', 'A')) as Val_1, 
             MAX(SWITCH( val='B', 'B')) as Val_2
  FROM table1 GROUP BY id
)a
WHERE Val_1 IS NULL OR Val_2 IS NULL;

This will produce the same results.