Oracle SQL: Select with variable and condition-CodePudding

I'm creating a query with a condition depending on the result of a variable.

If PROD = 'alpha' I need to show only models 2,3,4,5 and 6;
if PROD = 'beta' that only models 0 and 1 appear.

DEFINE PROD = 'ALPHA'

SELECT CAR_CODE, CAR, MODELS
FROM TB_CAR_MODELS
WHERE (
CASE PROD
WHEN 'ALPHA' THEN MODELS IN (2,3,4,5,6)
WHEN 'BETA' THEN MODELS IN (0,1)
END
);

I created this query but it's not working. Returns the error:

00000 - "missing keyword"

Can you help me please? Thanks

CodePudding user response：

There's a good answer here that I think will help you: Missing Keyword (ORA-00905) - Oracle SQL Case Statement

Check that out.

CodePudding user response：

You can modify your sql statement as following: 
SELECT CAR_CODE, CAR,
(CASE S.PROD
WHEN CASE S.PROD = 'ALPHA' THEN MODELS IN (2,3,4,5,6)
WHEN  CASE S.PROD =  'BETA' THEN MODELS IN (0,1)
END
)  MODELS
FROM TB_CAR_MODELS S

CodePudding user response：

A few sample rows:

SQL> select * from tb_car_models;

  CAR_CODE CAR      MODELS
---------- ---- ----------
         1 BMW           2  -- ALPHA
         2 Audi          5  -- ALPHA
         3 Opel          0  -- BETA

SQL>

As I'm running it in SQL*Plus, I'm using a substitution variable:

SQL> select *
  2  from tb_car_models
  3  where models in (select models
  4                   from tb_car_models
  5                   where models in (2, 3, 4, 5, 6)
  6                     and '&&prod' = 'ALPHA'
  7                   union all
  8                   select models
  9                   from tb_car_models
 10                   where models in (0, 1)
 11                     and '&&prod' = 'BETA'
 12                  );
Enter value for prod: ALPHA

  CAR_CODE CAR      MODELS
---------- ---- ----------
         1 BMW           2
         2 Audi          5

SQL> undefine prod
SQL> /
Enter value for prod: BETA

  CAR_CODE CAR      MODELS
---------- ---- ----------
         3 Opel          0

SQL>

Depending on tool you use, that might be e.g. ... and :prod = 'ALPHA' or similar.